How to get date difference with in the same column in a table using sql?

[former_member804254]

for example:

ID DATE

101 20220201

102 20220204

103 20220305

104 20220406

I Need the days difference between the rows in separate column? can anyone expain this?

[Vitaliy-R]

It would be best if you used the window function, in this case, eg. `LAG`: https://help.sap.com/docs/HANA_CLOUD_DATABASE/c1d3f60099654ecfb3fe36ac93c121bb/e7ef7cc478f14a408e1af...

Here is my snippet:

SELECT 
	"ID",
	"DATE", 
	DAYS_BETWEEN (TO_DATE (LAG("DATE") OVER (ORDER BY "ID"), 'YYYYMMDD'), TO_DATE ("DATE", 'YYYYMMDD')) AS "DAYS_DIFFERENCE_WITH_PREV"
FROM "EXAMPLE_TABLE";

Here is the result:

ID	DATE		DAYS_DIFFERENCE_WITH_PREV
101	20220201	NULL
102	20220204	3
103	20220305	29
104	20220406	32

Regards.

[Vitaliy-R]

I used an example in SAP HANA, but it should be the same or similar with SAP SQL Anywhere.

I assumed there are INTEGER columns:

CREATE TABLE "EXAMPLE_TABLE"(
	id INT,
	date INT
);
INSERT INTO "EXAMPLE_TABLE" VALUES(101, 20220201);
INSERT INTO "EXAMPLE_TABLE" VALUES(102, 20220204);
INSERT INTO "EXAMPLE_TABLE" VALUES(103, 20220305);
INSERT INTO "EXAMPLE_TABLE" VALUES(104, 20220406);

[robert_kratschmann]

SQL Anywhere didn't know LAG. You must use:

with cte as (select *, row_number() over (order by "date") as "Row" from EXAMPLE_TABLE )
select c1.*, c2."date", datediff(day, c1."date", c2."date") as Difference from
cte c1 inner join cte c2 on c1."Row" = c2."Row"-1;