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

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  June 21, 2018 at 3:25 pm

  #1994925

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

  SELECT *
FROM @Test t_curr
INNER JOIN @Test 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• tshad

  SSCertifiable

  Points: 5905

  More actions

  June 21, 2018 at 3:43 pm

  #1994926

  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: 77013

  More actions

  June 22, 2018 at 7:21 am

  #1994967

  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

  June 22, 2018 at 11:54 pm

  #1995051

  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