Skip to Content
0
Jun 06, 2018 at 01:56 PM

Contiguous Date Functionality: SAP HANA

74 Views Last edit Jun 06, 2018 at 02:31 PM 2 rev

We are trying to find continuous date from table

The expected output is attached in the image below:

Attaching the queries we have tried in SAP HANA

But we are not getting the output as expected find the attached out we got using SAP HANA SQL The END date should be changed.(Our output Image attached)

create column table "PS_CMP_TIME_ANALYTICS"."Temp2" (
"ID"  integer,
"Period"  date);

INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (4, '2010-04-03');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (5, '2010-04-07');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (2, '2010-04-10');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (3, '2010-04-15');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (6, '2010-04-16');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (7, '2010-04-17');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (3, '2010-04-22');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (4, '2010-04-24');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (7, '2010-04-30');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (2, '2010-05-01');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (5, '2010-05-02');
INSERT INTO "PS_CMP_TIME_ANALYTICS"."Temp2" VALUES (3, '2010-05-03');


SELECT MIN("Period") AS BeginRange,
       MAX("Period") AS EndRange
FROM (
SELECT "Period",
    --DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY "Period"), "Period") AS DtRange
cast(ROW_NUMBER() OVER(ORDER BY "Period") as date) as xyz,

    days_between(to_date(cast(ROW_NUMBER() OVER(ORDER BY "Period") as 
Date),'YYYY-MM-DD'), "Period") AS DtRange
FROM "PS_CMP_TIME_ANALYTICS"."Temp2") AS dt
GROUP BY DtRange;


Attachments

image4.png (7.7 kB)
unnamed.png (6.2 kB)