asked
SQL 2000: Calculate elapsed time between different rows of same column
Have an old SQL 2000 DB... Want to see difference of time elapsed from same column based on having two separate values in another column. DECLARE @Now DATETIME, @Yesterday DATETIME, @StartTime VARCHAR(16), @EndTime VARCHAR(16) SET @StartTime = '01:00:00AM' SET @EndTime = '11:59:00PM' SET @Now = GETDATE() SET @Yesterday = DATEADD(dd, DATEDIFF(dd, 0, @Now), -1) select EventTime, Description, EventID from EventLog WHERE EventTime BETWEEN @Yesterday + ' ' + @StartTime AND @Yesterday + ' ' + @EndTime AND EventID in (1200 ,1201) AND Description LIKE 'Lane _ Full PE%' ![alt text][1] Would like to see the elapsed time SUM from `EventID` 1201 - 1200 as`TimeFull` Would then like to show this against a 10 hour day and see the Percentage of what we call call `Downtime%` Lets say SUM was `2:00:00` we would divide by `10:00:00` to show the below ![alt text][2] [1]: /storage/temp/4558-sample.png [2]: /storage/temp/4559-sample1.png
@druidruid Why do you have such a weird interval for yesterday? From the logic in your query it looks like you would like to select the rows for all day yesterday, meaning from 00:00:00.000 (midnight, start of yesterday) till 11:59:59.997 PM (last tick of yesterday), and yet you filter for the yesterday's period from 1 AM till 11:59 PM, thus losing the rows for the whole hour between midnight and 1 AM and also the rows for he whole last minute of yesterday. Please clarify. Thank you.
@Oleg Records will never be logged until approx 1:30:00 am and will always stop being logged at approx 10:00:00 pm. and am just showing for example. What i need help with is the structure of the query to produce that resultset. Im trying so many options and am stuck
@druidruid Oh, I see, now it is clear. Your task would be simpler if the SQL Server version was newer than 2000, but I know that it is possible even with 2000. I will try to come up with something later this evening. May I assume that the event records always alternate over time so there is always 1200, then 1201, and next 1200 is going to be later, always after the previous 1201? If this is the case then the solution is going to be pretty straightforward.
@Oleg Ahhh, good
question.In a perfect situation ( where i do not have humans interacting with equipment and falsly cause a 1200 or 1201 event) then yes, but i am sure it will happen. lets say there is a 1200 and no 1201 after, could we factor that in to the equation (Every 1200 must have a 1201 in order to calculate )? I assume that would throw the SUM off entirely if this wasnt accounted for. Thanks for your help
@druidruid I made modifications to the query in my answer to include handling of the situation with possible multiple 1201 records which don't have their start event row (1200). The script should produce expected results, but please let me know whether it does or not. Thank you.
This task would be much easier in any version of SQL Server newer than 2000. This is because it would be easy enough to select the records for 1201 event and for each row snoop into the previous row to read the value of the event time. It would be also much easier to write a comprehensive check routine to ensure that the data is clean before proceeding with calculation of the total down time and its percentage. I do not have SQL Server 2000 instance handy, so I hope that the script below works. The logic is as follows: perform some rudimentary check of the rows (ID 1200 and 1201), namely the first row has to be for event 1200, the last row has to be for event 1201 and the count of both events must match. I use the period from 1 AM yesterday till 3 milliseconds before midnight, covering 23 hours period of yesterday. Datetime data type has accuracy of 3 milliseconds, and the only values that the rightmost digit of the datetime value can have is 0, 3, or 7, so 23:59:59.997 is the last tick before midnight. To calculate percentage, I sum up the difference in milliseconds between start and end of the event for each row and then divide resulting number by the number of milliseconds in 10 hours period multiplied by 100 (for percentage), so 10 times 3,600,000 divided by 100 = 360,000. This is the number used as the divider. Please change it if the total number of hours to consider is different from 10. Here is the script which should work in SQL Server 2000 (or so I hope): declare @start datetime; declare @end datetime; declare @firstID int; declare @lastID int; declare @countOf1200 int; declare @countOf1201 int; -- set the @start to 1 AM yesterday and @end to EOD yesterday (almost midnight today) select @start = dateadd(hour, -23, dateadd(day, datediff(day, 0, getdate()), 0)), @end = dateadd(millisecond, -3, dateadd(day, datediff(day, 0, getdate()), 0)); -- Check the events. First event must be 1200 and last 1201. Also, the number of events -- for any given day period should match. These checks might be too cursory, and may be -- expanded for something more comprehensive if need to be. select top 1 @firstID = EventID from #EventLog where EventTime between @start and @end and EventID in (1200, 1201) and [Description] like 'Lane _ Full PE%' order by EventTime; select top 1 @lastID = EventID from #EventLog where EventTime between @start and @end and EventID in (1200, 1201) and [Description] like 'Lane _ Full PE%' order by EventTime desc; select @countOf1200 = sum(case when EventID = 1200 then 1 else 0 end), @countOf1201 = sum(case when EventID = 1201 then 1 else 0 end) from #EventLog where EventTime between @start and @end and EventID in (1200, 1201) and [Description] like 'Lane _ Full PE%'; if isnull(@firstID, 0) 1200 raiserror('First event of the day is not 1200', 16, 1); if isnull(@lastID, 0) 1201 raiserror('Last event of the day is not 1201', 16, 1); if isnull(@countOf1200, -1) isnull(@countOf1201, 0) raiserror('Counts don''t match', 16, 1); -- if any errors were triggered then add some exit routine if needed -- final select to calculate the downtime select sum(datediff(millisecond, StartEventTime, EndEventTime)) Diff, convert(varchar(8), dateadd(millisecond, sum(datediff(millisecond, StartEventTime, EndEventTime)), 0), 108 ) TimeFull, cast( sum(datediff(millisecond, StartEventTime, EndEventTime)) / 360000. as decimal(10, 2) ) DownTimePct from ( -- running this will show how the script works, pulling the start time from prev event select e.EventTime as EndEventTime, ( select max(EventTime) from #EventLog where EventID = 1200 and EventTime < e.EventTime and [Description] like 'Lane _ Full PE%' ) as StartEventTime from #EventLog as e where e.EventTime between @start and @end and e.EventID = 1201 and e.[Description] like 'Lane _ Full PE%' ) t; Based on the sample data in question (after I up it by 5 days to bring the date parts to yesterday relative to today's date of March 14) the query above returns the following results: Diff TimeFull DownTimePct ----------- ---------- ---------------------- 11168749 03:06:08 31.02 **Edit to hand the scenario when there are multiple 1201 events for a single 1200.** The way the original script above was written already handled the situation with multiple 1200 events followed by the single 1201. In this case, the script would disregard all consecutive 1200 events except the last one. In order to handle the situation with the single 1200 followed by multiple 1201 records, there is a need to figure out the start of the previous event while looking at the current one. This will allow a comparison between the times of the start of event and end of the previous event. Requiring that the start of event must be later then the end of the previous allows filtering out of all consecutive 1201 rows except the first one because only the first one has a legitimate start of event row (event 1200), others don't. Again, because the windowing and analytical functions are not supported in SQL Server 2000, the script is reduced to the usage of another correlated subquery. This will make the script even heavier that it was before, but considering that the number of the records when filtered for only one day and 2 specific events is not huge, the performance of the script should be acceptable. Here is the modified script which will handle the requirement to disregard "extra" 1200 and 1201 records: declare @start datetime; declare @end datetime; -- set the @start to 1 AM yesterday and @end to EOD yesterday (almost midnight today) select @start = dateadd(hour, -23, dateadd(day, datediff(day, 0, getdate()), 0)), @end = dateadd(millisecond, -3, dateadd(day, datediff(day, 0, getdate()), 0)); -- final select to calculate the downtime select sum(datediff(millisecond, StartEventTime, EndEventTime)) Diff, convert(varchar(8), dateadd(millisecond, sum(datediff(millisecond, StartEventTime, EndEventTime)), 0), 108 ) TimeFull, cast( sum(datediff(millisecond, StartEventTime, EndEventTime)) / (datediff(millisecond, min(StartEventTime), max(EndEventTime)) / 100.) as decimal(10, 2) ) DownTimePct from ( -- running this will show how the script works, pulling the start time from prev event select e.EventTime as EndEventTime, ( select max(EventTime) from EventLog where EventID = 1200 and EventTime < e.EventTime and [Description] like 'Lane _ Full PE Full%' ) as StartEventTime, ( select max(EventTime) from EventLog where EventID = 1201 and EventTime < e.EventTime and [Description] like 'Lane _ Full PE Clear%' ) as EndPrevEventTime from EventLog as e where e.EventTime between @start and @end and e.EventID = 1201 and e.[Description] like 'Lane _ Full PE Clear%' ) t where t.StartEventTime > isnull(t.EndPrevEventTime, 0); go Hope this helps. Oleg
I first run this and get the 'Counts don't match error' I changed LINE 58 [Description] like 'Lane _ Full PE%' to [Description] like 'Lane _ Full PE Full%' there are other events that can throw EventID of 1200 that we dont want to look at LINE 64 e.[Description] like 'Lane _ Full PE%' to e.[Description] like 'Lane _ Full PE Clear%' there are other events that can throw EventID of 1201 that we dont want to look at After the that This looks to be acheiving exactly what i wanted, Thanks I do have a couple questions if you can elaborate. 1. What will happen in the event the either of the 3 raiserror are flagged? Will we not continue with query? If not, how can we only calculate where every 1200 has a 1201 and forget the ones that don't just so we can move on and show results of what actually have a pair? 2. Are we calculating the DownTimePct based off the very first 1200 of the day and the very last 1201 of the day as opposed to a 10 hour day? If this is the case then perfect. It actually would be most accurate for my report to show DownTimePct based off time elapsed from first 1200 and last 1201. I really appreciate all your help and hope i am conveying my questions adequately.
@druidruid I did not include any error handling, leaving it up to you, so the final query in my answer should produce correct result only if there are complete pairs. Yes, it is easy to use the actual interval from the first 1200 till last 1201 instead of hardcoded 10 hours, all you need to do is replace the hardcoded value currently reading **360000.** to read this instead: (datediff(millisecond, min(StartEventTime), max(EndEventTime)) / 100.) Please note that the dot after 100 is a must, otherwise you will always get 0 for your percentage due to integer division. If you know ***exactly what needs to happen*** if the pairs are not perfect (some are missing), please let me know and I can try to accommodate the script. For example, suppose there are 2 1200 records before their respective closing 1201 appears. What would you like to happen? I would argue that in this case the 1200 which is sandwiched in the middle has to be ignored, but this is just IMHO. Maybe you want to ignore the first 1200 instead. Most importantly, how the hanging 1200 (without the last 1201 counterpart) should be handled? The way it is written now it will be simply ignored. Please let me know.
@druidruid The scenario with 2 (or more) 1200 records followed by 1 1201 is already handled by the script because it selects just the 1201 records and then finds the last 1200 before each so if there is more than one 1200 they are all ignored (except the latest one). I will have to modify the script to handle the situation when there are multiple 1201 records without corresponding 1200. The way the script works now in this case, it just picks the "latest available 1200", which means that the script tends to overstate the downtime percentage if there are any such consecutive 1201 records without matching 1200 for each. The modification will add some complexity, but again, this is only because the SQL Server version is 2000, the one which does not have a support for any windowing and analytical features available in the later versions. I will not have time to do it now, but will try later today.
18 People are following this question.
