Search code examples
sqlfilterreturn

SQL select, only return results where a rate or figure changes in a date and time order sequence

I am trying to return rates in a SQL database that are seperated by timebands, I only need to return the results where a rate changes from the intial timeband, for example; BH(Bank Holiday), WD(Weekday), WE(Weekend) for reference...

ClientType  Column2     Column 3
Private     BH 06:00    £22.00
Private     BH 09:00    £25.00
Private     BH 17:00    £25.00
Private     WD 06:00    £12.00
Private     WD 09:00    £14.00
Private     WD 17:00    £14.00
Private     WE 06:00    £16.00
Private     WE 09:00    £16.00
Private     WE 17:00    £16.00
Family      BH 06:00    £28.00
Family      BH 09:00    £28.00
Family      BH 17:00    £28.00
Family      WD 06:00    £12.00
Family      WD 09:00    £12.00
Family      WD 17:00    £12.00
Family      WE 06:00    £15.00
Family      WE 09:00    £15.00
Family      WE 17:00    £15.00

So trying to get the above data returned as,

ClientType  Column2     Column 3
Private     BH 06:00    £22.00
Private     BH 09:00    £25.00
Private     WD 06:00    £12.00
Private     WD 09:00    £14.00
Private     WE 06:00    £16.00
Family      BH 06:00    £28.00
Family      WD 06:00    £12.00
Family      WE 06:00    £15.00

Only the results are returned where the rate changes across the timeband sequence.

Further to that if the results only return one of each timeband for example the Family type client, then it will rename the timenband to just BH, WD & WE. For example

ClientType  Column2     Column 3
Private     BH 06:00    £22.00
Private     BH 09:00    £25.00
Private     WD 06:00    £12.00
Private     WD 09:00    £14.00
Private     WE          £16.00
Family      BH          £28.00
Family      WD          £12.00
Family      WE          £15.00

Thank you

Solution

  • I think you can do what you want with lag():

    select clienttype, column2, column3
from (select t.*,
             lag(column3) over (partition by clienttype, left(column2, 2) order by column2) as prev_column3
      from t
     ) t
where prev_column3 is null or prev_column3 <> column3;