britpopfan74
asked on
Deleting records into another table for analysis
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.
SELECT [DOCUMENT]
, CAPACITY_CD
, FIRST_DOS
, TOTAL_PD
FROM @TIER3
GO
Then want to do summary stats like:
SELECT COUNT(DISTINCT [DOCUMENT]) AS TOT_CLAIMS
, SUM(TOTAL_PD) AS TOT_PD
FROM @TIER3
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);
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.
SELECT [DOCUMENT]
, CAPACITY_CD
, FIRST_DOS
, TOTAL_PD
FROM @TIER3
GO
Then want to do summary stats like:
SELECT COUNT(DISTINCT [DOCUMENT]) AS TOT_CLAIMS
, SUM(TOTAL_PD) AS TOT_PD
FROM @TIER3
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you may also want to try
select * from all_records
where tiers.id not in (select id from tier1 UNION select id from tier2 UNION select id from tier3)
dont know which should be faster
this one should at least not create a temporary table with uneeded columns but the tmp table cannot be used as the result set.
if you are concerned about speed also change the other two to select only the columns you need before you compare the speed
actually if the query analyser is perfect, it should be exactly the same
happy coding
select * from all_records
where tiers.id not in (select id from tier1 UNION select id from tier2 UNION select id from tier3)
dont know which should be faster
this one should at least not create a temporary table with uneeded columns but the tmp table cannot be used as the result set.
if you are concerned about speed also change the other two to select only the columns you need before you compare the speed
actually if the query analyser is perfect, it should be exactly the same
happy coding
ASKER