1
\$\begingroup\$

I'm writing a log table which contains the summary of the claims. The log table logs the data whenever there is an upload.

I have written the following SQL query. Though it serves the purpose, I'm trying to learn whether there is any way to simplify the UPDATEs.

CREATE TABLE #1 
  ( 
     id                         INT IDENTITY(1, 1), 
     [total no claims]          INT, 
     [total claims inserted]    INT, 
     [total claims errored out] INT, 
     [erroed claims]            VARCHAR(max) 
  ) 

INSERT INTO #1 
            ([total no claims]) 
SELECT Count(DISTINCT [claimnumber]) 
FROM   [Operations Productivity Tool].[dbo].[test_importedauditdata] ta 
WHERE  claimnumber IN (SELECT DISTINCT claimnumber 
                       FROM   [dbo].[opcod audit information] ts 
                       WHERE  [final status] IN ( 'Finding', 'No Finding' )) 
       AND id = Scope_identity() 

UPDATE #1 
SET    [total claims inserted] = (SELECT Count(DISTINCT [claimnumber]) 
                                  FROM 
              [Operations Productivity Tool].[dbo].[test_importedauditdata] ta 
                                  WHERE  claimnumber NOT IN 
                                         (SELECT DISTINCT claimnumber 
                                          FROM   [dbo].[opcod audit information] 
                                                 ts 
                                          WHERE  [final status] IN ( 
                                                 'Finding', 'No Finding' ) 
                                         )) 
WHERE  id = Scope_identity() 

UPDATE #1 
SET    [total claims errored out] = (SELECT 
       [total no claims] - [total claims inserted] 
                                     FROM   #1) 
WHERE  id = Scope_identity() 

UPDATE #1 
SET    [erroed claims] = (SELECT 
                                Stuff ((SELECT DISTINCT ',' + claimnumber 
                                         FROM 
       [Operations Productivity Tool].[dbo].[test_importedauditdata] 
                WHERE  claimnumber NOT IN 
                       (SELECT DISTINCT claimnumber 
                        FROM   [dbo].[opcod audit information] ts 
                        WHERE  [final status] NOT IN ( 
                               'Finding', 'No Finding' )) 
                FOR xml 
       path(''), type).value('text()[1]', 'varchar(max)'), 1, 2, '')) 
WHERE  id = Scope_identity()
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.