February 15, 2011 at 7:22 pm
Hi
I am trying to write a query to report vehicle utilisation of fleet cars.
I have records for each booking with a start and end date. The booking could start and finish on the same day, or could span many days.
I want to expand each record that I have one entry for each day of the booking. For example:
11/01/2010 15:30 12/01/2010 16:00
would become
11/01/2010 15:30 12/01/2010 23:59
12/01/2010 00:01 12/01/2010 16:00
Can this be done within a query using a for each loop? or is this a VBA question? I could manage this in perl or PL/SQL but not sure how to do this in TSQL
Any help would be greatly appreciated.
Thanks in advance.
Pete
February 15, 2011 at 8:24 pm
The three standard ways to handle this with T-SQL would be the following:
- While Loop (slowest but most common)
- Calendar Table (requires upkeep)
- Tally Table ( requires understanding of using them, see my signature for a link).
Basically, one way or another you've got to force a row repitition.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 9:01 pm
Craig Farrell (2/15/2011)
The three standard ways to handle this with T-SQL would be the following:- While Loop (slowest but most common)
- Calendar Table (requires upkeep)
- Tally Table ( requires understanding of using them, see my signature for a link).
Basically, one way or another you've got to force a row repitition.
Thanks for your reply.
I'm trying to work my way through a script which will loop through the data and insert each itteration into another table. (It's been a long time since i've done anything like this so im a bit rusty.)
I haven't heard of tally tables, so I'll have a look. Thanks for the suggestion.
February 15, 2011 at 11:44 pm
thepotplants (2/15/2011)
I haven't heard of tally tables, so I'll have a look. Thanks for the suggestion.
No problem. You'll find the Tally and/or Calendar table much quicker than any while loop, exponentially so in most circumstances.
If you always want every day between point a and b, timewise, then I'd go with the Tally table. If you're going to have to deal with any other rules (Sundays aren't counted, skipping Holidays, Free ride on birthdays, whatever...) then you'll probably want to look into constructing your own Calendar table. They're relatively lightweight and there's a lot of code on the net that you can borrow to build it out.
If you can build out some DDL and sample data like you'll find in the first link of my sig, we can even provide code for you to help you get to your final product.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2011 at 2:40 pm
Craig Farrell (2/15/2011)
thepotplants (2/15/2011)
I haven't heard of tally tables, so I'll have a look. Thanks for the suggestion.No problem. You'll find the Tally and/or Calendar table much quicker than any while loop, exponentially so in most circumstances.
If you always want every day between point a and b, timewise, then I'd go with the Tally table. If you're going to have to deal with any other rules (Sundays aren't counted, skipping Holidays, Free ride on birthdays, whatever...) then you'll probably want to look into constructing your own Calendar table. They're relatively lightweight and there's a lot of code on the net that you can borrow to build it out.
If you can build out some DDL and sample data like you'll find in the first link of my sig, we can even provide code for you to help you get to your final product.
Wow.. after reading about tally tables I'm sure I could smell something burning... my synapses sizzling I think... :blink:
I can see the potential of Tally tables, but i'm still struggling to shake the "for each.. loop" mind set.
DDL below as suggested.
USE tempdb
GO
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#booking_in','U') IS NOT NULL
DROP TABLE #booking_in
IF OBJECT_ID('TempDB..#booking_out','U') IS NOT NULL
DROP TABLE #booking_out
--===== Create the test table with
CREATE TABLE #booking_in(
BookingID int NOT NULL,
VehicleID int NULL,
BookingStart datetime NULL,
BookingEnd datetime NULL
)
CREATE TABLE #booking_out(
BookingID int NOT NULL,
VehicleID int NULL,
BookingStart datetime NULL,
BookingEnd datetime NULL
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== Insert the test data into the test table
INSERT INTO #booking_in
(BookingID ,VehicleID ,BookingStart ,BookingEnd)
SELECT '19159','6','Dec 29 2009 8:00AM','Jan 1 2010 4:00AM' UNION ALL
SELECT '19335','13','Jan 11 2010 3:00PM','Jan 20 2010 5:00PM' UNION ALL
SELECT '19336','11','Jan 11 2010 3:30PM','Jan 12 2010 4:00PM' UNION ALL
SELECT '19359','11','Feb 10 2010 2:00PM','Feb 11 2010 8:30AM' UNION ALL
SELECT '19377','6','Feb 23 2010 8:00AM','Feb 24 2010 5:30PM' UNION ALL
SELECT '19379','6','Feb 23 2010 3:30PM','Feb 24 2010 3:45PM' UNION ALL
SELECT '19415','11','Feb 1 2010 4:00PM','Feb 3 2010 8:30AM' UNION ALL
SELECT '19416','11','Feb 9 2010 4:00PM','Feb 11 2010 8:30AM' UNION ALL
SELECT '19434','6','Jan 26 2010 8:00AM','Jan 27 2010 10:00AM' UNION ALL
SELECT '19435','9','Jan 26 2010 7:00AM','Jan 27 2010 10:30AM'
The intention is expand records from the booking_in table into the booking_out table.
Booking 19159 would become 4 rows.
'19159','6' 'Dec 29 2009 08:00','Dec 29 2009 23:59:59'
'19159','6' 'Dec 30 2009 00:00','Dec 30 2009 23:59:59'
'19159','6' 'Dec 31 2009 00:00','Dec 31 2009 23:59:59'
'19159','6' 'Jan 1 2010 00:00','Jan 1 2010 4:00AM'
I can see that your example for extrapolating shifts is very close what I want, but I'm still struggle to change mindset.
I understand that each record would be converted via the tally table, but would that be possible for the entire input table in a single query? (I feel stupid asking this, as Im sure the answer is yes, but im struggling to make the mental leap.)
I would have created a cursor and worked through the table, and for each record execute another loop to build up each record. (So a loop within a loop?)
Many thanks for your assistance and patience.
February 16, 2011 at 10:32 pm
thepotplants (2/16/2011)
The intention is expand records from the booking_in table into the booking_out table.Booking 19159 would become 4 rows.
'19159','6' 'Dec 29 2009 08:00','Dec 29 2009 23:59:59'
'19159','6' 'Dec 30 2009 00:00','Dec 30 2009 23:59:59'
'19159','6' 'Dec 31 2009 00:00','Dec 31 2009 23:59:59'
'19159','6' 'Jan 1 2010 00:00','Jan 1 2010 4:00AM'
Perfect.
I can see that your example for extrapolating shifts is very close what I want, but I'm still struggle to change mindset.
Don't worry, it can be tough, and Tally's usually need a personal example to really make sense. The trick to realizing how to use a tally table is if you're looking to do a counter loop (say, WHILE c BETWEEN 1 and x) AND you're going to do an insert to a temp table while you're at it, thus creating multiple rows, the cross join does the work for you... and Tally.N is your counter.
I understand that each record would be converted via the tally table, but would that be possible for the entire input table in a single query? I would have created a cursor and worked through the table, and for each record execute another loop to build up each record. (So a loop within a loop?)
And without a Tally or calendar table, you'd be stuck with this. Luckily, we're not!
Check this out:
-- Note my tally table starts at 0
select
bi.bookingid,
bi.vehicleid,
CASE WHEN -- Test if we're on the first day
datediff( dd, dateadd( dd, n, bi.bookingstart), bi.bookingstart) = 0
THEN -- First day
bi.bookingstart
ELSE -- find midnight starting time of current Tally Day
dateadd( dd, datediff( dd, 0, dateadd( dd, n, bi.bookingstart)), 0)
END as bookingstart,
CASE WHEN -- Test if we're on the last day
datediff( dd, dateadd( dd, n, bi.bookingstart), bi.bookingend) = 0
THEN -- Last day
bi.bookingend
ELSE -- Get 23:59:59 997 of current Tally Day (rounding issues prevents .999)
DATEADD( ms, -2, DATEADD( dd, DATEDIFF( dd, 0, DATEADD( dd, n, bi.bookingstart)) + 1, 0))
END as bookingend
from
#booking_in AS bi,
tempdb..Tally AS t
WHERE
--bookingID = 19159 AND
t.N <= DATEDIFF( dd, bi.bookingstart, bi.bookingend)
As usual, when I start doing custom code, my disclaimer: Make sure you understand this before a production deployment because I don't *usually* check these boards at 2 AM when all code seems to self-destruct. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 1:13 pm
Wow, that is fantastic.
Thank you very much Craig.
Craig Farrell (2/16/2011)
As usual, when I start doing custom code, my disclaimer: Make sure you understand this before a production deployment because I don't *usually* check these boards at 2 AM when all code seems to self-destruct. 🙂
Understood... This is for a one off (until next time 😉 ) analysis exercise, in a safe environment. Performance was never a concern, but the logical approach and learning a new technique has been invaluable.
Many thanks once again.
Pete
February 17, 2011 at 1:27 pm
thepotplants (2/17/2011)
Wow, that is fantastic.Thank you very much Craig.
My pleasure.
Understood... This is for a one off (until next time 😉 ) analysis exercise, in a safe environment. Performance was never a concern, but the logical approach and learning a new technique has been invaluable.
The perfect time to start tinkering with a new way of doing things. Have fun!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 8:50 pm
It's not my job to judge but I have to say it anyway, Craig... well done not only on the usage of the Tally Table, but also on conveying the thought to someone who hasn't seen it before. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2011 at 2:21 am
Jeff Moden (2/17/2011)
It's not my job to judge but I have to say it anyway, Craig... well done not only on the usage of the Tally Table, but also on conveying the thought to someone who hasn't seen it before. 🙂
High Praise... Thank you. :blush:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply