Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2005-05-17 : 13:06:40
|
| I am working on a report that will generate results of a given production run on some time or event based interval. The trouble I am having with this is I am not sure what the best way to go about executing this report is. Here is my dilemma:I have a table as such (actually many more fields, but kept it simple for this post):CREATE TABLE [dbo].[tbl_1PMProduction] ( [Campaign_no] [numeric](6, 0) NULL , [RecordDate_Time] [datetime] NULL , [timestamp] [timestamp] NULL ) ON [PRIMARY]GOWith the following data:Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101561, '2005-05-09 11:35:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101561, '2005-05-09 11:34:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101561, '2005-05-09 11:33:52.400') Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101561, '2005-05-09 11:32:52.400') Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101561, '2005-05-09 11:31:52.400') Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101561, '2005-05-09 11:30:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101560, '2005-05-09 09:30:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101560, '2005-05-09 09:29:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101560, '2005-05-09 09:28:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101560, '2005-05-09 09:27:52.400')Insert into tbl_1PMProduction (Campaign_no, RecordDate_Time)values (101560, '2005-05-09 09:26:52.400')The data is generated on one-minute intervals from production machinery. Each data set has a unique number known as a campaign number. For the above data set there are two different production runs, 101561 and 101560. Looking closer at the time stamps its obvious that Production run 101560 ran between2005-05-09 09:26:52.400 and 2005-05-09 09:30:52.400. About 4 minutes.Production run 101561 ran between2005-05-09 11:30:52.400 and 2005-05-09 11:35:52.400. About 5 minutes.In the real world these runs would likely be hours or even days.I would like to develop a way for SQL2000 to periodically (maybe every hour or 1/2 hour) check the tbl_1PMProduction table for a change in campaign number. Once I have identified there has been a change (for ex. 101560 to 101561) I can tell it to execute a report that will be automatically emailed to the appropriate users having a summary of the production run. I’m thinking I could use the scheduler to execute a check. 1st, I am not sure scheduling is the best way. 2nd, I'm not sure how I would do the comparison to determine if the campaign number has changed and 3rd, how would I tell it to send data involving only the "previous" campaign number - which in thoery, the run should be DONE. I was wondering what kind of thoughts some of you experts here would have on this??? Any help you could provide is once again greatly appreciated.Thanks! Your limits are only as far as you set your boundries.... |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-05-17 : 15:04:27
|
| Didn't you say that the campaign number is unique? I am wondering on item #2, how is it that the campaign number would change? Would this sort of a setup work? I have a table tblNotes which holds notes about anything. The table has a primary key that is the identity field. I want to know when a user appends more notes to existing notes. To do that I have a routine in my Update script that pushes the previous data off to a History table before it updates the row. That way I have a copy of what the notes looked like before they were updated. I can keep an infinate number of revisions to notes since I am always pushing the most recent copy off to the History table. Make sense? Is this what you are trying to do?Aj |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-05-17 : 15:16:52
|
| I would say the application doing the inserts should peform this...ie the new run should know when it's starting and should call a stored proc on the previous run.Better yet, why not when the run completes do it then....To go down your route (whicj I wouldn't) you could create a trigger that could check the values of table and the insert. Then start a jon that calls a sproc.I'd have the application do it though.Brett8-) |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2005-05-20 : 07:01:33
|
| Actually stated above, the campaign number is unique to the dataset. Not unique to the record. I ended up using Brett's suggestion and am now having the controller (at the machine level) detect a change in campaign number (new production run) and using this change to execute a stored procedure which is executing the reports I need. Thanks for you help.Your limits are only as far as you set your boundries.... |
|
|
|
|
|
|
|
|
Comparing Records - Best ideas?