SQL query to return a column of previous date

[tnguyen315]

Let me explain this,
the query result:

ID VisitDate
125 1/1/2012
125 8/25/2012
125 1/8/2013
257 6/7/2012
257 9/15/2012
257 3/15/2013
257 6/1/2013

Now i would like to create another new column call "Previous Date" and pull data from VisitDate such as:

ID VisitDate Previous Date
125 1/1/2012
125 8/25/2012 1/1/2012
125 1/8/2013 8/25/2012
257 6/7/2012
257 9/15/2012 6/7/2012
257 3/15/2013 9/15/2012
257 6/1/2013 3/15/2013

Please help,
Thanks in advance.
Twee

 

[gmmastros]

Try this:

; With Data As
(
  Select  Id, 
          VisitDate,
          Row_Number() Over (Partition By Id Order By VisitDate) as RowId
  From    YourTableNameHere
)
Select  A.Id,
        A.VisitDate,
        B.VisitDate As PreviousVisitDate
From    Data As A
        Left Join Data As B
            On A.Id = B.Id
            And B.RowId = A.RowId - 1

If this works for you, and you would like me to explain it, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

If you're running SQL Server 2012, you have access to the LAG function. I'm not running it but I believe the syntax would be something like this...

SELECT ID, VisitDate, LAG(VisitDate) OVER (PARTITION BY ID ORDER BY VisitDate) As PreviousVisitDate
  FROM YourTable

 

[tnguyen315]

Thank you so much DaveInIowa and gmmastros

I tried LAG funciton but mine is still 2008 R2 server, and got error because it's not recognized the function.


I used gmmastros code on a simple query, it worked but took so long to get the result.

With Data As
(
Select PATIENT_NO, IMM_DATE,
Row_Number() Over (Partition By PATIENT_NO Order By IMM_DATE) as RowId
FROM dbo.IMFILE
WHERE (IMM_DATE BETWEEN CONVERT(DATETIME, '2013-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-05-31 00:00:00', 102))
)
Select A.PATIENT_NO,
A. IMM_DATE,
B. IMM_DATE As PreviousVisitDate
From Data As A
Left Join Data As B
On A.PATIENT_NO= B.PATIENT_NO
And B.RowId = A.RowId - 1


Now i tried to put back the code on my real query but couldn't figure out how
Please help...


SELECT DISTINCT
f.PATIENT_NO, DATEPART(YY, GETDATE() - m.DOB) - 1900 AS Age, m.SEX, r.DESCRIPTION AS Race, c.DESCRIPTION AS Clinic,
et.DESCRIPTION AS Ethnicity, m.ADDRESS, m.CITY, m.STATE, m.ZIP_CODE AS Zip, t.DESCRIPTION AS [Shots Received], CONVERT(CHAR(10), f.ENCOUNTER_DATE,
101) AS [Visit Date]
FROM dbo.ENHFILE AS f RIGHT OUTER JOIN
dbo.ENDFILE AS e ON e.ENCOUNTER_NO = f.ENCOUNTER_NO RIGHT OUTER JOIN
dbo.IMFILE AS i ON i.ENCOUNTER_NO = f.ENCOUNTER_NO INNER JOIN
dbo.TBCOSITE AS c ON c.COSITE = f.COSITE INNER JOIN
dbo.TBIMMUNE AS t ON t.VACCINE_CODE = i.VACCINE_CODE RIGHT OUTER JOIN
dbo.MPFILE AS m ON m.PATIENT_NO = f.PATIENT_NO LEFT OUTER JOIN
dbo.TBRACE AS r ON r.RACE = m.RACE LEFT OUTER JOIN
dbo.TBETHNIC AS et ON et.ETHNICITY = m.ETHNICITY
WHERE (f.ENCOUNTER_DATE >= CONVERT(DATETIME, '2012-01-01 00:00:00', 102)) AND (e.SUB_PROGRAM IN ('IMM', 'CFV')) AND
(f.ENCOUNTER_DATE <= CONVERT(DATETIME, '2013-12-31 00:00:00', 102))
ORDER BY f.PATIENT_NO, [Visit Date] DESC