[dba-SQLServer] Looping through queries over and over

[David McAfee]

So I have to come up with a report that shows an ongoing loss of insurance.

I would really like to do this on the fly.

Each week, we process a batch of records and those are stored with their
batch name.
Sample batch names:
Month Begin 20150105
Month Week2 20150112
Month Week3 20150120
Month End 20150126
Month Begin 20150203
Month Week2 20150209
Month Week3 20150216
Month End 20150223



Starting in Week2, I compare Week 2 to Week 1 (Month Begin) where
Week1.Insuranceflag =1 and Week2.Insuranceflag =0.

Easy.

On week 3, I compare week 3 vs wk 2 loss (new loss) plus any losses from
the previous week that are still 0.

I wrote a stored procedure where I pass it the batch names:

EXEC stpLossOfMediCalDetail 'Month Begin 20150105','Month Week2 20150112'

I can insert those results into a table variable, but I'm thinking I need
two temp tables.

One to hold the cumulative (new loss) records and another to temporarily
hold the  results
comparing week 2 to week 1 then left join those back to the cumulative
table and delete any records that don't exist in both tables. Then do that
over and over.


I don't know how my little text grid will display after being emailed:

Month1           Month2
W1 W2 W3 W4 W5 W6 W7 W8
 1    0    0    0    0    0    0    0
       1    0    0    0    0    0    0
             1    0    0    0    0    0
                   1    0    0    0    0
                         1    0    0    0
                               1    0    0
                                     1    0


Am I over complicating this?  :)

I hope I don't scare any Non SQL folk off, I could do this with VBA &
Queries instead of Cursors and TSQL. I'm just trying to think of a good way
to do this.