Need to list all dates irrespective of data presence

[AurobindoSaha]

Hi,

I am using Crystal Report XI R2 and MS SQL Server 2005. Will publish the reports in Infoview.

Background -

User has input parameters -
1. Input_Date (which is a date parameter)
2. Frequency (which is a string parameter with values - "Monthly" or "Weekly")

Requirement -
Based on the above 2 parameters, the records returned should be -

1. For "Weekly" frequency - All records between Input_Date and 6 days prior to Input_Date

2. For "Monthly" frequency - All records between Input_Date and 13 months prior to Input_Date

Data should be reported in format shown below, even if the record for a particular date is not present in the database table -

MM/DD/YYYY records

Note: If the date with which the input date range is compared is not present in the database, the records should be shown with value 0.

Challenge -
How do I get the dates where no records are present in database table?


Solution I thought of -
Use a command object and create a dummy recordset that lists all dates based on frequency. Left join this record set with the database table so that all records that are not present in the right side of the join (db table) are returned as 0. But how to create that dummy date resultset in MS SQL 2005?

Your help will be appreciated.

Thanks
Aurobindo

 

[dgillz]

Your solution is right on. Crystal cannot report on non existent data, so you'll need a date table with all dates in it, and use it i your report.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[AurobindoSaha]

Thanks for your reply Dgillz. I was trying to avoid the table creation complexity as its handled by a different DBA group here.

Can the SQL_Expression somehow help to generate the dates?