CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
priyanka.sarkar
priyanka.sarkar

Reputation: 26518

How to find three column values at different levels using TSQL?

I have a table as under

enter image description here

The expected output should be

enter image description here

That is the latest information for that Account should be picked.

table schema

declare @t table (transactionDate Date ,AccountId int, Address varchar(50) Null,Mobile varchar(50) null,Email varchar(50) null)
insert into @t 
select '01/31/2018', 1,null,'',null union all
select '01/30/2018',1,null,null,'[email protected]' union all
select '01/29/2018',1,null,null,null union all
select '01/28/2018',1,null,null,'[email protected]' union all
select '01/26/2018',1,'Address1',null,null union all
select '01/17/2018',2,'Address2',null,null union all
select '01/15/2018',2,null,null,null union all
select '01/14/2018',2,null,null,null union all
select '01/12/2018',2,null,null,null union all
select '01/11/2018',2,null,'345678',null union all
select '01/10/2018',2,null,null,null union all
select '01/04/2018',2,null,'67891','[email protected]' union all
select '01/03/2018',2,null,null,null union all
select '01/01/2018',2,'Adress222',null,null union all
select '01/30/2018',3,null,null,null union all
select '01/30/2018',4,'Addrss4','1234','[email protected]' union all
select '01/30/2018',5,'Addrss5',null,'[email protected]' union all
select '01/30/2018',5,null,'3456789',null

declare @loanAccounts table(AccountId int)
insert into @loanAccounts select 1 union all select 2 union all select 3 union all select 4 union all select 5

My attempt so far

;with addressCTE as(
-- for Address
Select transactionDate,AccountId,Address
from
(select Rn=ROW_NUMBER() Over(Partition by AccountId Order by transactionDate desc)
,transactionDate,AccountId,Address
from @t
where len(Address) > 0)x where x.Rn=1)

,mobileCTE as(
-- for Mobile
Select transactionDate,AccountId,Mobile
from
(select Rn=ROW_NUMBER() Over(Partition by AccountId Order by transactionDate desc)
,transactionDate,AccountId,Mobile
from @t
where len(Mobile) > 0)x where x.Rn=1)

,emailCTE as(
-- for email
Select transactionDate,AccountId,Email
from
(select Rn=ROW_NUMBER() Over(Partition by AccountId Order by transactionDate desc)
,transactionDate,AccountId,Email
from @t
where len(Email) > 0)x where x.Rn=1)

select la.AccountId, a.transactionDate,a.Address
from @loanAccounts la 
    left outer join addressCTE a on la.AccountId = a.AccountId

I am lost after that.

Upvotes: 1

Views: 55

Answers (2)

uzi
uzi

Reputation: 4146

Check this

select
    transactionDate, AccountId, isnull(iif(transactionDate = maxAddress, Address, ''), '')
    , isnull(iif(transactionDate = maxMobile, Mobile, ''), '')
    , isnull(iif(transactionDate = maxEmail, Email, ''), '')
from (
    select 
        *
        , maxAddress = max(iif(isnull(len(Address), 0) > 0, transactionDate, null)) over (partition by AccountId)
        , maxMobile = max(iif(isnull(len(Mobile), 0) > 0, transactionDate, null)) over (partition by AccountId)
        , maxEmail = max(iif(isnull(len(Email), 0) > 0, transactionDate, null)) over (partition by AccountId)
        , maxDate = max(transactionDate) over (partition by AccountId)
    from 
        @t
) t
where
    transactionDate in (maxAddress, maxMobile, maxEmail, case when maxAddress is null and maxMobile is null and maxEmail is null then maxDate end)

Upvotes: 1

sanatsathyan
sanatsathyan

Reputation: 1763

I guess this is what you are looking for,

SELECT transactionDate,AccountId,ISNULL(Address,'') Address,ISNULL(Mobile,'') Mobile,
       ISNULL(Email,'') Email 
FROM @t
GROUP BY transactionDate,AccountId,Address,Mobile,Email
HAVING  (Address is not null AND Address <> '') 
        OR (Mobile is not null AND Mobile <> '') 
        OR (Email is not null AND Email <> '')
ORDER BY AccountId

Demo

Upvotes: 1

Related Questions