April 10, 2017 at 9:44 pm
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.
April 10, 2017 at 10:36 pm
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;
April 11, 2017 at 12:13 pm
Thanks DesNorton, that works great!
Much appreciated!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply