Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subtract Values from Two Temp Tables 1

Status
Not open for further replies.
larrydavid

larrydavid

Programmer
Jul 22, 2010
174
US
Hello,

Within a greater select, I need to calculate a value based on a numerator and denominator as follows:

Code: 
--numerator: denied claim
DROP TABLE #dcn
SELECT COUNT(tc.CLAIMNO) as dcn_count
INTO #dcn
FROM TEST_CLAIMS tc 
WHERE tc.STATUS IN ('02','91') 
AND (tc.AMT1 = 0 AND tc.AMT2 = 0 AND tc.AMT3 = 0)

--denominator: paid claim
DROP TABLE #dcd
SELECT COUNT(tc.CLAIMNO) as dcd_count
INTO #dcd
FROM TEST_CLAIMS tc
WHERE tc.STATUS IN ('02','91')  
AND (tc.AMT1 > 0 OR tc.AMT2 > 0 OR tc.AMT3 > 0)

select dcn_count from #dcn (result = 1532375)
select dcd_count from #dcd (result = 5857895)

So the output I'm looking for is the [Count 1] / [Count 2]
(1532375 / 5857895).

I need to divide the two results from the temp tables and am not quite sure what the best approach is. This will be one of several other values within a main select statement.

Any help would be greatly appreciated.

Thanks,
Larry
 
Sort by date Sort by votes
Code: 
SELECT Tbl1.Count*1.0 /  Tbl2.Count AS Test
FROM (SELECT COUNT(tc.CLAIMNO) as dcn_count
             FROM TEST_CLAIMS tc
      WHERE tc.STATUS IN ('02','91')
      AND (tc.AMT1 = 0 AND tc.AMT2 = 0 AND tc.AMT3 = 0)) Tbl1

CROSS JOIN (SELECT COUNT(tc.CLAIMNO) as dcd_count
                   FROM TEST_CLAIMS tc
            WHERE tc.STATUS IN ('02','91')  
             AND (tc.AMT1 > 0 OR tc.AMT2 > 0 OR tc.AMT3 > 0)) Tbl2
Not tested!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Hi Boris,

I had to change the column name "count" to dcn_count and dcd_count in the select statement and it worked to give me the calculation. Then I just studied this for awhile to make sure I understood what you did and how you implemented the cross join.

I am trying to place this in a larger select statement (in a stored procedure) so when I add this code I made sure the previous value had a comma after and that this code block also had a comma after, but I get this error when I do a syntax check:

Msg 156, Level 15, State 1, Procedure udsp_TESTCLAIMS, Line 23
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure udsp_TESTCLAIMS, Line 34
Incorrect syntax near '='.

Here is the stored proc and how I am trying to implement this:

Code: 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER   PROCEDURE [dbo].[udsp_TESTCLAIMS] 
AS
BEGIN

	truncate table dbo.[TESTCLAIMS]

