July 19, 2012 at 2:18 pm
Lynn Pettis (7/19/2012)
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.
And at this point, you may want to start adding comments to the code to help you understand what is going on, especially when you have to come back to it a few months from now.
July 19, 2012 at 2:19 pm
Lynn Pettis (7/19/2012)
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.
My guess is they needed a question, not an answer (http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx Link included for those not familiar with the idiom). I can't even comprehend a situation where you would be unable to produce a dynamic script for a well defined 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 19, 2012 at 2:27 pm
Well I'm more than happy to gush in copious amounts about how everyone has been so incredibly helpful and knowledgeable. This has gone way above and beyond my expectations. If you were my go-to SQL person I'd be plying you with Amazon cards left and right.
Thank you for the clarification on the Route#. That makes sense now.
And since we're "testing"....I do have a few other SQL questions:
Capn Hector - on your code you had specified something that I really wasn't familiar with:
, 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
I've never seen the % sign used the way you did and tried creating some test data to reproduce what it was this code was doing. I googled everywhere to find something about this and couldn't find anything (beyond the usual wildcard LIKE 'ABC%'). I have only a slight grasp on how that worked but could i get more info on how that's being used there?
Lynn, to tell you the truth, so much of the code is way above my head, all the Cross Joins and Outer Apply, I'm trying to get a better understanding but that may take me quite a while! I'm trying to read up on Tally tables (http://www.sqlservercentral.com/articles/T-SQL/62867/) to better understand those as well. I'm adding comments where I get it so I'm hoping that will help.
Oh, and people who leave comments like that one obviously don't appreciate the gold mine of knowledge on these forums. There's always someone out there who has the answer, provided the question is presented properly...
(Thanks to all for their patience on my own issues in assembling all the necessary info!)
July 19, 2012 at 2:40 pm
tacy.highland (7/19/2012)
Well I'm more than happy to gush in copious amounts about how everyone has been so incredibly helpful and knowledgeable. This has gone way above and beyond my expectations. If you were my go-to SQL person I'd be plying you with Amazon cards left and right.Thank you for the clarification on the Route#. That makes sense now.
And since we're "testing"....I do have a few other SQL questions:
Capn Hector - on your code you had specified something that I really wasn't familiar with:
, CAST(CAST(StartHour AS VARCHAR) + ':' + CAST ( CASE WHEN StartMin % 15 = 0 THEN StartMinELSE (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
I've never seen the % sign used the way you did and tried creating some test data to reproduce what it was this code was doing. I googled everywhere to find something about this and couldn't find anything (beyond the usual wildcard LIKE 'ABC%'). I have only a slight grasp on how that worked but could i get more info on how that's being used there?
Lynn, to tell you the truth, so much of the code is way above my head, all the Cross Joins and Outer Apply, I'm trying to get a better understanding but that may take me quite a while! I'm trying to read up on Tally tables (http://www.sqlservercentral.com/articles/T-SQL/62867/) to better understand those as well. I'm adding comments where I get it so I'm hoping that will help.
Oh, and people who leave comments like that one obviously don't appreciate the gold mine of knowledge on these forums. There's always someone out there who has the answer, provided the question is presented properly...
(Thanks to all for their patience on my own issues in assembling all the necessary info!)
The % is also the modulus operator. This, StartMin % 15 , is basically asking for the remainder of a value divided by 15. If the value is evenly divisible by 15 the value returned is 0.
Cross Joins are basically cartesian products. If you take a table with 2 rows and CROSS JOIN it to another table with 2 rows (even itself) your result set is a table with 4 rows.
As for the OUTER APPLY, be sure to read about it and its companion CROSS APPLY. CROSS APPLY is (for the lack of a better term) like an inner join while the OUTER APPLY is like an outer join. In this case, I wanted null values where there was no value returned, an outer join type of result.
I believe that Paul White (SQLKiwi) has one or more articles about the APPLY operator as well.
July 19, 2012 at 2:44 pm
You should note that some of what I am saying above is very basic and there is much more to most of it. Just not sure if it can be fully described in a forum thread in a lot of detail.
Good reason to do the research you are and to come back and ask specific, targetted questions.
July 19, 2012 at 2:52 pm
Here is a link to Paul's article about APPLY.
Understanding and Using APPLY (Part 1)[/url]
Understanding and Using APPLY (Part 2)[/url]
This a great set of articles and totally opened my eyes on how it works.
_______________________________________________________________
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:55 pm
If you look carefully at my code you may notice something. Starting at the top, each piece that follows either builds upon that piece or combines two pieces together to complete a unit of work.
You could easily (hopefully) peel back each piece to see what each is doing. This should help you understand better what is going on. Espesially if you start returning all the columns of data returned in each step, as that is how I initially built the code, and worked to figure out what needed to be done to achieve the desired result.
July 19, 2012 at 3:07 pm
...very cool....
I have plenty to read up on now. 😀 I'll absorb and may come back with more....
Stay tuned.
July 19, 2012 at 3:21 pm
Is it too early to ask about the additional changes that need to be made?
Remember, sample data and expected results. As we build, keep the sample data from previous work in the sample set.
July 19, 2012 at 3:44 pm
Let's see.
For one thing, I've been trying to see how to modify the code to get a second dataset that totals (sums) each one of the time-increment columns and each row. I ran across some code that creates a Grand Total row and column using coalesce. This example sums data across a table that lists each month of the year:
/* GRAND TOTAL ROW */
DECLARE @GrandTotalRowNVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' + _
CAST(Month AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(Month AS VARCHAR)+']),0)')
FROMtbl_Matches ORDER BY Month
/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 'SELECT *, ('+ @GrandTotalCol + ') _
AS [Grand Total] INTO #temp_MatchesTotal
FROM
(SELECT Team,
Month
FROM tbl_Matches
) A
PIVOT
(
COUNT (*)
FOR ColName
IN ('+@columnHeaders +')
) B
ORDER BY Team
SELECT * FROM #temp_MatchesTotal UNION ALL
SELECT ''Grand Total'','''','+@GrandTotalRow +', _
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal'
Not sure it looks right though. Is this something worth pursuing, as far as modifying it to fit my pivot table? Don't know if I'm just diving in too far again.
July 19, 2012 at 3:54 pm
how would you want the sum, per route or per 15 min period. which one will determine which way to go about getting the sums
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 4:01 pm
I can see value in getting both the sum per route and sum for each 15 min period. So it would be adding a grand total row (summing total inservice time for each of the 15 min periods) and a grand total column (summing each route's total in service time).
Does that make sense?
(BTW, since we're on this subject, I saw the instructions for how to provide sample data, but how does one go about providing how the expected outcome should look like? Are there instructions out there? or do i just create a mock table with some data in it and script it like the sample data?)
July 19, 2012 at 4:02 pm
I can partially answer your question:
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
)
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
order by
pvt.RouteNumber;
July 19, 2012 at 4:06 pm
tacy.highland (7/19/2012)
I can see value in getting both the sum per route and sum for each 15 min period. So it would be adding a grand total row (summing total inservice time for each of the 15 min periods) and a grand total column (summing each route's total in service time).Does that make sense?
(BTW, since we're on this subject, I saw the instructions for how to provide sample data, but how does one go about providing how the expected outcome should look like? Are there instructions out there? or do i just create a mock table with some data in it and script it like the sample data?)
Yes, mock up a table that represents the expected result and then populate it using insert statements basing the output on the sample data you provide. This could mean some manual effort on your part to calculate results.
July 19, 2012 at 4:32 pm
A quick and easy way to get grand totals of the columns is to dump all of the pivot results into a temp table and just sum each column to get a grand total of each column. That may suffice.
....I'm starting to see more and more options with this code.
I can see potentially using it to display route idle/slack time in 15 min increments during the day as well. And I'll use it for determining break time for each route also, just running the same code but only pulling for a specific "Type" in the Schedules_RC table (which I'd have to add). And one thing I was mulling over was counting trips/trip time across 15 increments depending upon the trip distance. ie Trip = 10 miles = 45 mins, starts at 9am, travels to 945am. hmmmm. O the possibilities.
ahhhh. I'm loving this. Thanks guys.
Viewing 15 posts - 61 through 75 (of 81 total)
You must be logged in to reply to this topic. Login to reply