[dba-SQLServer] Looping through queries over and over

[fhtapia at gmail.com]

Hey Dave,
  it sounds like what you are fishing for a sql server analysis cube.
 since you are loading data each week by batch, that data should be
processed against your analysis cube so you can run the type of reports you
wish to build.

not only that you can easily convert from day > week > quarter and finally
annually.  this then provides the business with a quick snapshot of what
the patterns are.

if you haven't gotten into building cubes, you can start with this walk
through:
http://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv

or
http://www.sqlshack.com/sql-server-business-intelligence-features-olap-cube-creating/

regards,
Francisco


On Fri, May 15, 2015 at 1:02 PM David McAfee <davidmcafee at gmail.com> wrote:

> Ok, I was able to do this in SSMS with hardcoded values, but I'd like to
> make it dynamic.
>
> Maybe a better question would be:
>
> Given a select query or temp table of these batchnames:
> Month Begin 20141202
> Month Begin 20150106
> Month Begin 20150203
> Month Begin 20150302
> Month Begin 20150403
> Month Begin 20150501
>
> How can I dynamically come up with a loop or fill a temp table to look like
> this:
> Step tableABatch1       tableABatch2  tableTBatch1       tableTBatch2
> 1 Month Begin 20141202 Month Begin 20150106 Month Begin 20141202 Month
> Begin 20150203
> 2 NULL NULL Month Begin 20141202 Month Begin 20150302
> 3 NULL NULL Month Begin 20141202 Month Begin 20150403
> 4 NULL NULL Month Begin 20141202 Month Begin 20150501
> 5 Month Begin 20150106 Month Begin 20150203 Month Begin 20150106 Month
> Begin 20150302
> 6 NULL NULL Month Begin 20150106 Month Begin 20150403
> 7 NULL NULL Month Begin 20150106 Month Begin 20150501
> 8 Month Begin 20150203 Month Begin 20150302 Month Begin 20150203 Month
> Begin 20150403
> 9 NULL NULL Month Begin 20150203 Month Begin 20150501
> 10 Month Begin 20150302 Month Begin 20150403 Month Begin 20150302 Month
> Begin 20150501
> 11 Month Begin 20150403 Month Begin 20150501 NULL NULL
>
>
> The reason that I ask is because this is what I ended up doing (manually):
> for steps 1-4, above
> The big delete statements below are identical
>
> DECLARE @TempTableA TABLE(fields....) --Cumulative table
> DECLARE @TempTableT TABLE(fields....) --temporary temp table :)
>
> INSERT INTO @TempTableA EXEC stpLossOfMediCalDetail 'Month Begin
> 20141202','Month Begin 20150106'  --Step 1 above
> INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
> 'Month Begin 20150203' --Step 1 above
>
> DELETE @TempTableA
>  FROM @TempTableA B
> LEFT JOIN
> (SELECT A.*
> FROM @TempTableA A
> INNER JOIN @TempTableT T
> ON A.EligNumber =T.EligNumber
> AND A.AccountNumb = T.AccountNumb
> AND A.FirstDate = T.FirstDate) C
> ON B.EligNumber =C.EligNumber
> AND B.AccountNumb = C.AccountNumb
> AND B.FirstDate = C.FirstDate
> WHERE C.AccountNumb IS NULL
> AND C.FirstDate IS NULL
> AND C.EligNumber IS NULL
>
> DELETE FROM  @TempTableT
>
> INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
> 'Month Begin 20150302' --Step2
>
> DELETE @TempTableA
>  FROM @TempTableA B
> LEFT JOIN
> (SELECT A.*
> FROM @TempTableA A
> INNER JOIN @TempTableT T
> ON A.EligNumber =T.EligNumber
> AND A.AccountNumb = T.AccountNumb
> AND A.FirstDate = T.FirstDate) C
> ON B.EligNumber =C.EligNumber
> AND B.AccountNumb = C.AccountNumb
> AND B.FirstDate = C.FirstDate
> WHERE C.AccountNumb IS NULL
> AND C.FirstDate IS NULL
> AND C.EligNumber IS NULL
>
> DELETE FROM  @TempTableT
>
> INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
> 'Month Begin 20150403' --step3
>
> DELETE @TempTableA
>  FROM @TempTableA B
> LEFT JOIN
> (SELECT A.*
> FROM @TempTableA A
> INNER JOIN @TempTableT T
> ON A.EligNumber =T.EligNumber
> AND A.AccountNumb = T.AccountNumb
> AND A.FirstDate = T.FirstDate) C
> ON B.EligNumber =C.EligNumber
> AND B.AccountNumb = C.AccountNumb
> AND B.FirstDate = C.FirstDate
> WHERE C.AccountNumb IS NULL
> AND C.FirstDate IS NULL
> AND C.EligNumber IS NULL
> DELETE FROM  @TempTableT
>
> INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail 'Month Begin 20141202',
> 'Month Begin 20150501' --step4
>
> DELETE @TempTableA
>  FROM @TempTableA B
> LEFT JOIN
> (SELECT A.*
> FROM @TempTableA A
> INNER JOIN @TempTableT T
> ON A.EligNumber =T.EligNumber
> AND A.AccountNumb = T.AccountNumb
> AND A.FirstDate = T.FirstDate) C
> ON B.EligNumber =C.EligNumber
> AND B.AccountNumb = C.AccountNumb
> AND B.FirstDate = C.FirstDate
> WHERE C.AccountNumb IS NULL
> AND C.FirstDate IS NULL
> AND C.EligNumber IS NULL
>
> SET NOCOUNT OFF
>
> SELECT * FROM @TempTableA --Final Select
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>