November 2, 2018 at 12:37 pm
Hi All, create table #pick
(
Member_id INT,
ApplicationID VARCHAR(30),
Product varchar(5),
EffDate Date,
TermDate Date,
AppRecDate Date
)
insert into #pick
select '200394', '2-16461538', 'A100', '1/1/2017', '7/31/2018', '12/18/2016'
union all
select '200394', '2-16951248', 'A200', '8/1/2018', NULL, '07/02/2018'
union all
select '200436', '2-93784709', 'A300', '1/1/2018', '7/31/2018', '11/16/2017'
union all
select '200436', '2-94897342', 'A100', '8/1/2018', NULL, '07/05/2018'
drop table #pick
select * from #pick
I am trying to flag "Y" if there is a change is product, I tried window functions, but could not figure it out. Any input is appreciated.
Thank you!
November 2, 2018 at 2:02 pm
Try:DECLARE @pick table (
Member_id int NOT NULL
, ApplicationID varchar(30) NOT NULL
, Product varchar(5) NOT NULL
, EffDate date NOT NULL
, TermDate date NULL
, AppRecDate date NOT NULL);
INSERT @pick (Member_id, ApplicationID, Product, EffDate, TermDate, AppRecDate)
VALUES
('200394', '2-16461538', 'A100', '20170101', '20180731', '20161218')
, ('200394', '2-16951248', 'A200', '20180801', NULL, '20180702')
, ('200436', '2-93784709', 'A300', '20180101', '20180731', '20171116')
, ('200436', '2-94897342', 'A100', '20180801', NULL, '20180705') ;
WITH t
AS
( SELECT
t.Member_id
, t.ApplicationID
, t.Product
, t.EffDate
, t.TermDate
, t.AppRecDate
, row_number() OVER (PARTITION BY t.Product ORDER BY t.EffDate) r
, Binary_Checksum(
t.Member_id
, t.ApplicationID
, t.Product
, t.EffDate
, t.TermDate
, t.AppRecDate) Bc
FROM
@pick t
)
SELECT
t.Member_id
, t.ApplicationID
, t.Product
, t.EffDate
, t.TermDate
, t.AppRecDate
, CASE WHEN t.Bc <> t0.Bc THEN 'Y' ELSE 'N' END Flag
FROM
t t
LEFT JOIN
t t0 ON t0.Product = t.Product
AND t.r = t0.r+1
ORDER BY t.EffDate;
November 3, 2018 at 10:26 pm
Thank you Joe for your time and solution! there is a little change in the requirement and it added more complexity.
create table #pick
(
Member_id INT,
ApplicationID VARCHAR(30),
Product varchar(5),
EffDate Date,
TermDate Date,
AppRecDate Date
)
insert into #pick
select '200394', '2-16461202', 'A100', '1/1/2014', '12/31/2016', '12/06/2013'
union all
select '200394', '2-16461538', 'A100', '1/1/2017', '7/31/2018', '12/18/2016'
union all
select '200394', '2-16951248', 'A200', '8/1/2018', NULL, '07/02/2018' -- Only recent two records should be considered based on the term date.
union all
select '200436', '2-93784709', 'A300', '1/1/2018', '7/31/2018', '11/16/2017'
union all
select '200436', '2-94897342', 'A100', '8/1/2018', NULL, '07/05/2018'
union all
select '200900', '2-93784000', 'A300', '1/1/2018', '12/31/2018', '11/10/2017'
union all
select '200900', '2-94897783', 'B100', '1/1/2019', NULL, '07/05/2018' --Can have entirely different product.
drop table #pick
select * from #pick
If you can add some details on how the code works, will help me understanding and learning.
Thank you! appreciate all the help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply