Using Case in a Where Clause.

• bubs

  SSCommitted

  Points: 1620

  More actions

  April 10, 2017 at 9:44 pm

  #324348

  Hello,

  Using the following example tables and query:

  create table AccountTable (AcctNum int primary key, column2 varchar(50));
  insert into AccountTable (AcctNum, column2) VALUES (123, 'Test');

  create table AccountCycleTable (AcctNum int, EffDate Date not null, InactiveDate Date, constraint fk_acctnbr foreign key (AcctNum) References AccountTable(AcctNum));
  insert into AccountCycleTable (AcctNum, EffDate, InactiveDate) VALUES (123, '01-JAN-2017', '15-JAN-2017');
  insert into AccountCycleTable (AcctNum, EffDate, InactiveDate) VALUES (123, '01-FEB-2017', '28-FEB-2017');
  insert into AccountCycleTable (AcctNum, EffDate, InactiveDate) VALUES (123, '01-FEB-2017', null); 

  begin
  declare @postDate date set @postDate = '15-FEB-2017'
  select A.AcctNum, B.EFFDATE, B.INACTIVEDATE
  from
  AccountTable A
  LEFT JOIN AccountCycleTable B ON A.AcctNum = B.AcctNum AND
  CASE
  when (EFFDATE <= @postDate AND INACTIVEDATE > @postDate) then 1
  when (inactivedate is null) then 1
  end = 1
  where A.AcctNum in (123, 456, 789);
  end

  returns the following:
  AcctNum......EFFDATE..........INACTIVEDATE
  123..............2017-02-01.........2017-02-28
  123..............2017-02-01.........null

  The requirements are to return the AcctNum where the InactiveDate is null. However, if a row exists where the postdate falls between the EffDate and InactiveDate then return that row instead.
  This small example piece is from a much larger query, but this is the part that's giving me grief.
  Any suggestions?

  Thanks in advance.

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  April 10, 2017 at 10:36 pm

  #1937537

  A small tweak to your code will get you what you are looking for

  
SELECT
  data.AcctNum
, data.EffDate
, data.InactiveDate
FROM (
  SELECT A.AcctNum, B.EFFDATE, B.INACTIVEDATE
   , rn = ROW_NUMBER() OVER (PARTITION BY A.AcctNum
            ORDER BY ISNULL(INACTIVEDATE, '1900-01-01') DESC)
  FROM AccountTable AS A
  LEFT JOIN AccountCycleTable AS B
   ON A.AcctNum = B.AcctNum
   AND CASE
     WHEN (EFFDATE <= @postDate AND INACTIVEDATE > @postDate) THEN 1
     WHEN (inactivedate IS NULL) THEN 1
    END = 1
  WHERE A.AcctNum in (123, 456, 789)
) AS data
WHERE rn = 1;


  ---

  How to post data/code on a forum to get the best help.

• bubs

  SSCommitted

  Points: 1620

  More actions

  April 11, 2017 at 12:13 pm

  #1937696

  Thanks DesNorton, that works great!

  Much appreciated!!