Calculate the latest wave name-CodePudding

The following SQL retrieved the latest wave name based on the number suffix.

This is a wave name structure:XXX_XXX_XXXXXXXXXX.XXXX_< incremented number >

Example: XXX_XXX_XXXXXXXXXX.XXXX_1 , XXX_XXX_XXXXXXXXXX.XXXX_2, XXX_XXX_XXXXXXXXXX.XXXX_3

select  top 1 wave
from Outbound co WHERE Program = :Program
order by CAST(SUBSTRING(SUBSTRING(Wave, CHARINDEX('.', Wave)  1, len(wave)), PATINDEX('%[_]%', 
SUBSTRING(Wave, CHARINDEX('.', Wave)  1, len(wave)))  1, LEN(SUBSTRING(Wave, CHARINDEX('.', Wave)  1, len(wave)))) AS INT)  desc;

Lately, I notice that the wave name structure changed, there for the current SQL logic will not be applicable with the new format.

New format: XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX__< incremented number > _< monthyear >

Example: XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_81_042122, XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_80_032122

I would like to extend the current SQL logic to handle both scenarios in one SQL.

Any advice on how?

Thanks

CodePudding user response：

Perhaps a little cleaner approach would be to apply a bit of JSON

Declare @YourTable table (wave varchar(100))
Insert Into @YourTable values
 ('XXX_XXX_XXXXXXXXXX.XXXX_1')
,('XXX_XXX_XXXXXXXXXX.XXXX_2')
,('XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_81_042122')
,('XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_80_032122')

Select Wave
      ,Pos1 = IsNull(JSON_VALUE(JS,'$[5]'),JSON_VALUE(JS,'$[3]'))
 From  @YourTable
 Cross Apply (values ('["' replace(string_escape(wave,'json'),'_','","') '"]') ) B(JS)

Results

Note: Depending on your actual data, the string_escape() may not be necessary.

CodePudding user response：

I used common table expressions and incorporated the "nth occurrence" code provided by Alex K.

declare @Outbound table (
  Program varchar(50),
  wave varchar(50)
)

insert @Outbound
values 
  ('a', 'XXX_XXX_XXXXXXXXXX.XXXX_1')
, ('a', 'XXX_XXX_XXXXXXXXXX.XXXX_2')
, ('a', 'XXX_XXX_XXXXXXXXXX.XXXX_3')
, ('a', 'XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_81_042122')
, ('a', 'XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_80_032122')



;
with a as (
  select wave
  ,  SUBSTRING(
        Wave, 
        CHARINDEX('.', Wave)   1, 
        len(wave)
      ) as expression
  , case
    when CHARINDEX('_', Wave) = 4
        then '1'
    when CHARINDEX('_', Wave) = 5
        then '2'
    else 'unknown'
    end as 'Wave Name Structure Version'
  from @Outbound co
  
  WHERE Program = 'a'
),
T(
    wave
  , expression
  , starts
  , pos
  , [Wave Name Structure Version]
) as (
  select wave
  , expression
  , 1
  , charindex('_', expression)
  , [Wave Name Structure Version] 
  from a
  union all
  select wave
  , expression
  , pos   1
  , charindex('_', expression, pos   1)
  , [Wave Name Structure Version]
  from t
  where pos > 0
), 
b as (
  select wave
  , [Wave Name Structure Version]
  , substring(expression, starts, case when pos > 0 then pos - starts else len(expression) end) WaveNumber
  , ROW_NUMBER() over (partition by expression order by starts) as instance
  from T
)

select top 1 wave

from b

where instance = 
  case
    when [Wave Name Structure Version] = '1'
      then 2
    when [Wave Name Structure Version] = '2'
      then 4
  end

order by WaveNumber desc