OraFAQ Forum: SQL & PL/SQL » Oracle generate from-to DATE ranges and group by

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle generate from-to DATE ranges and group by (19.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle generate from-to DATE ranges and group by [message #685436]

Fri, 07 January 2022 04:32

Unclefool
Messages: 65
Registered: August 2021

Member

I have a list of date's, which I would like to output in a from-to format.

I want to group the value (by item and category too) only if they are the same for consecutive dates. Below is my sample data and expected output.

Any help would be greatly appreciated.


(SELECT to_date('01/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('02/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('03/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 80000 Value FROM dual UNION ALL
SELECT to_date('04/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('05/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 1 Category, 500 Value FROM dual UNION ALL
SELECT to_date('01/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('02/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('03/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('04/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('05/01/2022', 'dd/mm/yyyy') daytime, 'A' Item, 2 Category, 600 Value FROM dual UNION ALL
SELECT to_date('01/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('02/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('03/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('04/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual UNION ALL
SELECT to_date('05/01/2022', 'dd/mm/yyyy') daytime, 'C' Item, 1 Category, 600 Value FROM dual)


Expected output

| FromDate  | ToDate    | Item |Category| Value |
| --------- |---------  |------|------  |-------|
| 01.01.2022| 02.01.2022|A     |1       |500    |
| 03.01.2022| 03.01.2022|A     |1       |80000  |
| 04.01.2022| 05.01.2022|A     |1       |500    |
| 01.01.2022| 05.01.2022|A     |2       |600    |
| 01.01.2022| 05.01.2022|C     |1       |600    |

Report message to a moderator

Re: Oracle generate from-to DATE ranges and group by [message #685437 is a reply to message #685436]

Fri, 07 January 2022 05:14

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Click on the "Search" link above and put "consecutive dates" in the search field.

Report message to a moderator

Re: Oracle generate from-to DATE ranges and group by [message #685440 is a reply to message #685436]

Fri, 07 January 2022 06:52

Unclefool
Messages: 65
Registered: August 2021

Member

Thanks I got it

SELECT TRUNC(Daytime, 'MM') FromDate,
ADD_MONTHS(TRUNC(Daytime, 'MM'), 1) ToDate,
Item, Category,
SUM(Value) Value
FROM my_table
GROUP BY TRUNC(Daytime, 'MM'), Item, Category

Report message to a moderator

Previous Topic:	Defining Variables in the Package Spec
Next Topic:	Unexpected Grants To Public

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 05:41:08 CDT 2023