SQL: Increment ID only for new rows based on the count

Requirement: Generate new ID from the MAX ID for those Name doesn’t exist in the Target table and has count >1

Below is the Source data, The yellow highlighted are new rows, Those with count >1 are incremented with a new ID, and those with count =1 defaults to FM00000001

The expected result is highlighted in yellow in the Target table

I have generated the existing ID manually for one time , as I have to automate daily jobs so I need to generate incremental ID from MAX ID for those count >1

   with src as (
            select '121 MEDICAL PLACE'      as Label ,  1 as cnt 
            union all 
            select '16TH STREET COMMUNITY'  as Label ,  1 as cnt  
            union all 
            select '19TH AVENUE CLINIC'     as Label ,  2 as cnt
            union all 
            select '1ST CLASS URGENT CARE'  as Label ,  3 as cnt 
            union all 
            select '160 DORADO BCH'         as Label ,  2 as cnt 
            union all 
            select 'APPLETREE LN'           as Label ,  4 as cnt 
            union all 
            select 'KNOLLWOOD LN'           as Label ,  1 as cnt
)
select * from src 

with tgt as (
            select '121 MEDICAL PLACE'      as Label ,  'FM00000001' as ID 
            union all 
            select '16TH STREET COMMUNITY'  as Label ,  'FM00000001'as ID 
            union all 
            select '19TH AVENUE CLINIC'     as Label ,  'FM00000002'as ID 
            union all 
            select '1ST CLASS URGENT CARE'  as Label ,  'FM00000003' as ID 
)
select * from tgt

Answer

ok If I understand correctly , here is how you can do it :

select * 
   , 'FM'+ LPAD(case when cnt =1 then cnt else count(case when cnt > 1 then 1 end) over (order by OrderColumn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 end, 8,0) as ID
from table

for reference: Window Functions

Advertisement

Answer