Applying code for a derived table

[jayjay5]

Hi

I am brand new user - only my second post! Using 2008R2 - I have a report using an events table which contains datefrom and dateend dates. The from and end can be over a number of days but I need to group and display the event every day it runs. For example if event runs June 1st thru' June 3rd I need to display it on June 1, June 2, June 3. I thought the only way to do it would be to create a temporary table so have been provided with the following code. Problem is I'm not a developer and I don't know what to do with it! Can anyone give me a step by step guide? Thanks.

CREATE PROCEDURE BOOKINGS @START_DATE datetime, @END_DATE datetime, @bookingid int

AS

BEGIN

-- create temp table to store each row
CREATE TABLE #bookings
(
ID INT,
MYDATE datetime
)

-- iterate through each day in the range and insert a row per day along with the booking id
WHILE(@FROM_DATE < @END_DATE)
BEGIN

insert into #bookings
values(@bookingid,@FROM_DATE)

set @FROM_DATE = DATEADD(day, 1, @FROM_DATE)

END

 

[xlbo]

Ok - so that builds you a temp table

Now you just need to do your main query and join to that temp table on date (left outer join FROM the temp table) so that if there are any missing dates in your main query teh date will still be returned along with NULL for the rest of the records

If you are using SQL 2008 you can also build thi9s as a table variable rather than a temp table:

		declare @StrYrSt varchar(12)
		declare @StrYrEnd varchar(12)
					
		set @StrYrSt = '2011-03-04'
		set @StrYrend = '2011-03-16'
		
				DECLARE @tbl_Dates TABLE 
				( 
					rowNum INT, 
					[Date] date 
				);

				WITH Dates AS 
				(
					SELECT [Date] = CONVERT(DATETIME,@StrYrSt)
						UNION ALL 
					SELECT [Date] = DATEADD(DAY, 1, [Date])
					FROM Dates
					WHERE [Date] < @StrYrEnd
				) 
				
		
				
				INSERT INTO @tbl_Dates

				SELECT row_number() over ( order by [Date]) as RowNum, [Date] 
				FROM Dates 

				OPTION (MAXRECURSION 400)
				
				-- example to show what is in the table
				select * FROM @tbl_Dates

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jayjay5]

Hi

Thanks, I've tried a few things but just not getting it. I don't understand how to apply it to my query. I need it to get the dates from CombinedDate, I should have given you it all earlier!

select distinct(b.eventid),(b.fromdate) as BookingStartDate,
b.todate as BookingEndDate, e.fromdate as EventStartDate,
e.todate as EventEndDate,
case when b.fromdate IS NOT NULL
then b.fromdate
else e.fromdate end AS CombinedDate
from BOOKING b
LEFT JOIN [EVENT] e
ON b.eventid=e.eventid

Thanks
J

 

[xlbo]

you need to pass the dates from teh query in your 2nd post into the stored proc defined in teh 1st. This will create you a temp table which you can use as the LEFT table in a LEFT OUTER JOIN to the rest of your query that will ensure that dates are returned for each date in the event

If yo uneed more help than that, I'm gonna have to suggest posting in the T-SQL forum or doing a bit more research into how a left outer join works and what a temporary table is

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question