>>> 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 > I saw that. However, I don't see the advantage. Using a reference table > is simpler and clearer than using CTE or other code to generate the > reference values. > Thanks! I will most likely go with the reference table(s) option, but I will test both.
Regards Cornie [Non-text portions of this message have been removed]
