Em 24/10/2012 20:37, Doug Chamberlin escreveu:

> On 10/24/12 6:25 PM, Alexandre Benson Smith wrote:
>> 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
> 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.
>
>
>
>

One don't need to "refill" the values, like at the beginning of each
year... of course you could pre-fill i with a century of days... :)