More SQL Help (97 SR2)

Topic

New Reply

Reporting information that is NOT there is always a challenge. The only thing I can think of is to create a Dates table that includes the dates you want, and then use an Outer Join to link it to your regular table, based on date. I can remember if this is a right or left outer join, but essentially you want every date from the Dates table, plus any matching records from your regular table.

Reply | Quote

Replies

Alright, so i’ve got a nice little SQL statement:

SELECT Format(tblmain.DateStarted,"dddd") AS Day, 
Count(tblmain.DateStarted) AS Entered, 
Abs(Sum([Status]="Complete")) AS Complete, 
Abs(Sum([Status]="Pending")) AS Pending
FROM tblmain
GROUP BY tblmain.DateStarted
HAVING (((tblmain.DateStarted) Between Date() And Date()-7));

The problem with this is, if for any reason nothing was entered on a particular day, the resulting query skips right over it. Jumping for example, from monday to wednesday if nothing was entered on Tuesday. This adds irregularity to my report. Is there a way for me to represent that if nothing was done on Tuesday, then the values for tuesday would be Zero?

Thanks!

Edited to eliminate horizontal scrolling–Charlotte

Reply | Quote

I agree with Mark’s suggestion, I think it’s the correct way to do it.

You’ll want an left outer join from the dates table to the existing table. Something like:-

SELECT
   Format(tblmain.DateStarted,"dddd") AS Day,
   Count(tblmain.DateStarted) AS Entered,
   Abs(Sum([Status]="Complete")) AS Complete,
   Abs(Sum([Status]="Pending")) AS Pending
FROM
   tblDates
      LEFT JOIN
         tblMain
         ON
            tblDates.DateWanted=tblMain.DateStarted
GROUP BY
   tblmain.DateStarted
HAVING
   (((tblmain.DateStarted) Between Date() And Date()-7));

N.B. in the above example your dates table will be called ‘tblDates’ with a field named ‘DateWanted’ containing the days you want to run your report for…

Reply | Quote

>>You’ll want an left outer join from the dates table to the existing table. <<

One of these days I'll pull out Joe Celko's book again, and brush up on outer joins. I always get these confused!

Reply | Quote

Do I want to manually populate the dates field with every concievable date for the next 5 years or so? or is there a better way?

Thanks for all your help!

Reply | Quote

The better way is to write code that will create the dates for you, one day at a time, for the desired number of years.

Reply | Quote