[AccessD] [dba-SQLServer] Looping through queries over and over

[David McAfee]

I ended up doing in without cursors, using table variables and it does the
whole year in less that 1 second:
It could still use some clean up, but it's Friday and I am beat!


CREATE PROCEDURE stpLossOfMediCal AS
DECLARE @FirstBatch AS NVARCHAR(50), @SecondBatch AS NVARCHAR(50) SET
@FirstBatch = 'Month Begin 20141130' --Will change this to pull from a table
DECLARE @NumberRecords int, @RowCount int, @Batch AS NVARCHAR(50),
@PreviousBatchName as NVARCHAR(50), @T CHAR(1)
DECLARE @tblUniqueBatchNames TABLE(RowID int IDENTITY(1, 1), BatchName
NVARCHAR(50))
DECLARE @tblBatchMatrix TABLE(RowID int IDENTITY(1, 1),T CHAR(1), B1
NVARCHAR(50), B2 NVARCHAR(50))

--Insert distinct batch names into temp table A
INSERT INTO @tblUniqueBatchNames
SELECT Provbatchname from tblCovivitas WITH (NOLOCK) WHERE ProvBatchName
like 'Month Begin%' AND ProvBatchName>@FirstBatch  GROUP BY ProvBatchName
ORDER by ProvBatchName

SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1


--Create the Table Matrix for Temp table B:
WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @Batch = BatchName FROM @tblUniqueBatchNames  WHERE RowID =
@RowCount ORDER BY BatchName
 INSERT INTO @tblBatchMatrix SELECT @T, @Batch, batchname from
@tblUniqueBatchNames WHERE BatchName>@Batch oRDER by BatchName
 SET @RowCount = @RowCount + 1
END

--Update the first new batch record for b1 with an A, all other records
with T
UPDATE @tblBatchMatrix SET T = 'A' WHERE RowID IN (SELECT Min(RowID)FROM
@tblBatchMatrix GROUP BY B1)
UPDATE @tblBatchMatrix SET T = 'T' WHERE T IS NULL

--Test Select
--SELECT RowID, T,B1, B2 FROM @tblBatchMatrix ORDER BY B1, B2

--Now that we have the matrix made up, lets do the work:

DECLARE @TempTableA TABLE(a bunch of Fields here)

DECLARE @TempTableT TABLE(a bunch of Fields here)

 --Reset the Rowcount variable to 1
 Set @RowCount = 1

 WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @T = T, @FirstBatch = B1, @SecondBatch = B2 FROM @tblBatchMatrix
 WHERE RowID = @RowCount ORDER BY RowID

 IF @T = 'A'
BEGIN
INSERT INTO @TempTableA EXEC stpLossOfMediCalDetail @FirstBatch,
@SecondBatch
END
ELSE
BEGIN
INSERT INTO @TempTableT EXEC stpLossOfMediCalDetail @FirstBatch,
@SecondBatch

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
END

 SET @RowCount = @RowCount + 1
END

SELECT * FROM @TempTableA  --Not really * , I just put that to keep it
smaller for the list
order by
EligNumber,
LOCATION_NAME,
RecipName,
FirstDate,
SecondDate


I will look into the cube building for other projects that I am working on
over here.

Thanks,
D