SQL Query to find earliest date dependent on column value changing

sql, sql-server, t-sql

Solution

SELECT  JobCodeId, MIN(LastEffectiveDate) AS mindate
FROM    (
        SELECT  *,
                prn - rn AS diff
        FROM    (
                SELECT  *,
                        ROW_NUMBER() OVER (PARTITION BY JobCodeID 
                                    ORDER BY LastEffectiveDate) AS prn,
                        ROW_NUMBER() OVER (ORDER BY LastEffectiveDate) AS rn
                FROM    @tmp
                ) q
        ) q2
GROUP BY
        JobCodeId, diff
ORDER BY
        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

Problem

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 NOT null DROP TABLE #tmp CREATE TABLE #tmp ( UserID BIGINT NOT NULL, JobCodeID BIGINT NOT NULL, LastEffectiveDate DATETIME NOT NULL ) INSERT INTO #tmp VALUES ( 1, 5, '1/1/2010') INSERT INTO #tmp VALUES ( 1, 5, '1/2/2010') INSERT INTO #tmp VALUES ( 1, 6, '1/3/2010') INSERT INTO #tmp VALUES ( 1, 5, '1/4/2010') INSERT INTO #tmp VALUES ( 1, 1, '1/5/2010') INSERT INTO #tmp VALUES ( 1, 1, '1/6/2010') SELECT JobCodeID, MIN(LastEffectiveDate) FROM #tmp WHERE UserID = 1 GROUP BY JobCodeID DROP TABLE [#tmp] ``` This query will return 3 rows, with the min value. ``` 1 2010-01-05 00:00:00.000 5 2010-01-01 00:00:00.000 6 2010-01-03 00:00:00.000 ``` What I am looking for is for the group to be sequential and return more than one JobCodeID, like this: ``` 5 2010-01-01 00:00:00.000 6 2010-01-03 00:00:00.000 5 2010-01-04 00:00:00.000 1 2010-01-05 00:00:00.000 ``` Is this possible without a cursor?

Original source