>>> 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]