Maintaining a total balance of debit/credit sales transactions

Question

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 transactions

insert 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 time

insert into #work
    (POSYear,POSMonth,debit,credit,expires)
select datepart(Year, POSDate) as POSYear
      ,datepart(Month, POSDate) as POSMonth
      ,debit
      ,credit
      ,expires
from 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),
       expires
into #work2
from distilled
order 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 #work3
from #work2 x
    inner join #work2 y on x.posyear = y.posyear
                 and x.posmonth = y.posmonth
where y.expires is not null
group by x.posyear,x.posmonth,x.rollingbalance,y.expires
order by posyear,posmonth

-- add back in lines that are single credits, w/o debits

INSERT 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 12
end as EXPMonth
from #work2
where (POSYear + POSMonth) NOT IN (
           select POSYear + POSMonth
            from #work3)

Answer 1

You should always make sure nothing breaks if you accidentally execute your script twice.

Your script should begin with verifying whether table SumTest exists (and DROP it if it does), before creating it.

Temporary tables #work, #work2 and #work3 are never dropped. Not a biggie, but if you run the script twice you'll get an error saying there is already an object named '#work' in the database. Dropping your #temp tables is a good habit to take.

As for the naming, well, @Malachi's answer says one thing, I'll say another: be consistent. it's all that matters. - if you drag-and-drop columns and tables from the SSMS object explorer into your query, then by all means stick to UPPERCASE. Personally I find uppercase SQL is annoying to read, it's like all those keywords are jumping at you. Do what you will. I mean, chose whether you prefer UPPERCASE or lowercase, and stick to your plan.

Answer 2

it is good Coding Practice to Capitalize all of the Reserved SQL words so that it is easier to tell them apart.

words like SELECT, FROM, WHERE, WITH, INTO, GROUP BY, ORDER BY, CREATE TABLE, INSERT INTO, END, CASE, AS, IS NULL, THEN

I know this is Kind of Nitpicking, but you should get into the habit of doing it, even though SQL is not Case Sensitive on these.

I know this isn't the review that you were looking for, but it seems you have an issue that you need to fix before we can review this code