SPLIT Overlapping Date - Sap Hana

former_member805602 — Tue, 31 Jan 2023 16:33:15 GMT

Hi All,
Could someone show me a SQL code that I can write in Hana to be able to split dates as an example?

Re: SPLIT Overlapping Date - Sap Hana

Vitaliy-R — Wed, 01 Feb 2023 00:51:39 GMT

I see you took a question from https://stackoverflow.com/questions/24795907/sql-server-separate-overlapping-dates, so I am curious is this just a theoretical SQL exercise?

Re: SPLIT Overlapping Date - Sap Hana

Vitaliy-R — Wed, 01 Feb 2023 01:01:07 GMT

Watch out for how you define the boundaries of the request 😉 E.g. this code

WITH FLAT_TWO_DATES AS
(
	SELECT 
		"S_DATE",
		"E_DATE",
		LEAD("S_DATE") OVER( PARTITION BY "TYPE" ORDER BY "S_DATE" ) AS lead_s_date,
		LEAD("E_DATE") OVER( PARTITION BY "TYPE" ORDER BY "S_DATE" ) AS lead_E_date,
		"TYPE"
	FROM "SANDBOX"."TESTING"
)
SELECT 
		"S_DATE", ADD_DAYS(lead_s_date, -1) AS "E_DATE", TYPE
FROM FLAT_TWO_DATES
WHERE E_DATE>=LEAD_S_DATE
UNION ALL
SELECT 
		"LEAD_S_DATE" AS "S_DATE", "E_DATE", TYPE
FROM FLAT_TWO_DATES
WHERE E_DATE>=LEAD_S_DATE
UNION ALL
SELECT 
		ADD_DAYS("E_DATE", 1) AS "S_DATE", "LEAD_E_DATE" AS "E_DATE", TYPE
FROM FLAT_TWO_DATES
WHERE E_DATE>=LEAD_S_DATE
UNION ALL
SELECT 
		"S_DATE" AS "S_DATE", "E_DATE", TYPE
FROM FLAT_TWO_DATES
WHERE E_DATE<LEAD_S_DATE
UNION ALL 
SELECT 
		"LEAD_S_DATE" AS "S_DATE", "LEAD_E_DATE" AS "E_DATE", TYPE
FROM FLAT_TWO_DATES
WHERE E_DATE<LEAD_S_DATE

ORDER BY "TYPE", "S_DATE";

will do what is required

but only for the data such as in the example you provided: with two records per `TYPE` 🙂

CREATE TABLE sandbox.testing(
	pkey INT,
	s_date DATE,
	e_date DATE,
	type CHAR(1)
);

insert into sandbox.testing(PKey,s_date,e_date,Type) values
(1,'20100101','20100114','S');
insert into sandbox.testing(PKey,s_date,e_date,Type) values
(2,'20100110','20100131','S');
insert into sandbox.testing(PKey,s_date,e_date,Type) values
(3,'20100105','20100130','A');
insert into sandbox.testing(PKey,s_date,e_date,Type) values
(4,'20100124','20100206','A');
insert into sandbox.testing(PKey,s_date,e_date,Type) values
(5,'20100120','20100127','T');
insert into sandbox.testing(PKey,s_date,e_date,Type) values
(6,'20100128','20100130','T');

Regards,
-Witalij

Re: SPLIT Overlapping Date - Sap Hana

former_member805602 — Wed, 01 Feb 2023 07:49:52 GMT

Hi Witalij,

Thanks for your support.

But suppose i have also 20 records For type not only two can i have the same result?

Thanks

topic Re: SPLIT Overlapping Date - Sap Hana in Application Development and Automation Discussions

SPLIT Overlapping Date - Sap Hana

Re: SPLIT Overlapping Date - Sap Hana

Re: SPLIT Overlapping Date - Sap Hana

Re: SPLIT Overlapping Date - Sap Hana