Sql Query To Find Earliest Date Dependent On Column Value Changing

I have a problem where I need to get the earliest date value from a table grouped by a column, but sequentially grouped. Here is a sample table: if object_id('tempdb..#tmp') is N

Solution 1:

SELECT  JobCodeId, MIN(LastEffectiveDate) AS mindate
FROM    (
        SELECT  *,
                prn - rn AS diff
        FROM    (
                SELECT  *,
                        ROW_NUMBER() OVER (PARTITION BY JobCodeID 
                                    ORDERBY LastEffectiveDate) AS prn,
                        ROW_NUMBER() OVER (ORDERBY LastEffectiveDate) AS rn
                FROM    @tmp
                ) q
        ) q2
GROUPBY
        JobCodeId, diff
ORDERBY
        mindate

Continuous ranges have same difference between partitioned and unpartitioned ROW_NUMBERs.

You can use this value in the GROUP BY.

See this article in my blog for more detail on how it works:

• Grouping continuous ranges

Solution 2:

First comment - using a table variable not a temp table would be better practice. Then you can use a trick like this. Make sure you insert the values in the right order (i.e. ascending LastEffectiveDate):

DECLARE@tmptable
(
    Sequence            INTIDENTITY,
    UserID              BIGINT,
    JobCodeID           BIGINT,
    LastEffectiveDate   DATETIME
)

INSERTINTO@tmpVALUES ( 1, 5, '1/1/2010') 
INSERTINTO@tmpVALUES ( 1, 5, '1/2/2010') 
INSERTINTO@tmpVALUES ( 1, 6, '1/3/2010') 
INSERTINTO@tmpVALUES ( 1, 5, '1/4/2010') 
INSERTINTO@tmpVALUES ( 1, 1, '1/5/2010') 
INSERTINTO@tmpVALUES ( 1, 1, '1/6/2010')

SELECT TOP 1 JobCodeID, LastEffectiveDate
FROM@tmpUNIONALLSELECT t2.JobCodeID, t2.LastEffectiveDate
FROM@tmp t1
    INNERJOIN@tmp t2
        ON t1.Sequence +1= t2.Sequence
WHERE t1.JobCodeID <> t2.JobCodeID

This outputs the first date each time the job code changes, which I am guessing is what you want from your description.