Finding Breaks In Key Values

Question

Post reply

Finding Breaks In Key Values

Nilssond

Hall of Fame

Points: 3285
More actions
June 12, 2014 at 10:52 am

#396905

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?

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

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

pietlinden SSC Guru Points: 63509 More actions · Answer 1

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...

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 2

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)!

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

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