Tracking time in 15 min increments? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

Tracking time in 15 min increments?

Author

Topic

tacec
Starting Member

8 Posts

Posted - 2012-07-18 : 12:07:13

I have an interesting query to put together and have a general idea but wonder if someone out there might have some input to better handle this. I'm trying to build a dataset that shows, in 15 min increments, the actual minutes that a bus route is out on the road. I have a table with each route's start and end times which is the basis for the query. For example, if a route starts at 9am and ends at 10am, the dataset I'm looking for should look something like this: (not easy to simulate here)

ROUTE |830 |845 |900 |915 |930 |945 |1000 |1015
5010 |0 |0 |15 |15 |15 |15 |0 |0

It gets trickier when the route doesn't start or end exactly on the 15, like say, it starts at 0906, then in the 900 column, it would be 9 (because its only out for 9 of the 15 mins from 0900 to 0915).

Is there a function or something out there that will do this calculation for me? Or is there some code that someone knows about that might help?

If the route spans two days, it should still just show how many minutes it's in service for each 15 increment. Like at 1145 = 15, 1200 = 15, 1215 = 15...all the way to 1am. So it shouldn't matter if it spans 2 days or not, it should just calculate the hours/mins its in service.

I thought it might be necessary to calculate each hour between the start/end time to start with, and set a 15 in each hour for those hours, then look at the minutes and adjust accordingly...? Like have a cursor or something?

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 12:11:44

this should give you start i guess

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tacec
Starting Member

8 Posts

Posted - 2012-07-18 : 18:15:19

Thanks for the link. I actually have something I got from another forum that I think is pretty darn close to what I need. However, there's some calculation/subtraction issue occuring that I'm not clear on. Here's the code:

CREATE TABLE #BusRouteTime
(
BusRoute INT,
TimeOfDay TIME,
InService INT
)
--and the assumed BusRoutes table
CREATE TABLE #Schedules_RC(
Route INT,
Start_Time DATETIME,
End_Time DATETIME)

--And some sample date to work with in the BusRoutes Table
INSERT INTO #Schedules_RC VALUES (601,'2012-07-07 09:32','2012-07-07 10:18')
INSERT INTO #Schedules_RC VALUES (602,'2012-07-07 09:47','2012-07-07 10:28')
INSERT INTO #Schedules_RC VALUES (603,'2012-07-07 10:32','2012-07-07 14:26')
INSERT INTO #Schedules_RC VALUES (604,'2012-07-07 14:25','2012-07-07 17:30')
INSERT INTO #Schedules_RC VALUES (605,'2012-07-07 09:31','2012-07-07 09:44')

--Increments table
;WITH TimePart AS (SELECT Route, DATEPART(hour,Start_Time) AS StartHour, DATEPART(MI,Start_Time) AS StartMin,
DATEPART(hour,End_Time)AS EndHour, DATEPART(MI,End_Time) AS EndMin
FROM Schedules_RC)
,StartEnd AS (SELECT *, CAST(CAST(StartHour AS VARCHAR) + ':' + CAST ( CASE WHEN StartMin % 15 = 0 THEN StartMin
ELSE (StartMin / 15) * 15 END AS VARCHAR) AS Time) AS StartTime,
CAST(CAST(EndHour AS VARCHAR) + ':' + CAST ( CASE WHEN EndMin % 15 = 0 THEN EndMin
ELSE ((EndMin / 15)) * 15 END AS VARCHAR) AS Time) AS EndTime
FROM TimePart)
,E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 )
,E2(N) AS (SELECT 1 FROM E1 a, E1 b)
,cteTally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E2)

INSERT INTO #busroutetime
SELECT [Route], DATEADD(mi,15*N,StartTime)
, CASE WHEN StartTime BETWEEN DATEADD(mi,15*N,StartTime) AND DATEADD(mi,15*N+1,StartTime)
THEN 15 - StartMin % 15
WHEN EndTime BETWEEN DATEADD(mi,15*N-1,StartTime) AND DATEADD(mi,15*N,StartTime)
THEN EndMin % 15
ELSE 15 END
FROM StartEnd
CROSS JOIN cteTally
WHERE N <= DATEDIFF(MI,StartTime,EndTime) / 15

SELECT BusRoute AS [Route]
,[7:00],[7:15],[7:30],[7:45]
,[8:00],[8:15],[8:30],[8:45]
,[9:00],[9:15],[9:30],[9:45]
,[10:00],[10:15],[10:30],[10:45]
,[11:00],[11:15],[11:30],[11:45]
,[12:00],[12:15],[12:30],[12:45]
,[13:00],[13:15],[13:30],[13:45]
,[14:00],[14:15],[14:30],[14:45]
,[15:00],[15:15],[15:30],[15:45]
,[16:00],[16:15],[16:30],[16:45]
,[17:00],[17:15],[17:30],[17:45]
FROM (
SELECT BusRoute, TimeOfDay, inservice
FROM #busroutetime) p
PIVOT
( SUM(inservice)
FOR TimeOfDay IN (
[7:00],[7:15],[7:30],[7:45]
,[8:00],[8:15],[8:30],[8:45]
,[9:00], [9:15], [9:30], [9:45]
, [10:00], [10:15], [10:30], [10:45]
,[11:00],[11:15],[11:30],[11:45]
,[12:00],[12:15],[12:30],[12:45]
,[13:00],[13:15],[13:30],[13:45]
,[14:00],[14:15],[14:30],[14:45]
,[15:00],[15:15],[15:30],[15:45]
,[16:00],[16:15],[16:30],[16:45]
,[17:00],[17:15],[17:30],[17:45])) AS PVT

DROP TABLE #BusRouteTime,#Schedules_RC

When a route starts and ends in the same 15 min increment (ie starts at 0900 and ends at 0914), it doesn't calculate the minutes properly. See route 605 in the example above. Another thing I noticed is if you change the StartTime it will adjust the minutes in that case (still not right), but not if you change the end time. Otherwise it looks like it's doing what it needs to.

Anyone have any ideas why this is happening?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources