July 18, 2012 at 2:00 pm
tacy.highland (7/18/2012)
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?
yes tally is my tally table. you can either create a tally table or use the cteTally i posted earlier.
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 2:11 pm
My apologies, I've not used cte's before but I know something about how they need to be used after they're created. But I'm just not putting that tally table in the right place somehow. Based on your code, this is what i did:
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')
--Tally 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)
--Increments table
;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 cteTally
WHERE N <= DATEDIFF(MI,StartTime,EndTime) / 15
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
drop table #BusRouteTime, #BusRoutes
I'm so grateful for your excellent help on this, it's been so very much appreciated, so I apologize for my less than stellar SQL understanding. I'm excited to see this coming closer to being finalized!
Thanks again!
July 18, 2012 at 2:28 pm
i do hope you go through and follow through the logic of what i did and don't just blindly use it. research CTE's and Pivot tables and try to recreate what i did from scratch. the way i learned most of what i know for SQL is by reading the forums and stepping through the posted code. it really helped to accelerate my learning.
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 3:34 pm
I got it. (Didn't think about adding the original Tally cte into the existing one.)
One question on how it's calculating: If i have a route starting at 0900 and ending at 0914, it shows 15 in the 0900 column when it should show 14. That seems to only happen to the routes that start and end in the same 15 min increment, (if the entire routetime is less than 15 minutes). Is that somehow inherent in how the time subtraction is happening or is there something I could adjust so that it's calculating those instances more precisely?
This is excellent stuff. Thank you so much again. Obviously I did not have the SQL skill to put it together myself so your expertise is very much appreciated.
July 18, 2012 at 11:37 pm
Took me a while to accomplish this. It turned out that the hardest part was getting the select criteria correct to properly align the schedule times to the buckets.
Please take a look and let me know what you you all think.
declare @ReportDate DATE = '2012-07-18';
WITH
/* Create the dynamic tally table (0 based) */
e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b),
tally(n) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2 a CROSS JOIN e2 b),
/* Create the Time Buckets and the Start/End times to be used in time computations */
TimeBuckets(DailyTime, DailyBeginTime, DailyEndTime) AS (
SELECT TOP(DATEDIFF(MI,@ReportDate,DATEADD(dd,1,@ReportDate))/15)
CAST(DATEADD(mi,15 * n, CAST(@ReportDate AS DATETIME)) AS TIME(0)),
DATEADD(mi,15 * n, CAST(@ReportDate AS DATETIME)),
DATEADD(mi,15 * (n + 1), CAST(@ReportDate AS DATETIME))
FROM
tally
),
/* This CTE is the test data and can be removed from the query by replacing the source table in DailyBusRoutes CTE */
TestData(RouteNumber, StartTime, EndTime) AS
(SELECT
RouteNumber,
StartTime,
EndTime
FROM
(VALUES (1234,'2012-07-18 09:00:00','2012-07-18 13:00:00'),
(5678,'2012-07-18 10:13:00','2012-07-18 14:28:00'),
(1321,'2011-07-17 23:30:00','2012-07-18 06:30:00'),
(5342,'2012-07-18 21:14:00','2012-07-19 03:36:00'),
(6565,'2012-07-17 12:12:00','2012-07-17 20:42:00'),
(9897,'2012-07-19 06:12:00','2012-07-19 14:34:00')
)dt(RouteNumber, StartTime, EndTime)
WHERE
@ReportDate between cast(StartTime as DATE) and cast(EndTime as DATE)
),
/*********************************************************************************************************************/
/* DailyBusRoutes provides the data that will be pivoted. Replace the CTE TestData with the source table */
DailyBusRoutes as (
SELECT
left(cast(tb.DailyTime as varchar(10)),5) DailyTime,
td2.RouteNumber,
isnull(datediff(mi,case when td.StartTime > tb.DailyBeginTime then td.StartTime else tb.DailyBeginTime end,
case when td.EndTime > tb.DailyEndTime then tb.DailyEndTime else td.EndTime end),0) as TimeScheduled
FROM
TimeBuckets tb
CROSS JOIN TestData td2
OUTER apply (SELECT
*
FROM TestData td1
WHERE
(td1.RouteNumber = td2.RouteNumber) AND
(tb.DailyEndTime > td1.StartTime AND tb.DailyBeginTime < td1.EndTime )) td
)
select ReportDate,
RouteNumber as [Route],[00:00],[00:15],[00:30],[00:45],
[01:00],[01:15],[01:30],[01:45],
[02:00],[02:15],[02:30],[02:45],
[03:00],[03:15],[03:30],[03:45],
[04:00],[04:15],[04:30],[04:45],
[05:00],[05:15],[05:30],[05:45],
[06:00],[06:15],[06:30],[06:45],
[07:00],[07:15],[07:30],[07:45],
[08:00],[08:15],[08:30],[08:45],
[09:00],[09:15],[09:30],[09: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],
[18:00],[18:15],[18:30],[18:45],
[19:00],[19:15],[19:30],[19:45],
[20:00],[20:15],[20:30],[20:45],
[21:00],[21:15],[21:30],[21:45],
[22:00],[22:15],[22:30],[22:45],
[23:00],[23:15],[23:30],[23:45]
from (
select RouteNumber,DailyTime,TimeScheduled
from DailyBusRoutes) p
pivot (sum(TimeScheduled) for DailyTime in ([00:00],[00:15],[00:30],[00:45],
[01:00],[01:15],[01:30],[01:45],
[02:00],[02:15],[02:30],[02:45],
[03:00],[03:15],[03:30],[03:45],
[04:00],[04:15],[04:30],[04:45],
[05:00],[05:15],[05:30],[05:45],
[06:00],[06:15],[06:30],[06:45],
[07:00],[07:15],[07:30],[07:45],
[08:00],[08:15],[08:30],[08:45],
[09:00],[09:15],[09:30],[09: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],
[18:00],[18:15],[18:30],[18:45],
[19:00],[19:15],[19:30],[19:45],
[20:00],[20:15],[20:30],[20:45],
[21:00],[21:15],[21:30],[21:45],
[22:00],[22:15],[22:30],[22:45],
[23:00],[23:15],[23:30],[23:45])) as pvt
cross join (select @ReportDate) rpt(ReportDate);
July 19, 2012 at 6:40 am
tacy.highland (7/18/2012)
I got it. (Didn't think about adding the original Tally cte into the existing one.)One question on how it's calculating: If i have a route starting at 0900 and ending at 0914, it shows 15 in the 0900 column when it should show 14. That seems to only happen to the routes that start and end in the same 15 min increment, (if the entire routetime is less than 15 minutes). Is that somehow inherent in how the time subtraction is happening or is there something I could adjust so that it's calculating those instances more precisely?
This is excellent stuff. Thank you so much again. Obviously I did not have the SQL skill to put it together myself so your expertise is very much appreciated.
that was because the query was not hitting any of the case statemnts and going to the else. here is a fixed query and i over looked it because i made the assumption (from my experience with public transportation) that the routes would all be over 15 min. here is the fixed query
SELECT RouteNumber, DATEADD(mi,15*N,StartBase), CASE WHEN StartBase = EndBase THEN EndMin - StartMin
WHEN StartBase = DATEADD(mi,15*N,StartBase) THEN 15 - StartMin % 15
WHEN EndBase = DATEADD(mi,15*N,StartBase) THEN EndMin % 15
ELSE 15 END
FROM StartEnd
CROSS JOIN Tally
WHERE N <= DATEDIFF(MI,StartBase,EndBase) / 15
i also realized that i was actually using the 15 min base and not the actual start or end time (it was working so i did not test much, that will teach me) so i dropped the between and just have an =.
Lynn's solution is actually better as i did not take into account bus routes running longer than 24 hours. more assumptions and ways my solution breaks.
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 19, 2012 at 9:59 am
This is great stuff. Thanks everyone for your contributions.
I have another question now. Since we've been using smalldatetime as the startime and endtime format, (but I see it's being converted to varchar at one point in the process and then converted to time), would it make much difference if the startime/endtime were actually varchar of just time, like this: 0900, 1700? Rather than the DatePart being done in the TimePart cte, we could just do LEFT(startime,2) and RIGHT(startime,2) and the code would work the same, right? It looked like everything beyond that was doing the proper conversions for calculations.
July 19, 2012 at 10:08 am
tacy.highland (7/19/2012)
This is great stuff. Thanks everyone for your contributions.I have another question now. Since we've been using smalldatetime as the startime and endtime format, (but I see it's being converted to varchar at one point in the process and then converted to time), would it make much difference if the startime/endtime were actually varchar of just time, like this: 0900, 1700? Rather than the DatePart being done in the TimePart cte, we could just do LEFT(startime,2) and RIGHT(startime,2) and the code would work the same, right? It looked like everything beyond that was doing the proper conversions for calculations.
Store date/time values as date/time values. Sorry, but I strongly believe that you should use the appropriate data type for the data. Yes, there are conversions being done to character values and such, but the base data should still be the appropriate data type for the data.
July 19, 2012 at 10:13 am
I agree, however, I'm now up against a constraint where the start/end time data that i'll be receiving is in the varchar format which I can't do anything about.
I don't know much as far as efficiencies to be gained using specific data types, but given this specific constraint, is there something more to be done to make things work better or would the LEFT(startime,2)/RIGHT(startime,2) end up working ok? ie should there be an initial conversion of the data from varchar to time in another declare statement maybe?
July 19, 2012 at 10:23 am
Here is the problem, I don't know. You didn't provide any DDL or sample data for this problem. The sample data I used was provided by capn.hector.
If you are receiving this data in a file, I'd convert it to the appropriate data type when importing it.
July 19, 2012 at 10:32 am
I did provide sample data yesterday, though not much. Capn's data was essentially the same anyway. And the sample data actually was in the smalldatetime format, so this line of questioning was a bit of a tangent from that. I'll try to get the data converted and see how it goes.
Thanks for the feedback though. It certainly helps to hear from the experts out there and is very much appreciated.
😀
July 19, 2012 at 10:44 am
tacy.highland (7/19/2012)
I did provide sample data yesterday, though not much. Capn's data was essentially the same anyway. And the sample data actually was in the smalldatetime format, so this line of questioning was a bit of a tangent from that. I'll try to get the data converted and see how it goes.Thanks for the feedback though. It certainly helps to hear from the experts out there and is very much appreciated.
😀
Tacy,
Going to disagree a bit regarding the providing of sample data. I went back and could find nothing that was actually usable. The following would have been helpful as it would allow anyone to simply cut/paste and run the code to establish a working test environment for the problem.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RouteTimes]') AND type in (N'U'))
DROP TABLE [dbo].[RouteTimes]
GO
CREATE TABLE dbo.RouteTimes (
RouteNumber INT,
StartDatetime DATETIME,
EndDatetime DATETIME
);
INSERT INTO dbo.RouteTimes (
RouteNumber,
StartDatetime,
EndDatetime
)
VALUES (1234,'2012-07-18 09:00:00','2012-07-18 13:00:00'),
(5678,'2012-07-18 10:13:00','2012-07-18 14:28:00'),
(1321,'2011-07-17 23:30:00','2012-07-18 06:30:00'),
(5342,'2012-07-18 21:14:00','2012-07-19 03:36:00'),
(6565,'2012-07-17 12:12:00','2012-07-17 20:42:00'),
(9897,'2012-07-19 06:12:00','2012-07-19 14:34:00');
SELECT * FROM dbo.RouteTimes; -- Verify the data
GO
July 19, 2012 at 10:58 am
I'm confused.
I followed the instructions listed in the Best Practices link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
And posted the code yesterday at 834pm. Aside from the Set Identity statements (which do err out) it worked for me to copy,paste and run to create a table with data. This didn't work for you?
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
I used the Select/UnionAll as specified in the article to get the actual data from the existing table I had here.
July 19, 2012 at 11:06 am
tacy.highland (7/19/2012)
I'm confused.I followed the instructions listed in the Best Practices link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
And posted the code yesterday at 834pm. Aside from the Set Identity statements (which do err out) it worked for me to copy,paste and run to create a table with data. This didn't work for you?
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
I used the Select/UnionAll as specified in the article to get the actual data from the existing table I had here.
Are you talking about this?
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
If so, try running it in an empty sandbox database. When I run it in my sandbox database I get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Schedules_RC'.
July 19, 2012 at 11:09 am
Or is this what you are talking about? Not sure how I missed it.
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
If this is the data, it changes how I would need to look at doing my code. Also, not sure how to use the data in the onclock column. Need to look at it a bit more.
Viewing 15 posts - 16 through 30 (of 81 total)
You must be logged in to reply to this topic. Login to reply