Startdate -1 and enddate

Question

Hi Expert,
I am expecting enddate column from startdate -1

CREATE TABLE [dbo].Table3 ON [PRIMARY]
GO

insert into table3
values
('1','Progress', '20210203'),
('1', 'Closed', '20220403'),
('2', 'Closed', '20220703'),
('2', 'Progress', '20220503')

I wanted to get end date column from startdate -1 from start where count(testid) >1

expected output

Answer 1

I tried

select Testid, Status ,count(testid) as count,
Startdate, DATEADD(day,-1,Startdate) as enddate from table3 group by Testid,Status,Startdate

Answer 2

I tried lead function but getting repeated enddate where it should be reflects one

select Testid, Status ,count(testid) as count,
Startdate, lead(DATEADD(day,-1,Startdate)) over (order by testid,Startdate) from table3 group by Testid,Status,Startdate

Answer 3

You can try the following query (here [dbo].[Application] is the name of the table):

SELECT TestId,  DATEADD(dd, -1,(substring(StartDate,0,(len(StartDate)-3))+'-'+
substring(StartDate,len(StartDate)-3,2)+'-'+
substring(StartDate,(len(StartDate)-1),len(StartDate))))
AS EndDate 
FROM [dbo].[Application] group by TestId having count(TestId)>0;

Answer 4

This result doesn't match your picture, but matches your description?

SELECT TestID, Status, StartDate, DATEADD(DAY,-1,LEAD(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate)) AS EndDate
FROM #Table3
ORDER BY Testid, Startdate

Answer 5

I have repeated and loaded the insert statement

insert into table3

values

('1','Progress', '20210203'),

('1', 'Closed', '20220403'),

('2', 'Closed', '20220703'),

('2', 'Progress', '20220503')

it is showing wrong output

Screenshot 2022-03-15 185449

if the date is repeated then it should show the same and not the before date ..now it is showing 1 day minus from start date