startdate -1
-
Hi Expert,
I am expecting enddate column from startdate -1
CREATE TABLE [dbo].[Table3](
[Testid] [nchar](10) NULL,
[Status] [nchar](10) NULL,
[Startdate] [date] NULL
) 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
I tried
select Testid, Status ,count(testid) as count,
Startdate, DATEADD(day,-1,Startdate) as enddate from table3 group by Testid,Status,Startdate
expected output
Please refer above output and not from attachment as i am unable to remove attachement
-
Your expected output is inconsistent.
That said, I believe that you need to look at the LAG function.
-
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
I tried above lead function but unable to get above output
-
You are on the right track.
SELECT t.Testid
, t.Status
, t.Startdate
, EndDate = DATEADD(dd, -1, LEAD(t.Startdate) OVER (PARTITION BY t.Testid ORDER BY t.Startdate))
FROM Table3 AS t -
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
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
-
i tried
SELECT TestID, Status, StartDate, case when Lead(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate) =LEAD(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate) then Lag(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate) else DATEADD(dd, -1, LEAD(StartDate) OVER (PARTITION BY TestID ORDER BY StartDate)) end
FROM Table3
ORDER BY Testid, Startdate
but getting wrong output
- Shree23 wrote:
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
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
The solution that I provided was correct for what you requested. Now you have changed the requirements, but not provided the expected results
-
So, taking a guess at what you are looking for ....
WITH cteData AS (
SELECT t.Testid
, t.Status
, t.Startdate
, EndDate = DATEADD(dd, -1, LEAD(t.Startdate) OVER (PARTITION BY t.Testid ORDER BY t.Startdate))
FROM Table3 AS t
GROUP BY t.Testid, t.Status, t.Startdate
)
SELECT t.Testid
, t.Status
, t.Startdate
, cte.EndDate
FROM Table3 AS t
INNER JOIN cteData AS cte
ON t.Testid = cte.Testid
AND t.Status = cte.Status
AND t.Startdate = cte.Startdate
ORDER BY cte.Testid, cte.Startdate; -
Hi Expert,
I am so sorry
here is the expected output
-
The following code satisfies your requirement
SELECT t.Testid
, t.Status
, t.Startdate
, EndDate = CASE WHEN t.Startdate = LEAD(t.Startdate) OVER (PARTITION BY t.Testid ORDER BY t.Startdate)
THEN t.Startdate
ELSE DATEADD(dd, -1, LEAD(t.Startdate) OVER (PARTITION BY t.Testid ORDER BY t.Startdate))
END
FROM #Table3 AS t -
Hi Expert,
it is showing incorrect output. Am attaching the expected output again
-
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply