July 18, 2012 at 9:34 am
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?
Any help would be greatly appreciated!
July 18, 2012 at 9:55 am
How do you want to handle bus routes that spawn 2 days? Say, the bus starts at 11.45 PM and stops at 1 AM the next day ? How do want it to be captured?
I would rather build the 15 min increments as rows rather than columns. Easier to query and build.
July 18, 2012 at 10:04 am
If the route spans two days like in your example, 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?
Here's something that was started, converting the times to minutes to begin with, then evaluating if the starttime mins is between certain numbers, BUT, that would only work if each hour between the start and end times is defined. Otherwise, its only going to match on the actual start time and nothing else:
SELECT *
,(LEFT(dbo.FX_SmallDateTime_To_Time_Char4(Start_Time),2) * 60) + RIGHT(dbo.FX_SmallDateTime_To_Time_Char4(Start_Time),2) Start_Time_Min
,(LEFT(dbo.FX_SmallDateTime_To_Time_Char4(End_Time),2) * 60) + RIGHT(dbo.FX_SmallDateTime_To_Time_Char4(End_Time),2) End_Time_Min
INTO #temp
FROM [dbo].[Schedules_RC]
SELECT *,
CASE WHEN Start_Time_Min BETWEEN 524 AND 539 THEN
CASE WHEN (End_Time_Min - Start_Time_Min) >= 15 THEN 15 ELSE (End_Time_Min - Start_Time_Min) END
ELSE 0
END [0845]
,CASE WHEN Start_Time_Min BETWEEN 540 AND 555 THEN
CASE WHEN (End_Time_Min - Start_Time_Min) >= 15 THEN 15 ELSE (End_Time_Min - Start_Time_Min) END
ELSE 0
END [0900]
,CASE WHEN Start_Time_Min BETWEEN 556 AND 570 THEN
CASE WHEN (End_Time_Min - Start_Time_Min) >= 15 THEN 15 ELSE (End_Time_Min - Start_Time_Min) END
ELSE 0
END [0915]
,(End_Time_Min - Start_Time_Min)
FROM #temp
In this particular example above, the table has a route that starts at 9am and ends at 1130am. With this query, it shows 0845 = 0, 0900 = 15, 0915 = 0. Not what i wanted.
I think this task might be getting above and beyond my humble sql skills...
July 18, 2012 at 10:31 am
Without ddl, sample data and desired output nobody can do much to help. At best we can take a shot in the dark and see if it might work. If you can provide this information we can help...otherwise the best you will get is vague attempts. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2012 at 10:56 am
The table this query would be pulling from is literally just 3 columns: Route INT, Starttime SMALLDATETIME, Endtime SMALLDATETIME. The start/end time could be in some other format if it works better, that's just how it is now.
So the data that this query is pulling from is pretty flexible, but very simple.
Does that help?
July 18, 2012 at 11:05 am
tacy.highland (7/18/2012)
The table this query would be pulling from is literally just 3 columns: Route INT, Starttime SMALLDATETIME, Endtime SMALLDATETIME. The start/end time could be in some other format if it works better, that's just how it is now.So the data that this query is pulling from is pretty flexible, but very simple.
Does that help?
No offense meant but look at what you posted and ask yourself if you could put together a query against what you posted. There are no tables and no data. I can turn the description into a table...
create table #Data
(
Route INT,
Starttime SMALLDATETIME,
Endtime SMALLDATETIME
)
OK so now what about some sample data for this table? That might look like this:
insert #Data
select 1, GETDATE(), DATEADD(n, 145, getdate()) union all
select 1, DATEADD(n, - 145, getdate()), DATEADD(n, 15, getdate())
And what do you want as the output? I have no idea what you are looking for here.
The point is we are all volunteers around here and everything you can do to make it easier for us will help you because we can spend our time (which is often just a few spare minutes between tasks during our regular jobs) working on your problem instead of setting up the scenario.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2012 at 11:57 am
if you change your data structure and go with a pivot for the times to get them lined up you can use something like this:
CREATE TABLE busroutetime (
BusRoute INT,
TimeOfDay INT,
InService INT
)
DECLARE @Start DATETIME = '2012-07-07 09:00'
DECLARE @End DATETIME = '2012-07-07 10:30'
SELECT TOP (DATEDIFF(MINUTE,@Start,@End) / 15) 1234, DATEADD(MINUTE,15 * N,@Start), 15
FROM Tally
1234 is the route number which you could get from your route table, @start and @End are the start and end times from your route table and 15 is the number of min it was in service. now this only calculates whole 15 min incriments and you will have to adjust it to get your partial min but it works for a bulk of the problem. im still working on it to get the partial min
i used a 0 based tally table which you can read about here http://www.sqlservercentral.com/articles/T-SQL/62867/ Or use something like this to create a cteTally table:
WITH 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)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 18, 2012 at 12:29 pm
Thanks so much.
However, I ran into issues recreating your cteTally table. I took a look at the link you provided for creating the tally table but I started getting lost...
July 18, 2012 at 12:39 pm
tacy.highland (7/18/2012)
Thanks so much.However, I ran into issues recreating your cteTally table. I took a look at the link you provided for creating the tally table but I started getting lost...
What were the issues? Something like this?
Msg 319, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
That means you MUST terminate the preceding statement with a semicolon. You could also put a semicolon in front of the keyword WITH.
;WITH 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)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2012 at 12:56 pm
Ok. So that kind of works.
I'm trying to build it as a matrix, with the route number as a row, and each 15 min increment (for the whole day) listed as a column. Then the 15 would go into each timeframe that is relavant, like your query does, but leaves a 0 in the timeframes where that route is not in service. The final output would be a column for ROute#, then display all the columns for each time increment of the whole day: 0500, 0515, 0530, 0545, etc. And each row would show the route number and the mins (15 or whatever minutes it's in service in that 15 min increment) that it's in service in each time increment for the day.
If it helps, there's a definite list of columns for each timeframe so if those columns were defined in the table could the code be modified in some way to display that way?
Plus, there are going to be multiple routes in this table, so I'm assuming this type of code would have to be in a loop or something to loop through each route in the Schedules table and determine what hour/increments each route is actually in service.
Thanks so much!!
July 18, 2012 at 1:08 pm
So I found another query that does something similar, but includes every 15 min increment of the day, and it's still in the wrong format:
Declare @Schedule table (Starttm datetime, endtm datetime)
Insert @Schedule values ( '2012-02-16 02:00:00', '2012-02-16 04:00:00')
, ('2012-02-16 02:00:00', '2012-02-16 05:00:00')
, ('2012-02-16 03:00:00', '2012-02-16 06:00:00')
, ('2012-02-16 08:00:00', '2012-02-16 09:00:00')
Select *, (Select count(*) from @Schedule where Incr between StartTm and Endtm) as onclock -- DateAdd(ms, -3, Endtm)
from
(Select Seq, dateadd(minute, seq * 15, '2012-02-16') as Incr -- Add 15 minutes 95 times, to make up a whole day.
from
(
select top 95 row_number() over(partition by 1 order by A.name) as Seq
from syscolumns A -- This is one quick and dirty way for a list of sequential numbers
) as DQ
) as DQ1
order by incr
It's not entering the 15 for each 15 min increment that the "route" would be in service but I don't understand the code well enough to modify to fit what I'd need...
July 18, 2012 at 1:12 pm
tacy.highland (7/18/2012)
Ok. So that kind of works.I'm trying to build it as a matrix, with the route number as a row, and each 15 min increment (for the whole day) listed as a column. Then the 15 would go into each timeframe that is relavant, like your query does, but leaves a 0 in the timeframes where that route is not in service. The final output would be a column for ROute#, then display all the columns for each time increment of the whole day: 0500, 0515, 0530, 0545, etc. And each row would show the route number and the mins (15 or whatever minutes it's in service in that 15 min increment) that it's in service in each time increment for the day.
If it helps, there's a definite list of columns for each timeframe so if those columns were defined in the table could the code be modified in some way to display that way?
Plus, there are going to be multiple routes in this table, so I'm assuming this type of code would have to be in a loop or something to loop through each route in the Schedules table and determine what hour/increments each route is actually in service.
Thanks so much!!
Still no sample data or desired output.
In order to get a column for each of your timeslots you need to either have table of timeslots or use a tally like Cap'n showed you above. You can modify the example pretty easily to get all 96 of the 15 minute timeslots in a day.
DECLARE @Start DATETIME = '7/18/2012'
DECLARE @End DATETIME = '7/19/2012'
SELECT TOP (DATEDIFF(MINUTE,@Start,@End) / 15) DATEADD(MINUTE,15 * N,@Start) as TimeSlot
FROM Tally
order by DATEADD(MINUTE,15 * N,@Start)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2012 at 1:34 pm
Here's sample data:
CREATE TABLE #mytable
(
Route INT
, StartTime DATETIME
, EndTime DATETIME
)
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable
(Route
, StartTime
, EndTime
)
SELECT '601','Jul 17 2012 9:00AM','Jul 17 2012 11:30AM'
UNION ALL
SELECT '602','Jul 17 2012 9:15AM','Jul 17 2012 12:00PM'
SET IDENTITY_INSERT #mytable OFF
Other than my previous explanations, I don't know how else to actually illustrate the expected output. If I knew how to post a picture/image, I'd include a screenshot of what I was hoping to achieve.
The output needs to have all 15 min increments of the day listed as columns. For each row in the table, (which would be a single route), there would be a number entered in each of those columns indicating the number of minutes that vehicle is in service during that specific 15 min increment, based off of the starttime and endtime in the table specified above. Output from above data would be 2 rows with 96 columns (95 for each 15 min increment plus 1 for route#). Every column would have a number in it between 0 to 15.
Does that explain it any better?
July 18, 2012 at 1:39 pm
ok after much work here is the table insert and pivot sample to get you the output you want. this assumes much so use what you will and dont complain if its not right since i had to make some assumptions.
first is the table structure, i used a normalized table with a pivot to get the output you want. makes things cleaner in my mind, here is the table:
CREATE TABLE BusRouteTime (
BusRoute INT,
TimeOfDay TIME,
InService INT
)
--and the assumed BusRoutes table
CREATE TABLE BusRoutes(
RouteNumber INT,
StartTime DATETIME,
EndTime DATETIME)
--And some sample date to work with in the BusRoutes Table
INSERT INTO BusRoutes VALUES (1234,'2012-07-07 09:32','2012-07-07 10:18')
INSERT INTO BusRoutes VALUES (5678,'2012-07-07 09:47','2012-07-07 10:28')
to calculate the time durring each 15 min segment we run the following
;WITH TimePart AS (SELECT RouteNumber, DATEPART(hour,StartTime) AS StartHour, DATEPART(MI,StartTime) AS StartMin,
DATEPART(hour,EndTime)AS EndHour, DATEPART(MI,EndTime) AS EndMin
FROM BusRoutes),
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)
--INSERT INTO busroutetime
SELECT RouteNumber, 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 Tally
WHERE N <= DATEDIFF(MI,StartTime,EndTime) / 15
after i uncomment the insert and actually insert data into BusRouteTime then run a pivot with the following:
SELECT BusRoute AS [Route], [9:30], [9:45], [10:00], [10:15]
FROM (
SELECT BusRoute, TimeOfDay, inservice
FROM busroutetime) p
PIVOT
( SUM(inservice)
FOR TimeOfDay IN ([9:30], [9:45], [10:00], [10:15])) AS PVT
i get the following output
Route 9:30 9:45 10:00 10:15
----------- ----------- ----------- ----------- -----------
1234 13 15 15 3
5678 NULL 13 15 13
you just need to expand on that to have all the time periods you want and may need to change it because you failed to provide any DDL or Sample data.
the reason for the 3 column table is that allows me to calculate the time and minuites active in the 15 min period then with a pivot i can get the data out how you want it. to add all the other 15 min periods you can just add them in the FOR x IN () block in the pivot.
EDIT: At least i guessed right on the DDL for the route table.
EDIT 2: LINKS
BOL PIVOT http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Tally Table http://www.sqlservercentral.com/articles/T-SQL/62867/ all examples use a 0 base tally table (0 is the first number)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 18, 2012 at 1:58 pm
Thank you so much. I think you might have gotten it, looking at what your output is, but when I tried running the code I'm getting an error: "Invalid object name 'Tally' ". Did I miss something?
Viewing 15 posts - 1 through 15 (of 81 total)
You must be logged in to reply to this topic. Login to reply