Em 24/10/2012 20:21, Doug Chamberlin escreveu: > On 10/24/12 5:54 PM, cornievs wrote: >> I have query which extract the sales per day from a table >> >> Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, >> SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. >> >> It works 100%, but only returns the dates with sales, I need it to also >> include the dates with zero sales, for example: >> >> YEARNO DAYNO SUM >> 2012 01 5000 >> 2012 02 6000 >> 2012 03 0 (or null will be fine) >> 2012 04 7000 > I would crate a reference table that contains all the years and days > that you want to report on. Then join the data you have to that table > using an outer join so that all dates in the reference table are in the > result set and those that have data from the client invoices will show it. >
I used to do this way, but with CTE you create that "table" dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days
