Contiguous Date Functionality: SAP HANA

[former_member501914]

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;

[former_member558327]

Hi,

You can use Below Code

SELECT MIN("Period") as "Start_Date",MAX("Period") as "End_Date",(days_between(Min("Period"),Max("Period")) + 1) as "Days"
--days_between(to_date(cast(ROW_NUMBER() OVER(ORDER BY "Date") as Date),'YYYY-MM-DD'), "Date") AS pqr
 FROM
  (select "Period",add_days("Period" ,- ROW_NUMBER() OVER(ORDER BY "Period")) rn
  from "PS_CMP_TIME_ANALYTICS"."Temp2")
 
  GROUP BY rn

regards,

Raja Y