INSERT INTO dbo.[TESTCLAIMS]
(
	[Average_Billed],
	[Average_Paid],
	[Denial_Rate],
	[Identified_Underpayments],
	[Year],
	[Month],
	[MonthName]
)
   SELECT	
			[Average_Billed] = CASE WHEN tc.STATUS = '02' THEN ISNULL(SUM(tc.TOTAL) / NULLIF(COUNT(tc.CLAIMNO),0),0) * 100 END,
			[Average_Paid] = CASE WHEN tc.STATUS IN ('02','91') THEN ISNULL(SUM(tc.AMT1 + tc.AMT2 + tc.AMT3) / NULLIF(COUNT(tc.CLAIMNO),0),0) * 100 END,
				
			----------------------------------------------------------------
			SELECT Tbl1.dcn_count*1.0 / Tbl2.dcd_count AS [Denial_Rate]
			FROM (SELECT COUNT(tc.CLAIMNO) as dcn_count
						 FROM TEST_CLAIMS tc
				  WHERE tc.STATUS IN ('02','91')
				  AND (tc.AMT1 = 0 AND tc.AMT2 = 0 AND tc.AMT3 = 0)) Tbl1
			CROSS JOIN (SELECT COUNT(tc.CLAIMNO) as dcd_count
							   FROM TEST_CLAIMS tc
						WHERE tc.STATUS IN ('02','91')  
						 AND (tc.AMT1 > 0 OR tc.AMT2 > 0 OR tc.AMT3 > 0)) Tbl2,
			-----------------------------------------------------------------
		
			[Identified_Underpayments] = CASE WHEN tc.STATUS IN ('02','91') THEN SUM(tc.AMT1 + tc.AMT2 + tc.AMT3) END, 
			[Year] = DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
			[Month] = DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
			[MonthName] = LEFT(DateName(Month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   FROM	Claims_TEST.dbo.TEST_CLAIMS AS tc 
			INNER JOIN Top_Companies AS p ON LEFT(tc.COMPANY, 9) = p.NAME9 
   WHERE	tc.STATUS <> 'XX' AND convert(datetime, tc.PAIDDT, 102) between dateadd(year, datediff(year, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate())-12 , 0)	
   GROUP BY tc.STATUS,
			tc.amt1,
			tc.amt2,
			tc.amt3,
			DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
			DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
			LEFT(DateName(month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   ORDER BY DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
			DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
			LEFT(DateName(month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   END

Not sure what I'm overlooking here.

Thanks,
Larry
 
Upvote 0 Downvote
If this is a SP, you easily can use variables :)
So store the value of calculations into one variable and then use this variable in INSERT.
There is no need to do this calculations over and over again for each row of your insert.
Code: 
ALTER   PROCEDURE [dbo].[udsp_TESTCLAIMS]
AS
BEGIN

    truncate table dbo.[TESTCLAIMS]

INSERT INTO dbo.[TESTCLAIMS]
(
    [Average_Billed],
    [Average_Paid],
    [Denial_Rate],
    [Identified_Underpayments],
    [Year],
    [Month],
    [MonthName]
)
  DECLARE @Denial_Rate float
  SELECT @Denial_Rate =  (Tbl1.dcn_count*1.0 / Tbl2.dcd_count)
    FROM (SELECT COUNT(tc.CLAIMNO) as dcn_count
                 FROM TEST_CLAIMS tc
           WHERE tc.STATUS IN ('02','91')
            AND (tc.AMT1 = 0 AND tc.AMT2 = 0 AND tc.AMT3 = 0)) Tbl1
   CROSS JOIN (SELECT COUNT(tc.CLAIMNO) as dcd_count
                      FROM TEST_CLAIMS tc
               WHERE tc.STATUS IN ('02','91')  
                AND (tc.AMT1 > 0 OR tc.AMT2 > 0 OR tc.AMT3 > 0)) Tbl2

   SELECT    
            CASE WHEN tc.STATUS = '02' 
                 THEN ISNULL(SUM(tc.TOTAL) / NULLIF(COUNT(tc.CLAIMNO),0),0) * 100 END, -- No need of named columns when you did INSERT
            CASE WHEN tc.STATUS IN ('02','91')
                 THEN ISNULL(SUM(tc.AMT1 + tc.AMT2 + tc.AMT3) / NULLIF(COUNT(tc.CLAIMNO),0),0) * 100 END,
            ----------------------------------------------------------------
            @Denial_Rate,
            -----------------------------------------------------------------
        
            CASE WHEN tc.STATUS IN ('02','91') THEN SUM(tc.AMT1 + tc.AMT2 + tc.AMT3) END,
            DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
            DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
            LEFT(DateName(Month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)

   FROM    Claims_TEST.dbo.TEST_CLAIMS AS tc
            INNER JOIN Top_Companies AS p ON LEFT(tc.COMPANY, 9) = p.NAME9
   WHERE    tc.STATUS <> 'XX' AND convert(datetime, tc.PAIDDT, 102) between dateadd(year, datediff(year, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate())-12 , 0)    
   GROUP BY tc.STATUS,
            tc.amt1,
            tc.amt2,
            tc.amt3,
            DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
            DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
            LEFT(DateName(month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   ORDER BY DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
            DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
            LEFT(DateName(month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Huge typo, sorry.
The calculations must be BEFORE INSERT INTO clause:

Code: 
ALTER   PROCEDURE [dbo].[udsp_TESTCLAIMS]
AS
BEGIN

 truncate table dbo.[TESTCLAIMS]
  DECLARE @Denial_Rate float
  SELECT @Denial_Rate =  (Tbl1.dcn_count*1.0 / Tbl2.dcd_count)
    FROM (SELECT COUNT(tc.CLAIMNO) as dcn_count
                 FROM TEST_CLAIMS tc
           WHERE tc.STATUS IN ('02','91')
            AND (tc.AMT1 = 0 AND tc.AMT2 = 0 AND tc.AMT3 = 0)) Tbl1
   CROSS JOIN (SELECT COUNT(tc.CLAIMNO) as dcd_count
                      FROM TEST_CLAIMS tc
               WHERE tc.STATUS IN ('02','91')  
                AND (tc.AMT1 > 0 OR tc.AMT2 > 0 OR tc.AMT3 > 0)) Tbl2

INSERT INTO dbo.[TESTCLAIMS]
(
    [Average_Billed],
    [Average_Paid],
    [Denial_Rate],
    [Identified_Underpayments],
    [Year],
    [Month],
    [MonthName]
)

   SELECT    
            CASE WHEN tc.STATUS = '02'
                 THEN ISNULL(SUM(tc.TOTAL) / NULLIF(COUNT(tc.CLAIMNO),0),0) * 100 END, -- No need of named columns when you did INSERT
            CASE WHEN tc.STATUS IN ('02','91')
                 THEN ISNULL(SUM(tc.AMT1 + tc.AMT2 + tc.AMT3) / NULLIF(COUNT(tc.CLAIMNO),0),0) * 100 END,
            ----------------------------------------------------------------
            @Denial_Rate,
            -----------------------------------------------------------------
        
            CASE WHEN tc.STATUS IN ('02','91') THEN SUM(tc.AMT1 + tc.AMT2 + tc.AMT3) END,
            DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
            DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
            LEFT(DateName(Month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)

   FROM    Claims_TEST.dbo.TEST_CLAIMS AS tc
            INNER JOIN Top_Companies AS p ON LEFT(tc.COMPANY, 9) = p.NAME9
   WHERE    tc.STATUS <> 'XX' AND convert(datetime, tc.PAIDDT, 102) between dateadd(year, datediff(year, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate())-12 , 0)    
   GROUP BY tc.STATUS,
            tc.amt1,
            tc.amt2,
            tc.amt3,
            DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
            DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
            LEFT(DateName(month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   ORDER BY DatePart(year,CONVERT(datetime,tc.PAIDDT,102)),
            DatePart(Month,CONVERT(datetime,tc.PAIDDT,102)),
            LEFT(DateName(month,CONVERT(datetime,tc.PAIDDT,102)),3) + ' ' + LEFT(tc.PAIDDT,4)
   END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Hi Boris,

Thank you so much. Another great learning experience thanks to you. I hope other can benefit from this as much as I have. There will be many instances this can be applied. Very valuable info.

Thanks again,
Larry
 
Upvote 0 Downvote
Hi Boris,

After some testing I realize I am having a problem. My output is returning duplicate values which is throwing my record count off by thousands. here is a sample of the output:

Code: 
Average_Paid	Dispute_Rate	Denial_Rate
0.000000	  0.000000000000	 0.217769100388
46922.000000	0.000000000000	0.217769100388
NULL	        0.000000000000	0.217769100388
21850.000000	0.000000000000	0.217769100388
26352.000000	0.000000000000	0.217769100388
48168.000000	0.000000000000	0.217769100388
50035.000000	0.000000000000	0.217769100388
166964.000000	0.000000000000	0.217769100388
186816.000000	0.000000000000	0.217769100388
240840.000000	0.000000000000	0.217769100388
264924.000000	0.000000000000	0.217769100388
313092.000000	0.000000000000	0.217769100388
316224.000000	0.000000000000	0.217769100388
342576.000000	0.000000000000	0.217769100388
46922.000000	0.000000000000	0.217769100388
342576.000000	0.000000000000	0.217769100388
59524.000000	0.000000000000	0.217769100388
82761.000000	0.000000000000	0.217769100388
62037.000000	0.000000000000	0.217769100388
72252.000000	0.000000000000	0.217769100388
76002.000000	0.000000000000	0.217769100388
79056.000000	0.000000000000	0.217769100388

I did as you advised and placed the following before the insert:

Code: 
DECLARE @Denial_Rate float
  SELECT @Denial_Rate =  (Tbl1.dcn_count*1.0 / Tbl2.dcd_count)
    FROM (SELECT COUNT(tc.CLAIMNO) as dcn_count
                 FROM TEST_CLAIMS tc
           WHERE tc.STATUS IN ('02','91')
            AND (tc.AMT1 = 0 AND tc.AMT2 = 0 AND tc.AMT3 = 0)) Tbl1
   CROSS JOIN (SELECT COUNT(tc.CLAIMNO) as dcd_count
                      FROM TEST_CLAIMS tc
               WHERE tc.STATUS IN ('02','91')  
                AND (tc.AMT1 > 0 OR tc.AMT2 > 0 OR tc.AMT3 > 0)) Tbl2

I also tested along with the select and get the same return count, which is 25,061 vs. 977 I was getting before with the CASE statement.

Can you please tell me what might be happening and how it can be solved?

Thanks,
Larry
 
Upvote 0 Downvote
OK, give me some data and desired result from it.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Hi Boris,

Here is some data from the TEST_CLAIMS table:
Code: 
CLAIMNO	  SEGAMT1 SEGAMT2 SEGAMT3 STATUS TOTCHG
X18335255    25.00	0.00	0.00	02	25.00
X18305148    173.64	0.00	0.00	02	494.00
X18335264    25.00	0.00	0.00	02	25.00
X18335232    25.00	2.00	0.00	02	25.00
X18335274    25.00	0.00	0.00	91	25.00
X18335165    25.00	0.00	3.00	91	25.00
X18334992    25.00	0.00	0.00	02	25.00
X18433901    9.23	0.00    0.00	02	36.00
X18334918    25.00	0.00	0.00	02	25.00

Here is the desired output:
Code: 
Company        Average_Paid     Denial_Rate
ABC Co.        0.000000         0.39
BCD Co.        46922.000000     0.41
XYZ Co.        NULL             0.26
123 Co.        21850.000000     0.23
456 Co.        26352.000000     0.29

So, we need a different result for each company (see INNER JOIN with main query in previous post) as we have with the other values in which a CASE statement is employed.

Thanks,
Larry
 
Upvote 0 Downvote
OK, but where is the company ID?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Sorry, here it is:

Code: 
CompanyId   Company      Average_Paid     Denial_Rate
12345       ABC Co.      0.000000         0.39
12346       BCD Co.      46922.000000     0.41
12347       XYZ Co.      NULL             0.26
12348       123 Co.      21850.000000     0.23
12349       456 Co.      26352.000000     0.29

Thanks,
Larry
 
Upvote 0 Downvote
HI Boris,

Have you been able to arrive at a solution? If not I will try to on my own. Thanks for trying anyway.

Thanks,
Larry
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
265
bharons
bharons
Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
226
Cpreston
Cpreston
briangriffin
  • Locked
  • Question
Calculating differences every two rows
Replies
2
Views
177
kjv1611
kjv1611
ms901Boss
  • Locked
  • Question
Join two select statements and collecting data from both
Replies
13
Views
150
mikrom
mikrom
ms901Boss
  • Locked
  • Question
Joining two select statement into one
Replies
1
Views
85
kray4660
kray4660

Part and Inventory Search

Sponsor

Back
Top