I have a table Address with SeqNum and I want to pick address the lowest seqnum as MainAdress. If customer has more than address pick the next to be ScondaryAddress. if street matched with the previous street then skip it and move next seqnum.

My query is picking the wrong street if the customer has more than one address.

;with base AS 
(
    select SEQNUM,
         STREET AS MainAdress,
         --ROW_NUMBER() OVER(partition by street ORDER BY SEQNUM ASC) AS ROW,
   *
    FROM ADDRESS WHERE seqnum =1
   )
,ADDRESS AS
(
    SELECT 
           STREET AS SecondaryAdress,
* 
FROM BASE WHERE seqnum =2
)

But this not dynamically getting the street

CodePudding user response：

I think this is the query you asked for:

with base as
(
  select 
    ROW_NUMBER() over (partition by cus_id order by min(seqnum)) as rn,
    cus_id, min(seqnum) as seqnum, street, city
  from Address
  group by cus_id, street, city
)
select cus_id, max(MainAddress) MainAddress, max(SecondaryAddress) SecondaryAddress, city from (
  select 
    cus_id,
    case when rn = 1 then street  end MainAddress,
    case when rn = 2 then street  end SecondaryAddress,
    city
  from base
) as a
group by cus_id, city
having max(MainAddress) is not null or max(SecondaryAddress) is not null
order by cus_id

It returns this:

DBFiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b1b7caf84a374f2f387012e08fe49b59

CodePudding user response：

declare @table table (cus_id int, seqnum int, street varchar(10), city varchar (10));

insert into @table 
values
    (123, 4, '2 Main',  'NYC'   ),
    (512, 2, '5 Elm' ,  'LA'    ),
    (512, 1, '5 Elm' ,  'LA'    ),
    (423, 3, '7 Wes' ,  'Paris' ),
    (512, 3, '4 Nav' ,  'LA'    ),
    (512, 4, '6 Lane',  'WA'    );

with base as 
(
    select
        cus_id,
        seqnum,
        main_address        = concat(street, ' ', city),
        cnt                 = count(seqnum) over(partition by cus_id),
        secondary_address   = lead(concat(street, ' ', city), 1) over(partition by cus_id order by seqnum) 
    from
        @table
)
select top 1 with ties
    cus_id,
    main_address,
    secondary_address   
from
    base
where
    cnt = 1 or main_address != secondary_address
order by
    row_number() over(partition by cus_id order by seqnum);

SQL Fiddle