Deleting records into another table for analysis
Posted on 2013-06-08
I have a project where by process of elimination, I need to find which records fall into a final tier. So, basically I know the total amount and three out of the four tiers but need to get the final tier by this method.
Anyhow, I thought I could do a DELETE FROM into a temp table and it wouldn't be an issue but I'm finding that's not the case.
I tried adding the OUTPUT DELETED.* and having it go into a runtime variable which gets me the records, which is great -- however, then I want to run summary stats on this and it is not working.
Posting here the last steps I did and hoping someone may offer advice on how I can delete records into something that I can then query and run stats (like COUNT, SUM) on.
Thank you in advance!
DELETE FROM #temp_CLAIMS_MC -- total claims
OUTPUT DELETED.* INTO @TIER3 -- final tier needed
WHERE [DOCUMENT] NOT IN(SELECT [DOCUMENT] FROM #TIER_CLAIMS) -- temp table of tier 1+2+4
--Display the results of the table variable.
Then want to do summary stats like:
SELECT COUNT(DISTINCT [DOCUMENT]) AS TOT_CLAIMS
, SUM(TOTAL_PD) AS TOT_PD
but get error "Must declare the table variable "@TIER3"."
And have already defined it earlier in the session as:
DECLARE @TIER3 TABLE (
[DOCUMENT] NVARCHAR(9) NOT NULL
, CAPACITY_CD NVARCHAR(4) NOT NULL
, FIRST_DOS DATETIME
, TOTAL_PD INT NOT NULL);