SQL Server - recursive references (loop, join, insert?) -

i appreciate if give me hints regarding fastest solution of following sql server challenge:

let's have table date, client , several characteristics in other columns. need calculate column_1 , column_2 but:

• column_1 uses client's characteristics of current date , of previous date , column_1 value previous date (recursive referencing)
• column_2 additionally uses column_1 value of current date (therefore refer final value, not particular 'case when' implements column logic)

how replicate logic efficiently in sql server?

i thinking loop goes on data , each data, joins previous data, calculates firstly column_1, column_2 (but how make sure values in column_1 accessible column_2?)

regards, bart

without specific example not able tell solution efficient, when looking solution describe recursive. might not need full recursive solution if use window functions instead.

in sql server 2012+ have access lead() , lag() can use previous , next values column based on partition , order.

select      client   , date    , nextdate = lead(date) on (partition client order date)   , prevdate = lag(date)  on (partition client order date)   , column1 = 'do stuff lead/lag'   , column2 = 'do stuff lead/lag' t 

rextester example: http://rextester.com/ffhu71709

returns:

+--------+------------+------------+------------+------------------------+------------------------+ | client |    date    |  nextdate  |  prevdate  |        column1         |        column2         | +--------+------------+------------+------------+------------------------+------------------------+ |      1 | 2017-01-01 | 2017-01-02 | null       | stuff lead/lag | stuff lead/lag | |      1 | 2017-01-02 | 2017-01-03 | 2017-01-01 | stuff lead/lag | stuff lead/lag | |      1 | 2017-01-03 | null       | 2017-01-02 | stuff lead/lag | stuff lead/lag | |      2 | 2017-01-02 | 2017-01-04 | null       | stuff lead/lag | stuff lead/lag | |      2 | 2017-01-04 | 2017-01-06 | 2017-01-02 | stuff lead/lag | stuff lead/lag | |      2 | 2017-01-06 | null       | 2017-01-04 | stuff lead/lag | stuff lead/lag | +--------+------------+------------+------------+------------------------+------------------------+ 

---

one way simulate lead/lag prior sql server 2012 outer apply()

select      client   , date   , nextdate   , prevdate   , column1 = 'do stuff lead/lag'   , column2 = 'do stuff lead/lag' t   outer apply (     select top 1 nextdate = i.date     t     i.client = t.client       , i.date > t.date     order i.date asc   ) n   outer apply (     select top 1 prevdate = i.date     t     i.client = t.client       , i.date < t.date     order i.date desc   ) p 

rextester demo: http://rextester.com/ggs1299

returns:

+--------+------------+------------+------------+---------------------------------+---------------------------------+ | client |    date    |  nextdate  |  prevdate  |             column1             |             column2             | +--------+------------+------------+------------+---------------------------------+---------------------------------+ |      1 | 2017-01-01 | 2017-01-02 | null       | stuff nextdate/prevdate | stuff nextdate/prevdate | |      1 | 2017-01-02 | 2017-01-03 | 2017-01-01 | stuff nextdate/prevdate | stuff nextdate/prevdate | |      1 | 2017-01-03 | null       | 2017-01-02 | stuff nextdate/prevdate | stuff nextdate/prevdate | |      2 | 2017-01-02 | 2017-01-04 | null       | stuff nextdate/prevdate | stuff nextdate/prevdate | |      2 | 2017-01-04 | 2017-01-06 | 2017-01-02 | stuff nextdate/prevdate | stuff nextdate/prevdate | |      2 | 2017-01-06 | null       | 2017-01-04 | stuff nextdate/prevdate | stuff nextdate/prevdate | +--------+------------+------------+------------+---------------------------------+---------------------------------+ 

---

for solutions absolutely require recursion, need use recursive cte.

;with cte (   -- non recursive cte add `nextdate` recursive join   select        t.client     , t.date     , nextdate = x.date   t     outer apply (       select top 1 i.date       t       i.client = t.client         , i.date > t.date       order i.date asc     ) x ) , r_cte (   --anchor rows / starting rows   select        client     , date     , nextdate     , prevdate = convert(date, null)     , column1  = convert(varchar(64),null)     , column2  = convert(varchar(64),null)   cte t   not exists (     select 1     cte     i.client = t.client       , i.date   < t.date     )    union    --recursion starts here   select        c.client     , c.date     , c.nextdate     , prevdate = p.date     , column1 = convert(varchar(64),'do recursive stuff p.column1')     , column2 = convert(varchar(64),'do recursive stuff p.column2')   cte c     inner join r_cte p       on c.client = p.client      , c.date   = p.nextdate ) select * r_cte 

rextester demo: http://rextester.com/lkh38243

returns:

+--------+------------+------------+------------+-----------------------------------+-----------------------------------+ | client |    date    |  nextdate  |  prevdate  |              column1              |              column2              | +--------+------------+------------+------------+-----------------------------------+-----------------------------------+ |      1 | 2017-01-01 | 2017-01-02 | null       | null                              | null                              | |      2 | 2017-01-02 | 2017-01-04 | null       | null                              | null                              | |      2 | 2017-01-04 | 2017-01-06 | 2017-01-02 | recursive stuff p.column1 | recursive stuff p.column2 | |      2 | 2017-01-06 | null       | 2017-01-04 | recursive stuff p.column1 | recursive stuff p.column2 | |      1 | 2017-01-02 | 2017-01-03 | 2017-01-01 | recursive stuff p.column1 | recursive stuff p.column2 | |      1 | 2017-01-03 | null       | 2017-01-02 | recursive stuff p.column1 | recursive stuff p.column2 | +--------+------------+------------+------------+-----------------------------------+-----------------------------------+ 

reference

• recursive queries using common table expressions (cte)