SQL: Change Date and group by Fiscal Month End

Question

I have created the following table (via UNION ALL):

ID       Date        Hour   Weight Fiscal
AAA       1/27/2009    0    10       0
AAA       1/30/2009    0    20       0
AAA       2/14/2009     0    10       0
AAA       2/18/2009     0    20       0
AAA       2/27/2009     0    20       0
AAA       2/28/2009     0    20       0
AAA       1/14/2009    10    0       0
AAA       2/14/2009    20    0       0
AAA       2/16/2009    10    0       0
AAA       2/25/2009    10    0       0
AAA       2/26/2009    10    0       0
AAA       3/3/2009          20    0       0
NULL          0               0    0       1/28/2009
NULL       0                 0    0       2/27/2009
NULL       0                 0    0       3/29/2009

And I would like to retrieve:

    ID  Date  Hour Weight
    AAA  1/28/2009 10 10
    AAA  2/27/2009 50 50
    AAA  3/29/2009 20 20

It should first convert the DATE column to the next Fiscal month end FISCAL (for example 1/27/2007 -> 1/28/2009, 1/30/2009 -> 2/27/2009, etc.) and then group by that value.

I was able to use:

    SELECT  ID
    ,      left(CONVERT(VARCHAR(10)
    ,      date, 111),7) as Date
    ,      round(sum(Hours),0) as Hours
    ,      round(sum(Weight),0) as Weight
    FROM    ...
    GROUP   BY ID
    ,     left(CONVERT(VARCHAR(10), date, 111),7)
    ORDER   by ID ASC
    ,     left(CONVERT(VARCHAR(10), date, 111),7) ASC

But that just groups by calendar dates, not according to the Fiscal month ends I have in the FISCAL column.

Answer 1

You should not union the fiscal dates into your table. But with the table as you have now, you could first create a table (using WITH, so no special priviliges are necessary) with the fiscal 'periods' and join this table with your main table.

I called your Table SO. I also had to rename your columns to cHour and CDate because in Oracle at least Date is a reserved word. You could very probably create the table 'main' and the table 'fiscal' directly from your source tables to speed things up:

with fiscal_part as (
            select fiscal
            from   SO
            where  fiscal is not null)
,    fiscal as (
            select fb.fiscal         fiscal_begin
            ,      min(fe.fiscal)    fiscal_end
            from   fiscal_part fb right join 
                   fiscal_part fe
                on fe.fiscal > fb.fiscal
            group by fb.fiscal)
,    main as (
            select *
            from   SO
            where  fiscal is null)
select main.ID
,      fiscal.fiscal_end
,      sum(main.cHour)
,      sum(main.weight)
from   main
join   fiscal on main.cdate >= coalesce(fiscal.fiscal_begin, main.cdate)
             and main.cdate <  fiscal.fiscal_end
group by main.ID
,      fiscal.fiscal_end
order by fiscal.fiscal_end

Also note that your sample data has an error; the weight for your last period should be 40 (the rows with dates 2/27 and 2/28).