Startdate -1 and enddate

Shambhu Rai 1,406 Reputation points
2022-03-15T12:21:09.37+00:00

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

183285-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,903 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. Shambhu Rai 1,406 Reputation points
    2022-03-15T13:17:38.237+00:00

    I tried

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

    0 comments No comments

  2. Shambhu Rai 1,406 Reputation points
    2022-03-15T13:44:31.193+00:00

    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

    0 comments No comments

  3. Sudipta Chakraborty - MSFT 1,101 Reputation points Microsoft Employee
    2022-03-15T13:52:41.677+00:00

    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;
    

  4. Naomi 7,361 Reputation points
    2022-03-15T14:11:26.923+00:00

    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
    

  5. Shambhu Rai 1,406 Reputation points
    2022-03-15T14:58:24.143+00:00

    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

    183353-screenshot-2022-03-15-185449.png

    0 comments No comments