Find differences without cursor

Question

Find differences without cursor

tshad

SSCertifiable

Points: 5905
More actions
June 21, 2018 at 2:48 pm

#406758

I have a situation where I need to get changes in statuses for the current month and the previous month.
I can do that fine.
But without a cursor, what would be the best (or good) way to give me a list of changes from the beginning of the year.
DECLARE @test-2 TABLE ( EmpName varchar(50), EmpStatus VARCHAR(1), Job VARCHAR(10), YearMonth int )
DECLARE @CurrentYearMonth INT
SET @CurrentYearMonth = 201805
INSERT @test-2 Values('John', 'A', 'P', 201801)
INSERT @test-2 Values('John', 'L', 'P', 201802)
INSERT @test-2 Values('John', 'L', 'F', 201803)
INSERT @test-2 Values('John', 'A', 'P', 201804)
INSERT @test-2 Values('John', 'A', 'F', 201805)
INSERT @test-2 Values('Mary', 'A', 'F', 201801)
INSERT @test-2 Values('Mary', 'A', 'F', 201802)
INSERT @test-2 Values('Mary', 'T', 'F', 201803)
INSERT @test-2 Values('Mary', 'T', 'P', 201804)
INSERT @test-2 Values('Mary', 'A', 'F', 201805)
SELECT * FROM @test-2
SELECT *
FROM @test-2 t1
JOIN @test-2 t2
ON t1.EmpName = t2.EmpName
AND t2.YearMonth = @CurrentYearMonth - 1
WHERE t1.YearMonth = @CurrentYearMonth AND
((t1.EmpStatus <> t2.EmpStatus) OR
(t1.Job <> t2.job))
This gets me this result:
This gets me the results between April and May but how about any changes between January and May.
Thanks,
Tom

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

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

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

DECLARE @CurrentYearStart INT DECLARE @CurrentYearEnd INT SET @CurrentYearStart = 201801 SET @CurrentYearEnd = 201805

SELECT * FROM @test-2 t_curr INNER JOIN @test-2 t_prev ON t_prev.EmpName = t_curr.EmpName AND t_prev.YearMonth = t_curr.YearMonth - 1 AND (t_prev.EmpStatus <> t_curr.EmpStatus OR t_prev.Job <> t_curr.Job) WHERE t_curr.YearMonth BETWEEN @CurrentYearStart AND @CurrentYearEnd ORDER BY t_curr.YearMonth, t_curr.EmpName

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

tshad SSCertifiable Points: 5905 More actions · Answer 2

Great.
I also made a small change that lets me select the range. If @Choice is NULL, it will work as you have it. @Choice is the number of years back to look. If you have it set to 2, the results are:

The changed code is:
DECLARE @CurrentYearStart INT DECLARE @CurrentYearEnd INT DECLARE @Choice INT SET @CurrentYearStart = 201801 SET @CurrentYearEnd = 201805 SET @Choice = 2

SELECT *
FROM @test-2 t_curr
INNER JOIN @test-2 t_prev
ON t_prev.EmpName = t_curr.EmpName
AND t_prev.YearMonth = CASE WHEN @Choice = 1 THEN @CurrentYearEnd ELSE t_curr.YearMonth END - 1
AND (t_prev.EmpStatus <> t_curr.EmpStatus OR t_prev.Job <> t_curr.Job)
WHERE t_curr.YearMonth BETWEEN CASE WHEN @Choice IS NULL THEN @CurrentYearStart ELSE @CurrentYearEnd - @Choice END AND @CurrentYearEnd
ORDER BY t_curr.YearMonth, t_curr.EmpName

Thanks.

drew.allen SSC Guru Points: 76944 More actions · Answer 3

You need to be careful here, because the date arithmetic isn't automatically applied correctly. Specifically, 201801 - 1 is 201800 instead of 201712. This is part of the reason that it's a bad idea to store dates in non-date(time) fields.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

tshad SSCertifiable Points: 5905 More actions · Answer 4

Agreed.

This was just a sample of the actual code which is a bit more complicated and I do convert it to actual dates.

Thanks