CODEWITHSUNDEEP
sqlsql-servert-sql
user1505676
user1505676

Reputation: 45

Getting RefToDate of the Previous Row to become the RefFromDate of the next Row TSQL

I have a table with data on the below format:

ProjID   ProjName    RefDate    
-------- ----------- ---------- 
1        A           08/02/2013 
1        A           08/03/2013 
1        A           08/15/2013 
2        B           08/02/2013 
2        B           08/03/2013 
2        B           08/15/2013 
2        B           08/20/2013

I want a resultset that looks like below:

ProjID   ProjName    StartDate  EndDate    
-------- ----------- ---------- ------------
1        A           08/02/2013 08/02/2013 
1        A           08/02/2013 08/03/2013
1        A           08/03/2013 08/15/2013
2        B           08/02/2013 08/02/2013
2        B           08/02/2013 08/16/2013 
2        B           08/16/2013 08/20/2013 
2        B           08/20/2013 08/22/2013

The StartDate is copied from the refdate of the previous row.

How to have a TSQL statement to come up with the resultset mentioned above? I can do an iteration but it's not the optimal way to do it IMHO.

Upvotes: 1

Views: 58

Answers (2)

Luis LL
Luis LL

Reputation: 2993

if you run SQL2005 or later, you can

  • self join to pick the next date
  • and union the first row for each project

something like this:

;
WITH    ProjectDataRanked
      AS ( SELECT   ProjID ,
                    ProjName ,
                    RefDate ,
                    ROW_NUMBER() OVER ( PARTITION BY ProjID, ProjName ORDER BY RefDate ) RN
           FROM     ProjectData
         )
SELECT  ProjectData.ProjID ,
        ProjectData.ProjName ,
        ProjectData.RefDate AS StartDate ,
        MIN(ProjectDataNext.RefDate) AS EndDate
FROM    ProjectData
        INNER JOIN ProjectData ProjectDataNext ON ProjectData.ProjID = ProjectDataNext.ProjID
                                                  AND ProjectData.ProjName = ProjectDataNext.ProjName
                                                  AND ProjectData.RefDate > ProjectDataNext.RefDate
GROUP BY ProjectData.ProjID ,
        ProjectData.ProjName ,
        ProjectData.RefDate
UNION
SELECT  ProjID ,
        ProjName ,
        RefDate AS StartDate ,
        RefDate AS EndDate
FROM    ProjectDataRanked
WHERE   RN = 1
ORDER BY ProjID ,
        ProjName ,
        StartDate ,
        EndDate

Upvotes: 1

rs.
rs.

Reputation: 27467

If you are using SQL Server 2005/2008 then you can try this

;with cte as
(
  select *,
  row_number() over (partition by projid order by refdate) rn
  from projects
)
select c.projid, c.projname,
coalesce(l.refdate, c.refdate) as startdate,
c.refdate as enddate
from cte c
left outer join cte l 
  on c.projid = l.projid and (c.rn -1) = l.rn
order by 1,3,4

SQL DEMO

SQL Server 2012 Supports LAG Function which you can use to achieve same result

select 
c.projid, c.projname,
coalesce(lag(c.refdate)
         over (partition by projid 
               order by refdate),c.refdate)
as startdate,
c.refdate as enddate
from projects c
order by 1,3,4;

SQL DEMO for 2012

Upvotes: 1

Related Questions