sql - Change Date and group by Fiscal Month End -

I created the following table (union all):

  date of ID Hours Weight Financial AAA 1/2/2009/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 AAA 2/14/2009 20 0 AAA 2/16/2009 10 0 AAA 2/25/2009 10 0 AAA 2/26 / 2009 10 0 AAA 3/3/2009 20 0 Faucet 0 0 0 1/28/2009 Faucet 0 0 2/27/2009 Faucet 0 0 3/2 9/2009  

And I want to retrieve:

  ID date hour weight AAA 1/2 8/2009 10 10 AAA 2/27/2009 50 50 AAA 3/2 9/2009 20 20  

before The column should be changed in the interval of the next fiscal year (e.g. 1/27/2007 -> 1/28/2009, 1/30/2009 -> 2/27/2009, etc.) and after that group .

I was able to use it:

but it is only related to the group calendar , does not happen according to the month of month i in the FISCAL column.

You should not be linked to fiscal dates in your table. But now with your table, you can make a table for the first time (together, special privileges are required) Join the fiscal period and join your main table in this table.

I called your table SO I had to give seahor and CDAT to change the name of my column because at least date is a reserved word in Oracle because you can probably have the table 'main' and the table 'fiscal' You can create straightforward things from your source tables:

  as financial_part (where the fiscal is not null, therefore select financial), as a fiscal (fb .fiscal fiscal_begin, min (fe.fis) Cal) Fix fiscal_end with fiscal_part Fb, Feastible and in the fst. Fiscal & ft; fb.fiscal group), main CD, fiscal.fiscal_end, sum (main.cHour), sum (main.weight ) Select key cdate & gt; = Coalesce (financial.fiscal_begin, main.cdate) and main.cdate and lt from the main involved financially. ; Order fiscal fiscal.fiscal_end by the fiscal.fiscal_end group, main.id, financial fiscal.fiscal_end  

Also note that there is an error in your sample data; Your last period should be weight 40 (lines 2/27 and 2/28 along with dates).