Finding Breaks In Key Values

  • The following is sample data I am dealing with.

    SELECT * INTO TEMP

    FROM

    (SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X

    I need to extract the date that the value in CODE column changes to another code for each value

    of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.

    Any SQL help?

  • If you're using SQL 2012, you can use LAG:

    SELECT Category

    , Code AS CurrCode

    , LAG(Code) OVER (ORDER BY Category, StartDate) PrevCode

    , CASE WHEN LAG(Code) OVER (ORDER BY Category, StartDate)<>Code THEN 'Changed' ELSE 'Same' END

    , StartDate

    , EndDate

    FROM

    (SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE

    UNION

    SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X

    Granted, it's probably not the prettiest example, but it gives you the idea. Basically, it lets you compare values in different rows.

    Here's the LAG example from MS...

  • I have a recent SSC article on linking up rows of data to grab a previous or next value for a given column that may be of help:

    http://www.sqlservercentral.com/articles/CTE/109287/

    This has methods for both SQL Server 2012 (using LEAD/LAG) and earlier versions (using correlated subqueries)

    Hope this helps (or at least makes for some fun reading)!

  • Here is a non SQL 2012 method.This would require a row identifier column which I believe every table should have.But personally I have seen LEAD/LAG functions performing way better than the APPLY clauses for these kind of queries.

    ;WITH CTE

    AS

    (

    SELECT 1 ID,'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE

    UNION

    SELECT 2 ID,'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE

    UNION

    SELECT 3 ID,'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE

    UNION

    SELECT 4 ID,'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE

    UNION

    SELECT 5 ID,'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE)

    SELECT C1.*,T.CODE PREVIOUSCODE FROM CTE C1

    OUTER APPLY (SELECT TOP 1 CODE FROM CTE

    C2 WHERE C1.ID=C2.ID+1 AND C1.CATEGORY=C2.CATEGORY)T

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply