SQL DateDiff where second field is equal

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL DateDiff where second field is equal

Author

Topic

AlainTV
Starting Member

5 Posts

Posted - 2011-12-20 : 01:08:09

Hi,

Relatively new to SQL

, and I need to come up with a query that will return the difference between two dates, to calculate usage.
The following is an extract of the data in MS SQL Server 2008:

Level            EventDate                   EventDetail                               EventPair
Level 5	   2011-08-02 18:00:00.000	   After Hours Mode Activated via card	        21800
Level 5    2011-08-02 20:30:00.000	   Timer expired	                        21800
Level 4    2011-08-03 21:00:00.000	   After Hours Mode Activated via Push Button	32100
Level 4	   2011-08-03 22:30:00.000	   Timer expired	                        32100
Level 5	   2011-08-10 18:00:00.000	   After Hours Mode Activated via card	        101800
Level 5	   2011-08-10 20:00:00.000	   Timer expired	                        101800

I know that using a SELECT with a DATEDIFF in minutes divided over 60.0, should return the right info. However, I'm not sure how to put it all together so that the query will look at the data, sort by level, then should look into the 'EventPair' column to ensure that are a match, as one row represents the start time and the other represent the end time, so that I can calculate the difference in hours for every matching pair of rows, I could then add the result together in the report as a group for each level.

Any help is appreciated.

Regards,

Alain

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-20 : 01:19:31

[code]
select Level, EventPair, datediff(minute, min(EventDate), max(EventDate))
from yourtable
group by Level, EventPair
[/code]

KH
[spoiler]Time is always against us[/spoiler]

AlainTV
Starting Member

5 Posts

Posted - 2011-12-20 : 04:01:20

Thanks so much.

I can't believe it was that easy.

Good thing I joined this forum.

Regards,

Alain

AlainTV
Starting Member

5 Posts

Posted - 2011-12-20 : 04:34:44

Hi KH,

While the suggested option works. I believe if we had multiple entries within the same date, the results would be wrong.

Also, what would be the best way to allow me to display the EventDate field without loosing the calculation.

The following returns the right result (provided there is only one entry within the same level and date):

SELECT Site, Level, Tenant, EventPair, (DATEDIFF(minute, min(EventDate), max(EventDate))/60.0)AS HrsUsage FROM TenantUsage GROUP BY Site, Tenant, Level, EventPair

As soon as I add the [EventDate] in the query the date is displayed and sorted, however, the 'HrsUsage' field stops returning the calculated date differences:

SELECT Site, Level, Tenant, EventDate, EventDetail, EventPair, (DATEDIFF(minute, min(EventDate), max(EventDate))/60.0)AS HrsUsage FROM TenantUsage GROUP BY Site, Tenant, Level, EventDate, EventDetail, EventPair

Any suggestions?

Regards,

Alain

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-20 : 04:48:00

quote:
While the suggested option works. I believe if we had multiple entries within the same date, the results would be wrong

Example ?


SELECT Site, Level, Tenant, EventPair, EventDate,
       (
            DATEDIFF(minute, 
                     min(EventDate) over (partition by Site, Tenant, Level, EventPair), 
                     max(EventDate) over (partition by Site, Tenant, Level, EventPair) )
             / 60.0 
       )AS HrsUsage 
FROM   TenantUsage

KH
[spoiler]Time is always against us[/spoiler]

AlainTV
Starting Member

5 Posts

Posted - 2011-12-20 : 05:33:20

Fantastic KH,

Exactly what I was after, and it fixes the issue of multiple entries within the same date.

Some much to learn in SQL. I will have to google about that 'over' function that you used with the DateDiff function, to understand in detail what it does.

Thanks again.

Regards,

Alain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources