Distinguishing between records with same code

Transact-SQL
, ,
1

Hope someone can help.

I have to populate the Class field based on the RefID field. For now, I'm only really interested in any records that are "FUNDED". Here is some sample code:

DECLARE @test AS TABLE
(
TDate VARCHAR (8),
CustID INT,
RefID VARCHAR (20),
Value DECIMAL (15, 2),
TranType VARCHAR (20),
Class VARCHAR (20)
)
INSERT INTO @test
VALUES 
(
'20160301', '32990', 'FUNDED', '1050.00', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160301', '32990', 'NEW PREMIUM', '900.00', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160301', '32990', 'TAX', '90.00', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160301', '32990', 'ARRANGEMENT FEE', '60.00', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160311', '32990', 'FUNDED', '250.00', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160311', '32990', 'ADDITIONAL PREMIUM', '175.00', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160311', '32990', 'TAX', '17.50', 'New Business', '????'
)
INSERT INTO @test
VALUES 
(
'20160311', '32990', 'ADDITIONAL FEE', '57.50', 'New Business', '????'
)
SELECT * FROM @test

The first "FUNDED" should be marked as "NBFUND" because there is an entry of "NEW PREMIUM" on the same day.

The second "FUNDED" (on a different day) doesn't contain an entry of "NEW PREMIUM" but does contain one of "ADDITIONAL PREMIUM" so this one needs to be marked as "EXTRA PREMIUM".

Does that make sense?

I'm thinking of some sort of self-join but am lacking the skillset to work out the code.

Thank you for reading.

2

This might work for you:

with cte
  as (select a.tdate
            ,a.custid
            ,case
                when sum(case when a.refid='NEW PREMIUM' then 1 else 0 end)>0
                then 'NBFUND'
                when sum(case when a.refid='ADDITIONAL PREMIUM' then 1 else 0 end)>0
                then 'EXTRA PREMIUM'
             end as [class]
        from @test as a
       where exists (select 1
                       from @test as b
                      where b.tdate=a.tdate
                        and b.custid=a.custid
                        and b.refid='FUNDED'
                    )
       group by a.tdate
               ,a.custid
     )
select a.tdate
      ,a.custid
      ,a.refid
      ,a.[value]
      ,a.trantype
      ,b.[class]
  from @test as a
       left outer join cte as b
                    on b.tdate=a.tdate
                   and b.custid=a.custid
;
1 Like