Displaying non-existing dates

[acessn]

Reporting from SQL server 2003, using CR10.

In my report I have grouped on person and date to display number of hours worked pr person pr date. Easy and basic.

My problem is that the database does not contain an entry on every day on every person, hence, I cannot display all dates, just the dates they have actually worked.

Example for last week. Mr. X have worked 8 hours Monday-Wednesday, nothing on thursday and Saturday, 6 hours Friday and Sunday.

Output will be:

Mr.X (Group header 1)
Mo 19/5 8 hours (Group footer 2)
Tu 20/5 8 hours (Group footer 2)
We 21/5 8 hours (Group footer 2)
Fr 23/5 6 hours (Group footer 2)
Su 25/5 6 hours (Group footer 2)

Sum Last week 36 hours (Group footer 1)

Wanted output is:
Mo 19/5 8 hours
Tu 20/5 8 hours
We 21/5 8 hours
Th 22/5 0 hours
Fr 23/5 6 hours
Sa 24/5 0 hours
Su 25/5 6 hours

Sum Last week 36 hours


Any ideas on how to achieve this without adding a table containing all dates?

Thanks,
Bjorn

 

[dgillz]

Create a date database with all the dates you desire to report on, and left outer join it to the database you are currently reporting from. Be sure to use this data, not the current data you are using, to bring in the date. All dates will show up.

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

http://www.gainfocus.biz

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

 

[acessn]

Do you mean that I should do the joining in Crystal?
I've tried that before with no luck, but I guess I can try to create a table containing all dates, and use that instead of creating a new db.

My hope was that I did not need to go there, but maybe create a group footer 2b and do some logic on the dates..
If the difference between previous record and current record is >1, I could add previousrecord +1 or something like that.

Thanks,
Bjorn

Thanks,
Bjorn