Alternative function for LAG in SQL

Question

I know this question has been asked a million times and I know what functions to use but I don't know how to put it together. I am new to SQL, been working on it for 2 months with no formal training. I have a table (see below), I need to group it by acountid and populate the TaxDiff column by subtracting the previous year's normalTax amount from the subsequent year. I figured it out using the LAG function but soon discovered that I was using an old version of SQL. I know I need to use the Row Partition function but I am not sure how to put it together. Do any of you perhaps know how to go about doing this? I am using SQL server 2008 (cannot upgrade as this decision is not up to me) and my table is much bigger than this, a few thousand lines.

AccountId	Year	NormalTax	Date	SeqNo	TaxDiff
9000156	2019	15293056	2019-10-25	3	NULL
9000156	2018	118592	2018-11-30	1	NULL
9000611	2015	1000000	2015-10-30	1	NULL
9000611	2014	750000	2014-12-31	4	NULL
9000611	2013	659923	2014-01-30	2	NULL
9001230	2020	50000000	2020-06-25	1	NULL
9001230	2019	1500000	2019-12-31	1	NULL
9001230	2018	1750000	2018-09-25	3	NULL
9001230	2017	15000000	2017-06-30	2	NULL
9001230	2016	12500000	2017-02-20	2	NULL

Answer 1

You could use a correlated sub query to figure this out. So assuming year is a synonym for tax year

drop table t
go
create  table t
(AccountId  int, Year int,  NormalTax int,  Date date,  SeqNo int,  TaxDiff int)
go
insert into t values
(9000156    ,2019   ,15293056   ,'2019-10-25'   ,3  ,null),
(9000156    ,2018   ,118592     ,'2018-11-30'   ,1  ,null),
(9000611    ,2015   ,1000000    ,'2015-10-30'   ,1  ,null),
(9000611    ,2014   ,750000     ,'2014-12-31'   ,4  ,null),
(9000611    ,2013   ,659923     ,'2014-01-30'   ,2  ,null),
(9001230    ,2020   ,50000000   ,'2020-06-25'   ,1  ,null),
(9001230    ,2019   ,1500000    ,'2019-12-31'   ,1  ,null),
(9001230    ,2018   ,1750000    ,'2018-09-25'   ,3  ,null),
(9001230    ,2017   ,15000000   ,'2017-06-30'   ,2  ,null),
(9001230    ,2016   ,12500000   ,'2017-02-20'   ,2  ,null)

select  AccountId   , Year, NormalTax,  Date,   SeqNo,
        (select top 1 normaltax 
        from t t1 
        where t1.accountid = t.accountid and t1.year < t.year order by t1.year desc) lastyear ,
        coalesce(
        t.normaltax - 
        (select top 1 normaltax 
        from t t1 
        where t1.accountid = t.accountid and t1.year < t.year order by t1.year desc),
        normaltax) taxdiff   
from t
order by t.accountid,t.year

AccountId   Year        NormalTax   Date       SeqNo       lastyear    taxdiff
----------- ----------- ----------- ---------- ----------- ----------- -----------
9000156     2018        118592      2018-11-30 1           NULL        118592
9000156     2019        15293056    2019-10-25 3           118592      15174464
9000611     2013        659923      2014-01-30 2           NULL        659923
9000611     2014        750000      2014-12-31 4           659923      90077
9000611     2015        1000000     2015-10-30 1           750000      250000
9001230     2016        12500000    2017-02-20 2           NULL        12500000
9001230     2017        15000000    2017-06-30 2           12500000    2500000
9001230     2018        1750000     2018-09-25 3           15000000    -13250000
9001230     2019        1500000     2019-12-31 1           1750000     -250000
9001230     2020        50000000    2020-06-25 1           1500000     48500000

(10 row(s) affected)

NOTE I cannot remember when coalesce came in for the handling of nulls You may need to change a bit. You can also test this in a fiddle of your choice..

Answer 2

Could you please start with something like that:

WITH  CTE(AccountId,CalendarYear,NormalTax,Date,SeqNo,TaxDiff) AS
 (

     select 9000156,    2019,   15293056,   '2019-10-25',   3,  NULL
          UNION ALL
      SELECT 9000156 ,  2018,   118592,     '2018-11-30' ,  1,  NULL
          UNION ALL
      SELECT 9000611 ,  2015 ,  1000000,    '2015-10-30',   1 , NULL
          UNION ALL
      SELECT 9000611 ,  2014,   750000 ,    '2014-12-31',   4,  NULL
          UNION ALL
     SELECT 9000611 ,   2013 ,  659923,     '2014-01-30',   2 , NULL
          UNION ALL
     SELECT 9001230 ,   2020 ,  50000000 ,  '2020-06-25',   1,  NULL
          UNION ALL
     SELECT 9001230 ,   2019 ,  1500000 ,   '2019-12-31',   1,  NULL
           UNION ALL
     SELECT 9001230 ,   2018 ,  1750000,    '2018-09-25',   3,  NULL
           UNION ALL
     SELECT 9001230 ,   2017 ,  15000000,   '2017-06-30' ,  2 , NULL
           UNION ALL
     SELECT 9001230,    2016,   12500000,   '2017-02-20',   2,  NULL
 )
 SELECT C.*,X.PREV_TAX,C.NormalTax-X.PREV_TAX AS DIFF
   FROM CTE AS C
   OUTER APPLY
    (
       SELECT NORMALTAX AS PREV_TAX FROM CTE AS C2 WHERE C.AccountId=C2.AccountId
          AND C.CalendarYear=C2.CalendarYear+1
    )X
 ORDER BY C.AccountId,C.CalendarYear