Calculate the latest wave name

Question

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

Looks like Sybase or SQL Server. What database are you using? Sybase, SQL Server, Oracle, PostgreSQL, DB2, etc. — The Impaler, Sep 15 '22 at 17:19
Based on your sample inputs, it looks like the number of characters is consistent. Why are you using `PATINDEX` and `CHARINDEX` rather than hard-coding the integers? If the number of characters betwen the delimiters (`_` and `.`) varies, please post better sample data -- preferably with `CREATE TABLE` and `INSERT` statements. — dougp, Sep 15 '22 at 17:43

John Cappelletti · Accepted Answer · 2022-09-15T18:17:17.877

2

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.

edited Sep 15 '22 at 18:17

answered Sep 15 '22 at 18:09

John Cappelletti

79,615
7
44
66

1

So you're splitting the value into a JSON array using `_` as the delimiter, then returning the value of element 5 or 3? Nice! Will that work in Oracle and Postgres, or is this solution limited to SQL Server? – dougp Sep 15 '22 at 18:27
@dougp I'd imagine. I suspect the syntax would be a little different. – John Cappelletti Sep 15 '22 at 18:33

score 0 · Answer 2 · answered Sep 15 '22 at 18:18

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

Calculate the latest wave name

2 Answers2