CODEWITHSUNDEEP
sqlsql-server
NiMuSi
NiMuSi

Reputation: 412

Self Join in Grouping Query

I have a table as follows:

PERSON_REF, CODE_START_DATE, CODE

where the PK is PERSON_REF

I want a query giving me those records where the CODE_START_DATE is in August 2012 together with the record with the latest CODE_START_DATE Prior to this, ie this pseudocode

SELECT PERSON_REF,
       CODE_START_DATE,               --S/B in August 2012
       CODE,
       CODE_START_DATE [PrevDate],    --Latest CODE_START_DATE Prior to Column 2 in Query
       CODE [PrevCode]                --Code at Date of Column 4 in Query

Columns 4 and 5 might be blank, in the case where there is no record prior to the column2 date

Upvotes: 1

Views: 70

Answers (2)

hgulyan
hgulyan

Reputation: 8239

Since you're using SQL Server, give a try to outer apply. Here's the query

SELECT T1.*, T2.CODE_START_DATE PrevDate, T2.CODE PrevCode
FROM TableName T1 
 OUTER APPLY (
     SELECT TOP 1 *
     FROM TableName T2
     WHERE T2.CODE_START_DATE < T1.CODE_START_DATE 
     ORDER BY T2.CODE_START_DATE DESC
) T2
WHERE T1.CODE_START_DATE BETWEEN '01/Aug/12' AND '01/Sep/12'

UPDATE:

I've added where condition for rows in August 2012

Upvotes: 2

mehdi lotfi
mehdi lotfi

Reputation: 11571

You Can Use Below Query :

SELECT A.PERSON_REF,
       A.CODE_START_DATE,
       A.CODE,
       (Select Top 1 B.CODE_START_DATE From YourTable B Where B.CODE_START_DATE > A.CODE_START_DATE Order by CODE_START_DATE DESC)AS [PrevDate],
       (Select Top 1 B.CODE From YourTable B Where B.CODE_START_DATE > A.CODE_START_DATE Order by CODE_START_DATE DESC) AS [PrevCode]
From YourTable A
Where ..Conditions...

Upvotes: 0

Related Questions