July 19, 2012 at 12:34 pm
o crap, yes. I forgot they modified the routes so now they're varchars(15). I am so sorry for the sloppy work.
The table would then be:
CREATE TABLE #Schedules_RC(
Route VARCHAR(15),
Start_Time VARCHAR(10),
End_Time VARCHAR(10)
)
July 19, 2012 at 12:35 pm
Or do we wrap the data around on the display?
July 19, 2012 at 12:36 pm
Beyond 2345, yes. I was going to modify the pivot to list the 2400 - 2859 times instead of the 0100, 0200, etc.
July 19, 2012 at 1:00 pm
I got an idea on the breaks.
I think we'll add a "type" column to the Schedules_RC table indicating what type of schedule it is, ie ScheduledTime, Break, Lunch, OutOfService etc. Then I can run the original query for where type = ScheduledTime to get a total of the inservice time, run the query again where type = Break to get a total of the break time and subtract that number from the inservice time.
I think that's a pretty easy way to handle this, don't you?
July 19, 2012 at 1:02 pm
Converted your temp table into a permanent table (created it in a separate query window from my code).
Here is my updated code based on your current sample data (i.e. we aren't including break times yet).
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] 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
)
select
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],
[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
order by
RouteNumber;
July 19, 2012 at 1:06 pm
Hold on, I have a problem.....
July 19, 2012 at 1:09 pm
Looking at the sample data, looks like we have multiple entries for the same route. I need to modify my code a bit more to handle this additional possibility.
July 19, 2012 at 1:15 pm
Okay, new update to my code:
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 converts the data to be used within the query */
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
)
select
left(RouteNumber,charindex('.',RouteNumber) - 1) 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],
[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
order by
RouteNumber;
July 19, 2012 at 1:23 pm
Wow, Lynn, you are a machine! That is awesome!
I wish there was something I could do for you to thank you for all your time on this.
This is just so great.
July 19, 2012 at 1:33 pm
The hard part was the initial code. Making the mods is just knowing what it is doing and having a better understanding of the data upon which it must work.
Now, we need to look at the additional requirements. To do that we need to know how you are receiving this data and how to incorporate it into the code.
Test time!
Do you understand how the CTE BaseData modifies the source data for the query?
July 19, 2012 at 2:01 pm
I think so. It's taking the varchars and converting them to smalldatetime. Although, I'm not clear on why its converting the route# to .1?
July 19, 2012 at 2:05 pm
tacy.highland (7/19/2012)
I think so. It's taking the varchars and converting them to smalldatetime. Although, I'm not clear on why its converting the route# to .1?
the route to route# + row number is to uniqueify the routes since you had some duplicates.
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 2:10 pm
capn.hector (7/19/2012)
tacy.highland (7/19/2012)
I think so. It's taking the varchars and converting them to smalldatetime. Although, I'm not clear on why its converting the route# to .1?the route to route# + row number is to uniqueify the routes since you had some duplicates.
Yes, I had to add a value to the route number to make each route entry unique. That is what I discovered when I first reported a problem a few posts back.
July 19, 2012 at 2:10 pm
I wish there was something I could do for you to thank you for all your time on this.
This is just so great.
I think you just did. 😀
Most of us that post here do so because we enjoy helping others. Having those people show such gratitude is incredibly rewarding!!!
_______________________________________________________________
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 19, 2012 at 2:15 pm
Sean Lange (7/19/2012)
I wish there was something I could do for you to thank you for all your time on this.
This is just so great.
I think you just did. 😀
Most of us that post here do so because we enjoy helping others. Having those people show such gratitude is incredibly rewarding!!!
Well, a gift card to Amazon.com would be nice. 😉
Actually, Sean is right. Your expression of gratitude is enough, especially after getting a response like this one on another thread last night:
I think you are unable to provide the dynamic script for the scenario i have given.
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply