I have a table with this format

The date format is formatted like this : 1yymmdd

What I want is for each From and To couple (per example A/B) create a Begin_Date and End_Date (based on the next Begin_Date) with the Format dd.mm.yy.

I already have the Begin_date since its the value given by the user. Now, for the End_Date, I have to check if there's a Begin_date bigger than my from/to couple.

Per exemple, for the A/B :

It should gave me something like this (the result that I want at the end with all the from/to couple) :

The value End_date is 31.01.2022 because there's a bigger value for this couple (01.02.2022) and it's a day before this value.

And if there's no bigger Begin_date, I just want to write null.

Can you help me to do this ? I couldn't find a viable solution for this use case.

CodePudding user response：

You should consider storing dates in a Date Column, not in a varchar column.

Maybe this is what you are after ?

select t.[From],
       t.[To],
       convert(date, right(t.Begin_Date, 6)) as BeginDate,
       t2.BeginDate as EndDate
from   table1 t
  outer apply ( select top 1
                       dateadd(day, -1, convert(date, right(t1.Begin_Date, 6))) as BeginDate
                from   table1 t1
                where  t1.[From] = t.[From]
                and    t1.[To] = t.[To]
                and    convert(date, right(t1.Begin_Date, 6)) > convert(date, right(t.Begin_Date, 6))
                order by convert(date, right(t1.Begin_Date, 6)) 
              ) t2

Click on this link to see it in DBFiddle

If this is not exact what you want, then please try to explain in more detail

CodePudding user response：

you can use lead() or lag() to get the next / previous value and then dateadd() to subtract one day from it.

Conversion of your dateformat 1YYMMDD to date is done in the CROSS APPLY

select *,
       EndDate = dateadd(day, -1, lead(b.BeginDate) over (partition by t.[From], t.[To]
                                                            order by b.BeginDate) )
from   tbl t
       cross apply
       (
           select BeginDate = convert(date, '20'   right(t.[Begin_Date], 6))
       ) b
order by t.[From], t.[To], b.[BeginDate]

db<>fiddle demo