The overall intent is to consolidate individual sales transactions to total debits/credits summing up the totals by year and month--retaining a running balance from start to end. This code uses multiple #temp tables. I am interested in having this code reviewed in order to find a more efficient way to achieve this.The create table and insert statements are for the convenience of the code reviewer, should s/he choose to execute the code.CREATE TABLE SumTest(    POSDate datetime NULL,    debit money NULL,    credit money NULL,    RollingBalance money NULL,    expires datetime NULL,    ExpiringDebitBalance_2013 money,    ExpiringDebitBalance_2014 money);-- mimic actual sales transactionsinsert into sumtest (POSDate,debit,credit,expires) values ('Jan  5 2013 12:00AM','670.00','22.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Jan  6 2013 12:00AM','821.00','0.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Mar  8 2013 12:00AM','62.00','700.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Mar 11 2013 12:00AM','78.00','29.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Mar 11 2013 12:00AM','900.00','87.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Apr 16 2013 12:00AM','0.00','440.00',NULL);insert into sumtest (POSDate,debit,credit,expires) values ('Aug 18 2013 12:00AM','0.00','50.00',NULL);insert into sumtest (POSDate,debit,credit,expires) values ('Aug 19 2013 12:00AM','470.00','200.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Dec 31 2012 12:00AM','1000.00','200.00','Dec 31 2013 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Dec 22 2013 12:00AM','200.00','0.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Dec 20 2013 12:00AM','500.00','0.00','Dec 31 2014 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Nov 10 2012 12:00AM','200.00','0.00','Dec 31 2013 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Nov 11 2012 12:00AM','150.00','0.00','Dec 31 2013 12:00AM');insert into sumtest (POSDate,debit,credit,expires) values ('Nov 15 2012 12:00AM','0.00','100.00',NULL);create table #work(       POSYear int NULL,    POSMonth int NULL,    debit money NULL,    credit money NULL,    RollingBalance money NULL,    expires datetime)-- start with ditching the timeinsert into #work    (POSYear,POSMonth,debit,credit,expires)select datepart(Year, POSDate) as POSYear      ,datepart(Month, POSDate) as POSMonth      ,debit      ,credit      ,expiresfrom sumtest-- dump an ordered set by year,month with a rolling balance of all points/redemptions;with distilled as  (select POSYear,          POSMonth,          TotalDebit = sum(debit),          TotalCredit = sum(credit),          expires   from #work   group by POSYear,POSMonth,expires   )select POSYear,       POSMonth,       TotalDebit,       TotalCredit,       RollingBalance =  sum(totaldebit + (totalcredit)*-1) over ( order by POSYear, POSMonth),       expiresinto #work2from distilledorder by POSYear,POSMonth,TotalDebit,TotalCredit-- filter out credits lines that dont have an expiration date select x.POSYear,       x.POSMonth,       sum(x.TotalDebit) as TotalDebit,       sum(x.TotalCredit) as TotalCredit,       x.RollingBalance,       EXPYear = datepart(Year, y.expires),       EXPMonth = datepart(Month, y.expires)into #work3from #work2 x    inner join #work2 y on x.posyear = y.posyear                 and x.posmonth = y.posmonthwhere y.expires is not nullgroup by x.posyear,x.posmonth,x.rollingbalance,y.expiresorder by posyear,posmonth-- add back in lines that are single credits, w/o debitsINSERT INTO #work3    (POSYear,POSMonth,TotalDebit,TotalCredit,RollingBalance,EXPYear,EXPMonth)select     POSYear,    POSMonth,    TotalDebit,    TotalCredit,    RollingBalance,case     when expires is null then (POSYear + 1)end as EXPYear,case     when expires is null then 12end as EXPMonthfrom #work2where (POSYear + POSMonth) NOT IN (           select POSYear + POSMonth            from #work3)
Maintaining a total balance of debit/credit sales transactions
sql;sql server;t sql;finance