SINGLE or MULTIPLE INSERTS based on SELECTED values
We pull reservations for our tennis courts from our SQL database into a simple results table to help us get a picture of the court's usage. This is pretty straight forward except when it comes to orders that last over an hour.
Currently, each booking result is displayed in the results table. Each line contains the start time, duration and number of the trial. We would like to map this table directly to a table or pivot table so that we can see how many hours our ships are booked and what hours of the day.
Currently, our SQL query looks something like this:
INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
DATEPART (mm, b.StartDateTime),
DATEPART (dd, b.StartDateTime),
DATEPART (hh, b.StartDateTime),
a.Duration,
a.Court
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
Our problem is that orders for 2, 3 or more hours only have one row in the results table, i.e. within the first hour of booking. This is because the duration of the booking is recorded in the duration data. We could do some post-processing on the data to achieve our goals, but it would be easier if it were possible in our SQL query.
Can this query be modified in some way so that depending on the duration (which can be 1, 2, 3, ... hours) the corresponding number of rows is inserted into the results table, each of which is 1. Thus 3 - Hourly bookings starting at 9am will result in three rows in the table of results, one at 9am, one at 10am and one at 11am, each lasting 1 hour.
So, instead of the following line in the results table:
Year, Month, Day, Hour, Duration, Court 2009, 08, 25, 09, 3, 1
we get the following lines:
Year, Month, Day, Hour, Duration, Court 2009, 08, 25, 09, 1, 1 2009, 08, 25, 10, 1, 1 2009, 08, 25, 11, 1, 1
This would make it easier to map the results table to a spreadsheet.
UPDATE 2009-08-25: Of course, as the first couple of answers show, it doesn't have to be one request. The set is lovely.
UPDATE 2009-08-26: have been tracked and have not yet had a chance to try out the proposed solutions. Hopefully so soon and choose an answer based on the results.
UPDATE 2009-08-27: It is finally possible to try out solutions. The integer table and connection to create a solution was one of the first. Especially using cross-joins to create such a table. This is probably the cleaner, SQL way of doing things.
However, in the end I went with Aaron's solution with a flag and a simple algorithm. I improved it by wrapping its algorithm in a while loop to keep repeating until there were no durations> 1. It was quick and easy to implement. He also stressed that we had about 10 hours of orders, so I didn't have to hard-code the limit here.
I should note that I included Jeff's idea of maximum duration in the counter of the while loop, not my original idea of counting items with duration> 1. A little less code.
source to share
It's not trivial. First you need another column "Flag" which is 0:
INSERT INTO Results (year, month, day, hour, duration, court, Flag)
SELECT DATEPART (yy, b.StartDateTime),
DATEPART (mm, b.StartDateTime),
DATEPART (dd, b.StartDateTime),
DATEPART (hh, b.StartDateTime),
a.Duration,
a.Court,
0
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
You need to run these queries multiple times:
-- Copy all rows with duration > 1 and set the flag to 1
insert into results(year, month, day, hour, duration, court, Flag)
select year, month, day, hour+1, duration-1, court, 1
from result
where duration > 1
;
-- Set the duration of all copied rows to 1
update result
set duration = 1
where flag = 0 and duration > 1
;
-- Prepare the copies for the next round
update result
set flag = 0
where flag = 1
This will create an additional entry for each duration > 1
. I am guessing that you cannot schedule a trial for more than 8 hours, so you just have to run those three 8 times to fix them all.
source to share
Edited to fix the missing hour
Create a single column temporary table with n rows for integer n - (I assumed the maximum booking time is 8 hours).
create table #t
(id int
,addHour int
)
insert #t
select 1,0
union all select 2,0
union all select 2,1
union all select 3,0
union all select 3,1
union all select 3,2
union all select 4,0
union all select 4,1
union all select 4,2
union all select 4,3
union all select 5,0
union all select 5,1
union all select 5,2
union all select 5,3
union all select 5,4
union all select 6,0
union all select 6,1
union all select 6,2
union all select 6,3
union all select 6,4
union all select 6,5
union all select 7,0
union all select 7,1
union all select 7,2
union all select 7,3
union all select 7,4
union all select 7,5
union all select 7,6
union all select 8,0
union all select 8,1
union all select 8,2
union all select 8,3
union all select 8,4
union all select 8,5
union all select 8,6
union all select 8,7
You can check that the temporary table has the correct row count with the following query:
select id, count(1)
from #t
group by id
order by id
Modify your query to include the connection to the temporary table:
INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
DATEPART (mm, b.StartDateTime),
DATEPART (dd, b.StartDateTime),
DATEPART (hh, b.StartDateTime) + addHour,
1 AS Duration,
a.Court
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
INNER JOIN #t AS t
ON t.id = a.Duration
EDIT - clarification on how this works
When joining tables, the output creates a row for each combination of joined rows in the source tables that match the join criteria.
I am using a temporary table to "multiply" the original result set from Orders and Activities by the number of hours the booking continues, joining Duration. This only works if orders are filled in whole hours.
If you want to see this more clearly, add a second column to #t that uniquely identifies each row and includes it in the output result set:
create table #t
(id int
,unique_id int identity
)
INSERT #t (id)
select 1
union all select 2
... etc
SELECT DATEPART (yy, b.StartDateTime),
DATEPART (mm, b.StartDateTime),
DATEPART (dd, b.StartDateTime),
DATEPART (hh, b.StartDateTime) + addHour,
1 AS Duration,
a.Court,
t.unique_id
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
INNER JOIN #t AS t
ON t.id = a.Duration
This should clarify that each row in the result set is created from the only valid combination of "Orders, Actions" and "#".
source to share
A slight modification to your original is sufficient if you put in an integers table (or VIEW
) to serve as a side by side generator:
INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
DATEPART (mm, b.StartDateTime),
DATEPART (dd, b.StartDateTime),
DATEPART (hh, b.StartDateTime) + (a.Duration - i.iii - 1)
1,
a.Court
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
INNER JOIN Integers999 i -- N.B.: Integers999 (iii INT), all numbers 0 .. 999
ON a.Duration > i.iii; -- So, a true Duration of 1 appears once, of 2 twice ...
source to share
I haven't had a chance to debug this, but something like this should do it for you:
DECLARE @maxDuration INTEGER
DECLARE @curDuration INTEGER
SELECT @MaxDuration = SELECT MAX(Duration) FROM Activities
SET @curDuration = 1
WHILE @curDuration <= @MaxDuration
BEGIN
INSERT INTO Results (year, month, day, hour, duration, court)
SELECT DATEPART (yy, b.StartDateTime),
DATEPART (mm, b.StartDateTime),
DATEPART (dd, b.StartDateTime),
DATEPART (hh, b.StartDateTime) + @curDuration - 1,
a.Duration,
a.Court
FROM Bookings b
INNER JOIN Activities a
ON b.ActivityID = a.ID
WHERE a.Duration <= @MaxDuration
SET @curDuration = @curDuration + 1
END
source to share