# question

## 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
sample.png (166.5 KiB)
sample1.png (8.0 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@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.
0 Likes 0 ·
·
@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
0 Likes 0 ·
·
@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.
0 Likes 0 ·
·
@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
0 Likes 0 ·
·
@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.
0 Likes 0 ·

·

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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.
0 Likes 0 ·
·
@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.
0 Likes 0 ·
·
@Oleg This is a perfect clarification and an excellent script. If there are 2 1200 records followed by 1 1201, we should ignore the 1st 1200 If there is a 1201 with no 1200 before then ignore the 1201 instance
0 Likes 0 ·
·
@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.
0 Likes 0 ·