I am trying to create a report for a range of dates. The report will be run with a 'from' and 'to' date. I would like a row returned if the 'start' and 'end' date for that record are between the from and to date. For example, I have a reservation from July 1st to July 5th. I would like a count for this record on 7/1, 7/2, 7/3 and 7/4. And also group by these dates. So have a total for 7/1, 7/2, etc. The record in the table has a 'Start' and 'End' date. Would I create a dynamic prompt? I know how to do it in sql, I would create a temp table to hold all my dates, then join to my main table, however this is through Crystal reports running on an Informix database. - Can this be done?
This is what I am trying to add to the begining of the report, to get a table of the dates I input for my parameters. The 'Fromdate' and 'ToDate' will be my parameters. I can do it all in a query, but not in Crystal
DECLARE @FROMDATE DATETIME, @TODATE DATETIME, @ADATE1 DATETIME
SET @FROMDATE = '07/01/2009'
SET @TODATE = '07/31/2009'
CREATE TABLE template (hotelnum INT, Adate datetime)
set @ADATE1 =@FROMDATE
WHILE @ADATE1 <= @TODATE
BEGIN
INSERT INTO template (hotelnum, Adate)
values(1958,@ADATE1
SET @ADATE1 = DATEADD(DD,1,@ADATE1)
END
Then I can build this query with this table
SELECT res.hotelnum, res.status, t1.Adate,
Case When t1.adate = res.departdate then 0
else 1 end as 'Count',
res.arrivedate,
res.departdate,
res.departdate -res.arrivedate as 'StayLength',
res.marketseg,
res.guestnum,
res.roomrate,
res.accomcode
FROM #template t1, ewr_galaxy_reservations res
WHERE res.hotelnum=t1.hotelnum
and t1.adate between res.arrivedate and (res.departdate-1)
and t1.adate between @FROMDATE AND @TODATE
Edited by mbottley - 16 Jun 2009 at 3:59pm
IP Logged
