Home » SQL & PL/SQL » SQL & PL/SQL » Oracle generate from-to DATE ranges and group by (19.2)
Oracle generate from-to DATE ranges and group by [message #685436] Fri, 07 January 2022 04:32 Go to next message
Unclefool
Messages: 28
Registered: August 2021
Junior 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    |


Re: Oracle generate from-to DATE ranges and group by [message #685437 is a reply to message #685436] Fri, 07 January 2022 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68078
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.

Re: Oracle generate from-to DATE ranges and group by [message #685440 is a reply to message #685436] Fri, 07 January 2022 06:52 Go to previous message
Unclefool
Messages: 28
Registered: August 2021
Junior 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
Previous Topic: Defining Variables in the Package Spec
Next Topic: Unexpected Grants To Public
Goto Forum:
  


Current Time: Mon Jan 17 10:07:47 CST 2022