CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
parthi
parthi

Reputation: 11

Group by months range between year in oracle

Friends...I have here a sample data.

+-----------+-------+---------------+
| PRD       | AMT   |   Date        |
+-----------+-------+---------------+
|    1      | 100   |  14-JUL-03    |
|    1      | 100   |  16-JUN-04    |
|    1      | 200   |  18-JUL-04    |
|    1      | 200   |  14-MAY-05    |
|    2      | 100   |  20-JUL-05    |
|    2      | 100   |  14-OCT-05    |
|    2      | 100   |  26-APR-06    |
+-----------+-------+---------------+

I need to sum the amount field by grouping PRD & date between the range (01-JUL-year & 30-June-year+1). Example: For 2003-2004 year, need to compare the range between 01-July-2003 to 30-June-2004. Desired output should be like below:

PRD  |   Sum(AMT) |        year
 1   |    200     |      2003-2004
 1   |    400     |      2004-2005
 2   |    300     |      2005-2006

Any help in providing the query/logic would be much gladly appreciated. Thank you so much!

Upvotes: 1

Views: 547

Answers (1)

Mottor
Mottor

Reputation: 1948

The group criteria is prd and the second one is when you extract 6 months from the Date and truncates to year:

  SELECT prd, 
         SUM (amt), 
         TO_CHAR (ADD_MONTHS ("Date", -6), 'YYYY') || '-' || TO_CHAR (ADD_MONTHS ("Date", 6), 'YYYY') AS year
    FROM table_name
GROUP BY prd, TO_CHAR (ADD_MONTHS ("Date", -6), 'YYYY') || '-' || TO_CHAR (ADD_MONTHS ("Date", 6), 'YYYY')
ORDER BY 1, 3

Upvotes: 2

Related Questions