Cursor Question

kilika · August 21st, 2003, 10:28 AM

I have a select statement that returns a bunch of records of events each with a start hour and a start minute. There is also a begin time and an end time for the process in which the event occurs. There could be 8-10 events for a process meaning that all 8-10 records would all the the same start and end times, but different start hour and a different start minute for the event itself. If the process spans to days, the only way I have of knowing which evens started on which day is by looking at the start minute and start hour for the event and comparing it to the previous to see if the start hour is less than the previous records. This is not really important for my question, but maybe someone has a better idea.

I am using a stored proc and want to do al the data manipulation on the server side so I can reuse the proc all over without having to mess with the data itself. I was thinking of opening this recordset in a cursor and running through it. I need to get the minutes between the even and the next event (so I know how long the event ran) and maybe even work with the dates a bit so I can have the date with the start time of the actual event (this is where I'd have to test the start time of the even to see if it was less than the previous heats so I knew it cam e from the same day as the start day versus the end day (a process runs for about an hour). If I want to simplify my data and create and return a smaller set of data, how do I create it in the stored proc and then return the manipulated data?

Chris

Jeff Mason · August 21st, 2003, 10:44 AM

It's tough to refrain from observing that you could have simplified your problem considerably if you had used the "correct" datatype (i.e. datetime) for storing your event start and end (date)times...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · August 21st, 2003, 11:00 AM

I couldn't agree more. However, the data is populated from another system on our plant and it was set up that way from long before the time I got here. It is how the other software works, so I cannot change the fact that I have to deal with the dates this way. Trust me, if I could change them to datetimes, I'd do it in a heartbeat. I do not even know the programming language it is done with as I do not even maintain it. One of our electrical engineers maintains it. Since it level 2 software dealing directly with machines and PLC's and such, I do not have much say it what happens with it.

Chris

Jeff Mason · August 21st, 2003, 11:27 AM

Let me get this straight. You have event data which can span (presumably) multiple days, but you have no indication which actual days are being spanned? Thus, you could have two rows with otherwise identical data, and no way to discern which row corresponded to which events on which days? Or which one came 'first'?

Perhaps you can post the relevant DDL for the table(s) and maybe we can make some sense of this mess.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · August 21st, 2003, 11:46 AM

I have one table that is for the processes. I have one table that is events. The process table has a start time and a stop time. A process runs about 45 minutes average. In the event table, I only have start minute and start hour. It has a foreign key into the process table to tie it to a process. I am in the process of seeing if I can get the date written to the events as well, but it may not happen. A process could begin at 11:30PM and end at 12:15 AM. In this case I may have something like this:

Code:

Hour  Minute  Event
23    30      Process start
23    45      load charge
23    59      gunning
0     12      load charge 2

I can tie it back to the original process and get the start date and end date, but I'd have to tie the date of the startdate to the event records that start with 23 and I'd have to tie the end date's date to the records that started with 0.

Chris

kilika · August 21st, 2003, 11:55 AM

I guess the basics of what I am thinking...Can I take data in a cursor, moodify it so I have my own new set of data (this all happens in a stored proc) and then return this modified or created set of data?

Chris

Jeff Mason · August 21st, 2003, 12:18 PM

How do you know which day the process starts? That is, from your event data you posted, how do you know which day the data is for? How do I distinguish Monday's processes from Tuesdays?

How do you distinguish the set of events for one process as you posted it:

Code:

Hour  Minute  Event
23    30      Process start
23    45      load charge
23    59      gunning
0     12      load charge 2

from this (hypothetical) set of events:

Code:

Hour  Minute  Event
0     12      Process start
23    30      load charge
23    45      gunning
23    59      load charge 2

where this particular process took a long time to start (but it all happened on the same day?)

It would appear that the only way you could would be to infer an event ordering from the physical order of the events as they arrived into the table. This then implies that you have some way of physically ordering these events. Do you?

To answer your original question:

Quote:

quote:
If I want to simplify my data and create and return a smaller set of data, how do I create it in the stored proc and then return the manipulated data?

A cursor is probably your most straightforward solution, given that you must make certain inferences based on physical order. You can create a cursor to process this physically ordered data, assuming you have a way to order it, extract from each row the times, save then in local variables, get the next row and compute the elapsed time by subtracting this row's values from the prior row's (saved in variables), then INSERT each result into a temporary table, then SELECT the temporary table to return a resultset back to the client.

You have some ugly little mod 60 arithmetic to do there. It might be better to construct an intermediate datetime variable by concatenating the hours and minutes with the start date of the process (assuming you can know that - it isn't clear from you post that you can), then you can use such things as datediff() to easily calculate duration.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · August 21st, 2003, 12:39 PM

I did not include all the fields. Those were just examples. I have an EventID and ProcessID in the event table. The ProcessID is a foreign key to the Process tables which has a Primary Key called ProcessID. These are just example names as some of the names are long and cryptic (another aggravating thing to wade through). As I said 2 posts ago, the process has a start time and an end time. if they are in the same day, then I am fine, but when they cross the midnight mark, I only know from the times of the events when they cross the midnight mark.

To answer your questions:
1: from the joined Process table that has the STARTDATE and ENDDATE specified.
2:same as 1
3:same as 1
4:there is a proccessID int he event table (Primary/Foreign Key relationship)
5:refer to 1 and 4

Hope it makes sense now.

Chris

Jeff Mason · August 21st, 2003, 01:34 PM

What's an EventID? I'm guessing that it is some kind of identity column, but what does it mean?

You say that 2 posts ago that the process has a start and end time, yet in this post you say you have a STARTDATE and ENDDATE. Which is it?

(2 posts ago I asked for DDL, because narratives can be imprecise and rather than simplifying things, can actually result in the confusion you are participating in. ;))

Unfortunately, I'm a bit of a design purist, so please forgive me if am horrified at the design here; I tend to fixate on addressing that. For example, note that it is a good thing that a process is never more than 24 hours, as there would be no good way to know which day a given hour/minute combination actually was on.

I think I already answered your main question, which is that you use the cursor to process the data in an appropriate order and calculate the durations from one row to the next in physical order, insert the resultant values into a temporary table (or in memory table variable if SQL Server 2000), then SELECT the temporary back to the client. Though a cursor can be updateable, you'll be constructing the cursor from existing table structures, which have no place (as I guess your structure may be) for the updated data. You have to create a place for the duration values you are computing, hence the temporary table.

You can calculate durations easily enough by constructing an intermediate datetime value by concatenating the process startdate (if you have one, see above) with the hour and minute of one row and then datediff the same from the next row. If the hour of the 'next' row is less than the prior row, use the enddate instead. This of course presumes you can unambiguously determine the correct order of the rows, as so far I haven't seen any evidence that you can.

We can continue tearing apart your current design, and after a fashion come up with a more rational one which may lend itself to a set-based solution which wouldn't need a cursor or temporary table, (and would probably be an order of magnitude faster depending on the volume of data) but given that you said you can't change the data source, that may not be worth it, other than as an academic exercise.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com