Calculating duration where outside search range

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

Calculating duration where outside search range

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-09 : 02:59:50

I need to calculate time durations limited to search boundaries. Here is an example of what I mean.

Using SQL 2005 Business Intelligence Development Studio - Visual Studio 2005 for the first time. Implementing in Reporting Services.

User enters a date range as @sourcestarttime and @sourceendtime using report parameters. For this example the user enters 1/2/2008 to 29/2/2008. In the final report I need it to work for specific year, month, day, hour or specific date range as used in this example.

Data in TimeDetails table:
TimeID SourceStartTime SourceEndTime
1 2/2/2008 10:00:00AM 3/2/2008 11:00:00AM
2 31/1/2008 11:00:00PM 1/2/2008 01:00:00AM
3 29/2/2008 11:00:00PM 1/2/2008 01:00:00AM
4 29/2/2008 11:00:00PM NULL
5 31/1/2008 11:00:00PM 1/3/2008 01:00:00AM
6 31/1/2008 11:00:00PM NULL

The total duration for this report should be 59 days and 4 hours.

1 1day 1hour (report duration 1 day 1 hour - start and end within range)
2 2hours (report duration 1 hour ie from midnight to 1AM - start not within range
3 2hours (report duration 1 hour ie from 11PM to midnight - end not within range)
4 NA (report duration 1 hour ie from 11PM to midnight - no end date)
5 29days and 2hours (report duration 29 days - start and end date not within range)
6 NA (report duration 29days - no end date and start date not within range)

Here is what I have so far which is fine if sourcestarttime and sourceendtime is within @sourcestarttime and @sourceendtime. Also calculating totalseconds as a field and then using that to calculate seperate fields for days, hours, minutes, seconds. Also I am filtering by the SiteName field so the TimeID's (key) in the TimeDetails table are linked to certain SiteID's (key) in the SiteDetails table and hence the SiteName field in the table. This means the total duration is for each SiteName in the SiteDetails table.

SELECT SUM(Duration.TotalSeconds) / 86400 AS Days, SUM(Duration.TotalSeconds) % 86400 / 3600 AS Hours, SUM(Duration.TotalSeconds) % 3600 / 60 AS Minutes, SUM(Duration.TotalSeconds) % 60 AS Seconds, TimeDetails_1.TimeID, Duration.TotalSeconds
FROM(SELECT TimeID, DATEDIFF(second, SourceStartTime, SourceEndTime) AS TotalSeconds
FROM TimeDetails)AS Duration INNER JOIN
TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID INNER JOIN
EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID INNER JOIN
SystemState ON EventDetails.EventID = SystemState.EventID INNER JOIN
SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE(TimeDetails_1.SourceStartTime >= @SourceStartTime) AND (SiteDetails.SiteName IN (@SiteName)) AND
(TimeDetails_1.SourceStartTime <= @SourceEndTime + DAY(0))
GROUP BY TimeDetails_1.TimeID, Duration.TotalSeconds

I hope I have provided the correct detail.

Thankyou in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources