I have a table with 3 columns: clientid, start_date, end_date

Each row is an episode of care, clients can have multiple episodes of care. I am trying to write a query in SQL Server which will display the start and end date of the latest episode of care only if it is 21 days after the previous episodes end date. Can anyone help please?

CodePudding user response：

'I do not understand how to compare the end date from one episode (row 1) with the start date of the next episode (row 2).' is straightforward but you cannot use window functions in the where clause or the calculated difference - which means using a sub query (or a cte) to do the bulk of the work

drop table t
go
create table t
(clientid int, start_date date, end_date date);
go
insert into t values
(1,'2022-09-01','2022-09-01'),
(1,'2022-09-10','2022-09-10')

go

select * from
(
select *,
 lag(end_date) over (partition by clientid order by start_date) lage,
 datediff(d,lag(end_date) over (partition by clientid order by start_date),start_date) diff
 from t
) s
where diff = 9

clientid    start_date end_date   lage       diff
----------- ---------- ---------- ---------- -----------
1           2022-09-10 2022-09-10 2022-09-01 9

(1 row(s) affected)

CodePudding user response：

LAG worked for me. Thanks for the help.