July 20, 2012 at 12:24 am
Okay, once I finally decided to look at the code again after getting home, this is what I came up with base on your post about totals. Nothing big, it only took a few minutes tought to come up with the solution. I'll leave it as an exercise for you to figure out what I did.
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(116)
CAST(DATEADD(mi,15 * n, CAST(cast(getdate() as date) AS DATETIME)) AS TIME(0)),
DATEADD(mi,15 * n, CAST(cast(getdate() as date) AS DATETIME)),
DATEADD(mi,15 * (n + 1), CAST(cast(getdate() as date) 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 */
BaseData(RouteNumber, StartTime, EndTime) AS
(SELECT
[Route] + '.' + cast(row_number() over (partition by [Route] order by Start_Time) as varchar(10)) as RouteNumber,
dateadd(mi,((cast(left(Start_Time,2) as int) * 60) + cast(right(Start_Time,2) as int)),cast(cast(getdate() as date) as datetime)) as StartTime,
dateadd(mi,((cast(left(End_Time,2) as int) * 60) + cast(right(End_Time,2) as int)),cast(cast(getdate() as date) as datetime)) as EndTime
FROM
dbo.Schedules_RC
),
/*********************************************************************************************************************/
/* DailyBusRoutes provides the data that will be pivoted. Replace the CTE TestData with the source table */
DailyBusRoutes as (
SELECT
case when cast(tb.DailyBeginTime as date) = cast(getdate() as date)
then left(cast(tb.DailyTime as varchar(10)),5)
else stuff(left(cast(tb.DailyTime as varchar(10)),5),1,2,cast(cast(left(tb.DailyTime,2) as int) + 24 as char(2)))
end 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 BaseData td2
OUTER apply (SELECT
*
FROM BaseData td1
WHERE
(td1.RouteNumber = td2.RouteNumber) AND
(tb.DailyEndTime > td1.StartTime AND tb.DailyBeginTime < td1.EndTime )) td
),
RouteMatrix as (
select
left(pvt.RouteNumber,charindex('.',pvt.RouteNumber) - 1) as [Route], datediff(mi,dt.StartTime, dt.EndTime) as TotalTime,
[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],
[24:00],[24:15],[24:30],[24:45],
[25:00],[25:15],[25:30],[25:45],
[26:00],[26:15],[26:30],[26:45],
[27:00],[27:15],[27:30],[27:45],
[28:00],[28:15],[28:30],[28: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],
[24:00],[24:15],[24:30],[24:45],
[25:00],[25:15],[25:30],[25:45],
[26:00],[26:15],[26:30],[26:45],
[27:00],[27:15],[27:30],[27:45],
[28:00],[28:15],[28:30],[28:45])) as pvt
cross apply (select bd.RouteNumber, bd.StartTime, bd.EndTime from BaseData bd where bd.RouteNumber = pvt.RouteNumber) dt
)
select * from RouteMatrix rm
union all
select
'Totals',
SUM(TotalTime),
SUM([00:00]),SUM([00:15]),SUM([00:30]),SUM([00:45]),
SUM([01:00]),SUM([01:15]),SUM([01:30]),SUM([01:45]),
SUM([02:00]),SUM([02:15]),SUM([02:30]),SUM([02:45]),
SUM([03:00]),SUM([03:15]),SUM([03:30]),SUM([03:45]),
SUM([04:00]),SUM([04:15]),SUM([04:30]),SUM([04:45]),
SUM([05:00]),SUM([05:15]),SUM([05:30]),SUM([05:45]),
SUM([06:00]),SUM([06:15]),SUM([06:30]),SUM([06:45]),
SUM([07:00]),SUM([07:15]),SUM([07:30]),SUM([07:45]),
SUM([08:00]),SUM([08:15]),SUM([08:30]),SUM([08:45]),
SUM([09:00]),SUM([09:15]),SUM([09:30]),SUM([09:45]),
SUM([10:00]),SUM([10:15]),SUM([10:30]),SUM([10:45]),
SUM([11:00]),SUM([11:15]),SUM([11:30]),SUM([11:45]),
SUM([12:00]),SUM([12:15]),SUM([12:30]),SUM([12:45]),
SUM([13:00]),SUM([13:15]),SUM([13:30]),SUM([13:45]),
SUM([14:00]),SUM([14:15]),SUM([14:30]),SUM([14:45]),
SUM([15:00]),SUM([15:15]),SUM([15:30]),SUM([15:45]),
SUM([16:00]),SUM([16:15]),SUM([16:30]),SUM([16:45]),
SUM([17:00]),SUM([17:15]),SUM([17:30]),SUM([17:45]),
SUM([18:00]),SUM([18:15]),SUM([18:30]),SUM([18:45]),
SUM([19:00]),SUM([19:15]),SUM([19:30]),SUM([19:45]),
SUM([20:00]),SUM([20:15]),SUM([20:30]),SUM([20:45]),
SUM([21:00]),SUM([21:15]),SUM([21:30]),SUM([21:45]),
SUM([22:00]),SUM([22:15]),SUM([22:30]),SUM([22:45]),
SUM([23:00]),SUM([23:15]),SUM([23:30]),SUM([23:45]),
SUM([24:00]),SUM([24:15]),SUM([24:30]),SUM([24:45]),
SUM([25:00]),SUM([25:15]),SUM([25:30]),SUM([25:45]),
SUM([26:00]),SUM([26:15]),SUM([26:30]),SUM([26:45]),
SUM([27:00]),SUM([27:15]),SUM([27:30]),SUM([27:45]),
SUM([28:00]),SUM([28:15]),SUM([28:30]),SUM([28:45])
from
RouteMatrix rm1
order by
rm.[Route];
July 20, 2012 at 9:05 am
You made the pivot into another cte and then did a select * to get the details and added the aggregates to the original dataset with a union. Last night I did something similar: I dumped the pivot into a temp table, doing a select * to get the detail and doing another select to get the aggregates, for 2 separate datasets. Thanks for the update!
Here's another twist on things. Now that we have the actual minutes in service per increment, I need to determine how those minutes translate to an physical route. What I mean is that when a time increment for a route shows 15, that equals 1 (route) for that timeframe. When there's a value less than 15, it would be a fraction of 1 (or a fraction of a route), example: if the pullout is 0905, the driver is inservice 10 minutes, 10/15 = 0.66. So there's not a full route in service for that 15 min increment. This would be a whole separate dataset/query as this specific query would be used to represent actual minutes, the new data would be used to represent actual routes.
So much of the logic is already determined here. Like you mentioned before how you just build on each step, would this be a matter of building another "BusRoutes" cte based on the new minute values in each increment now and doing some division to come up with the 0,1, and decimals? That's my initial thought.
What do you think?
July 20, 2012 at 9:10 am
Separate query and change the computation to include dividing the time by 15 to give you the portion of the route (or whatever you called it).
July 20, 2012 at 12:32 pm
I think I got it! (at least I'm cautiously optimistic)
Does this look about right to you?
CREATE TABLE #Schedules_RC(
[Route] VARCHAR(15),
Start_Time VARCHAR(10),
End_Time VARCHAR(10)
)
INSERT INTO #Schedules_RC VALUES ('600','0410','2330')
INSERT INTO #Schedules_RC VALUES ('801R','0815','1825')
INSERT INTO #Schedules_RC VALUES ('803R','0745','1755')
INSERT INTO #Schedules_RC VALUES ('804','0905','1915')
;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(116)
CAST(DATEADD(mi,15 * n, CAST(cast(getdate() as date) AS DATETIME)) AS TIME(0)),
DATEADD(mi,15 * n, CAST(cast(getdate() as date) AS DATETIME)),
DATEADD(mi,15 * (n + 1), CAST(cast(getdate() as date) 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 */
BaseData(RouteNumber, StartTime, EndTime) AS
(SELECT
[Route] + '.' + cast(row_number() over (partition by [Route] order by Start_Time) as varchar(10)) as RouteNumber,
dateadd(mi,((cast(left(Start_Time,2) as int) * 60) + cast(right(Start_Time,2) as int)),cast(cast(getdate() as date) as datetime)) as StartTime,
dateadd(mi,((cast(left(End_Time,2) as int) * 60) + cast(right(End_Time,2) as int)),cast(cast(getdate() as date) as datetime)) as EndTime
FROM
#Schedules_RC--dbo.Schedules_RC
),
/*********************************************************************************************************************/
/* DailyBusRoutes provides the data that will be pivoted. Replace the CTE TestData with the source table */
DailyBusRoutes as (
SELECT
case when cast(tb.DailyBeginTime as date) = cast(getdate() as date)
then left(cast(tb.DailyTime as varchar(10)),5)
else stuff(left(cast(tb.DailyTime as varchar(10)),5),1,2,cast(cast(left(tb.DailyTime,2) as int) + 24 as char(2)))
end 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 BaseData td2
OUTER apply (SELECT
*
FROM BaseData td1
WHERE
(td1.RouteNumber = td2.RouteNumber) AND
(tb.DailyEndTime > td1.StartTime AND tb.DailyBeginTime < td1.EndTime )) td
),
DailyRouteCount as (
SELECT
dbr.DailyTime,
dbr.RouteNumber,
(CAST(TimeScheduled AS DECIMAL(5,2))/15) RoutesScheduled
FROM
DailyBusRoutes dbr
),
RouteMatrix as (
select
left(pvt.RouteNumber,charindex('.',pvt.RouteNumber) - 1) as [Route]
, datediff(mi,dt.StartTime, dt.EndTime) as TotalTime,
[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],
[24:00],[24:15],[24:30],[24:45],
[25:00],[25:15],[25:30],[25:45],
[26:00],[26:15],[26:30],[26:45],
[27:00],[27:15],[27:30],[27:45],
[28:00],[28:15],[28:30],[28:45]
from (
select RouteNumber,DailyTime,RoutesScheduled
from DailyRoutecOUNT) p
pivot (SUM(RoutesScheduled) 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],
[24:00],[24:15],[24:30],[24:45],
[25:00],[25:15],[25:30],[25:45],
[26:00],[26:15],[26:30],[26:45],
[27:00],[27:15],[27:30],[27:45],
[28:00],[28:15],[28:30],[28:45])) as pvt
cross apply (select bd.RouteNumber, bd.StartTime, bd.EndTime from BaseData bd where bd.RouteNumber = pvt.RouteNumber) dt
)
select * from RouteMatrix rm
union all
select
'Totals',
SUM(TotalTime),
SUM([00:00]),SUM([00:15]),SUM([00:30]),SUM([00:45]),
SUM([01:00]),SUM([01:15]),SUM([01:30]),SUM([01:45]),
SUM([02:00]),SUM([02:15]),SUM([02:30]),SUM([02:45]),
SUM([03:00]),SUM([03:15]),SUM([03:30]),SUM([03:45]),
SUM([04:00]),SUM([04:15]),SUM([04:30]),SUM([04:45]),
SUM([05:00]),SUM([05:15]),SUM([05:30]),SUM([05:45]),
SUM([06:00]),SUM([06:15]),SUM([06:30]),SUM([06:45]),
SUM([07:00]),SUM([07:15]),SUM([07:30]),SUM([07:45]),
SUM([08:00]),SUM([08:15]),SUM([08:30]),SUM([08:45]),
SUM([09:00]),SUM([09:15]),SUM([09:30]),SUM([09:45]),
SUM([10:00]),SUM([10:15]),SUM([10:30]),SUM([10:45]),
SUM([11:00]),SUM([11:15]),SUM([11:30]),SUM([11:45]),
SUM([12:00]),SUM([12:15]),SUM([12:30]),SUM([12:45]),
SUM([13:00]),SUM([13:15]),SUM([13:30]),SUM([13:45]),
SUM([14:00]),SUM([14:15]),SUM([14:30]),SUM([14:45]),
SUM([15:00]),SUM([15:15]),SUM([15:30]),SUM([15:45]),
SUM([16:00]),SUM([16:15]),SUM([16:30]),SUM([16:45]),
SUM([17:00]),SUM([17:15]),SUM([17:30]),SUM([17:45]),
SUM([18:00]),SUM([18:15]),SUM([18:30]),SUM([18:45]),
SUM([19:00]),SUM([19:15]),SUM([19:30]),SUM([19:45]),
SUM([20:00]),SUM([20:15]),SUM([20:30]),SUM([20:45]),
SUM([21:00]),SUM([21:15]),SUM([21:30]),SUM([21:45]),
SUM([22:00]),SUM([22:15]),SUM([22:30]),SUM([22:45]),
SUM([23:00]),SUM([23:15]),SUM([23:30]),SUM([23:45]),
SUM([24:00]),SUM([24:15]),SUM([24:30]),SUM([24:45]),
SUM([25:00]),SUM([25:15]),SUM([25:30]),SUM([25:45]),
SUM([26:00]),SUM([26:15]),SUM([26:30]),SUM([26:45]),
SUM([27:00]),SUM([27:15]),SUM([27:30]),SUM([27:45]),
SUM([28:00]),SUM([28:15]),SUM([28:30]),SUM([28:45])
from RouteMatrix rm1
order by rm.[Route];
drop table #Schedules_RC
July 20, 2012 at 12:35 pm
yep you got that part right. it took me a little while to fully grasp CTE's but looks like you got it.
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 20, 2012 at 12:56 pm
Looks good, but I am going to quibble a bit here. The semicolon (;) is a statement terminator, not a statement begininator. The following snippet from your post:
CREATE TABLE #Schedules_RC(
[Route] VARCHAR(15),
Start_Time VARCHAR(10),
End_Time VARCHAR(10)
)
INSERT INTO #Schedules_RC VALUES ('600','0410','2330')
INSERT INTO #Schedules_RC VALUES ('801R','0815','1825')
INSERT INTO #Schedules_RC VALUES ('803R','0745','1755')
INSERT INTO #Schedules_RC VALUES ('804','0905','1915')
;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)),
...
should look like this, IMHO:
CREATE TABLE #Schedules_RC(
[Route] VARCHAR(15),
Start_Time VARCHAR(10),
End_Time VARCHAR(10)
);
INSERT INTO #Schedules_RC VALUES ('600','0410','2330');
INSERT INTO #Schedules_RC VALUES ('801R','0815','1825');
INSERT INTO #Schedules_RC VALUES ('803R','0745','1755');
INSERT INTO #Schedules_RC VALUES ('804','0905','1915');
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)),
...
CTE's require that the previous statement be terminated with a semicolon (;), not that the CTE begin with the semicolon(;). This is something that people started doing simply to ensure that the previous statement was terminated by a semicolon (;).
In addition, you will notice that there is also a statement in T-SQL that actually requires that it be terminated by a semicolon (;), the MERGE statement.
From MSDN:
The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.
With this, I have found it best to simple begin using the semicolon (;) to terminate all statements. Also, please note, that I do fail at this at times, but it is something I work to achieve.
July 20, 2012 at 2:56 pm
Hey thanks for all the replies. Everyone's been very helpful.
On the CTEs, I apparently hadn't done enough homework. The semi at the start is how I've always seen them. Thanks for the tip, Lynn.
I hope someday to be in a position to help others the way I've been helped here. Very well done.
Thanks everyone!
Viewing 7 posts - 76 through 81 (of 81 total)
You must be logged in to reply to this topic. Login to reply