July 19, 2012 at 11:12 am
Then I found this (lost between a couple of other posts, and I just missed it):
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
This is more like what we worked with in the final code. The difference looks to be the string format of the datetime values.
Is this how the data is sent to you?
July 19, 2012 at 11:17 am
Here is my take away from all this. Read slower, you (meaning me) may catch things you may other wise miss.
In general, it is a good idea to start the question with as much information as possible. Provide all the information that someone else may need to understand what the problem is, what you have to work with, what you would like to see, and what you have done so far to solve the problem.
I will admit, I tend to spen more time reading the initial post, and start getting lazy after seeing several requests for DDL, sample data, and expected results. Normally, if we don't get that info in first couple of posts, the OP tends to not provide this at all.
I apologize.
July 19, 2012 at 11:37 am
No need to apologize! I was a bit lax in providing that sample code only because I thought it was so easy to describe (me being lazy). There were several pieces of code thrown around in this post, it was easy to miss things.
As for how I get the data, (of course, things get uncovered the further you move forward in something) while I had been told smalldatetime, in actuality the values will be the varchar for time. I did modify the capn's code to use this datatype and it seemed to work ok. I tried modifying your code to use my existing table (Schedules_RC) but it didn't quite work (showed zeros across the board) so I obviously missed something. I do like the fact that your code takes into account routes that do span two days, so I'll work on that to see if I can get it to work.
This forum has always been the best one for getting answers, (the right people are definitely here!) so I'm just glad I could get the help I needed.
Thanks Lynn!
July 19, 2012 at 11:40 am
If you post some sample data in the format you are actually using, I can look at how things may need to change in my code.
July 19, 2012 at 11:59 am
Ok, here's the sample data with the varchars:
CREATE TABLE #Schedules_RC
(
Route INT
, Start_Time VARCHAR(4)
, End_Time VARCHAR(4)
)
INSERT INTO #Schedules_RC
(
Route
, Start_Time
, End_Time
)
VALUES (601,'0932','1018')
,(602,'0947','1228')
,(603,'1032','1426')
,(604,'1425','1730')
,(605,'0939','1745')
,(606,'0930','0944')
SELECT *
FROM #Schedules_RC
If you really wanted to get crafty, there is a time "thing" with this data in that it uses a "29 hour clock" rather than 24. So there may be times included anywhere from 2500 (1am) - 2859 (459am) which obviously doesn't jive with the regular Time format. I was thinking we'd have to create a function that converts the times to the regular 24 hr clock prior to this processing so that it doesn't cause more strife. But that's where your "across multiple days" code comes in. Routes may indeed be in service past midnight but they'd have an endtime of something in the 29 hr clock range.
Does that make sense?
July 19, 2012 at 12:04 pm
tacy.highland (7/19/2012)
Ok, here's the sample data with the varchars:
CREATE TABLE #Schedules_RC(
Route INT
, Start_Time VARCHAR(4)
, End_Time VARCHAR(4)
)
INSERT INTO #Schedules_RC
(
Route
, Start_Time
, End_Time
)
VALUES (601,'0932','1018')
,(602,'0947','1228')
,(603,'1032','1426')
,(604,'1425','1730')
,(605,'0939','1745')
,(606,'0930','0944')
SELECT *
FROM #Schedules_RC
If you really wanted to get crafty, there is a time "thing" with this data in that it uses a "29 hour clock" rather than 24. So there may be times included anywhere from 2500 (1am) - 2859 (459am) which obviously doesn't jive with the regular Time format. I was thinking we'd have to create a function that converts the times to the regular 24 hr clock prior to this processing so that it doesn't cause more strife. But that's where your "across multiple days" code comes in. Routes may indeed be in service past midnight but they'd have an endtime of something in the 29 hr clock range.
Does that make sense?
One assumption I see having to make here is that times do not span days, unless this is a daily feed. I could use a little more detail on this, but I'll start with what I have.
July 19, 2012 at 12:07 pm
Times could span days but you'd know that if it has a starttime in regular time format and an endtime in 29 hour clock format (ie 2500 - 2859).
This indeed would only be looking at routes in service in one "day" (or 29 hour period).
July 19, 2012 at 12:13 pm
tacy.highland (7/19/2012)
Times could span days but you'd know that if it has a starttime in regular time format and an endtime in 29 hour clock format (ie 2500 - 2859).This indeed would only be looking at routes in service in one "day" (or 29 hour period).
Do you mean 2400 - 2859?
July 19, 2012 at 12:13 pm
Here's another trick.
So now that the hours are defined in 15 increments, if I wanted to specify "break" periods where the route is not in service during their start/end time, what would be the best way to even note that? Should it be additional columns in the Schedules_RC table (Route, Start_time, End_time, breakstart_time, breakend_time?) I could see it just removing whatever 15's are in the 15 increments for those break periods.
Would that be a matter of adding to the existing code at this point where it would go back and evaluate the breaktimes and remove them, or would it require a total overhaul?
July 19, 2012 at 12:15 pm
Sorry, yes. 2400 - 2859.
Not 2500 - 2859.
July 19, 2012 at 12:17 pm
You may want to extend your sample data, as this will result in modifications to the code. Also, how does this change how the data is presented?
Also, spanning days, does the span go forward only, or can you span from a previous day?
It may help if you provide a few more sample records that represents this possibility. Just post another insert statement with the additional sample data.
July 19, 2012 at 12:20 pm
tacy.highland (7/19/2012)
Here's another trick.So now that the hours are defined in 15 increments, if I wanted to specify "break" periods where the route is not in service during their start/end time, what would be the best way to even note that? Should it be additional columns in the Schedules_RC table (Route, Start_time, End_time, breakstart_time, breakend_time?) I could see it just removing whatever 15's are in the 15 increments for those break periods.
Would that be a matter of adding to the existing code at this point where it would go back and evaluate the breaktimes and remove them, or would it require a total overhaul?
im guessing you may get values like the following:
VALUES (601,'0932','2530')
VALUES (602,'2100','2800')
trying to figure out your data.
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 12:26 pm
That would be exactly right:
INSERT INTO #Schedules_RC
(
Route
, Start_Time
, End_Time
)
SELECT '600','0400','2330' UNION ALL
SELECT '801R','0815','1825' UNION ALL
SELECT '803R','0745','1755' UNION ALL
SELECT '804R','0900','1910' UNION ALL
SELECT '804R','1815','2535' UNION ALL
SELECT '999R','0730','1740' UNION ALL
SELECT '999R','0830','1840' UNION ALL
SELECT '353R','0900','1910' UNION ALL
SELECT '8732','1415','2425'
Notice there's a 4am startime in there. So the data would only show that 29 hr clock on the endtime of a route.
July 19, 2012 at 12:31 pm
tacy.highland (7/19/2012)
That would be exactly right:
INSERT INTO #Schedules_RC(
Route
, Start_Time
, End_Time
)
SELECT '600','0400','2330' UNION ALL
SELECT '801R','0815','1825' UNION ALL
SELECT '803R','0745','1755' UNION ALL
SELECT '804R','0900','1910' UNION ALL
SELECT '804R','1815','2535' UNION ALL
SELECT '999R','0730','1740' UNION ALL
SELECT '999R','0830','1840' UNION ALL
SELECT '353R','0900','1910' UNION ALL
SELECT '8732','1415','2425'
Notice there's a 4am startime in there. So the data would only show that 29 hr clock on the endtime of a route.
You have Route defined as an INT. Your data above won't work.
July 19, 2012 at 12:33 pm
Another assumption here, you want to display the pivot data out beyond 23:45, correct?
Viewing 15 posts - 31 through 45 (of 81 total)
You must be logged in to reply to this topic. Login to reply