Stored Procedure and Ratio column problems.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure and Ratio column problems.

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-04 : 15:02:46

I have this stored procedure and in my store procedure I have an update statement. I am having problems getting the
correct results the the success. Let me explain a little. For the SuccessRatio I need to find all negotiatedclaims and then /by the total number of claims.

Example
Number of Negotiated claims were 4
Number of Non Negotiated claims was 7
Which gives you a total of 11. What I need to do is take the negotiated claims "4" and divide it by Total claims "11". The
result should = 36.36, but my SuccessRatio is saying 31000. I don't understand, can someone help me please?

Here is my procedure below.

SET NOCOUNT ON --added to prevent extra result sets from

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[sumALLNegoNonNegoClaimsWMY]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[sumALLNegoNonNegoClaimsWMY]

--DECLARE @Variables [decimal]

DECLARE @NegotiatedCount int
-------------------------------------------------------------------------------------------------
--STEP ONE "All ClaimSettlement" Weekly, Monthly Yearly
--------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[sumALLNegoNonNegoClaimsWMY](-----Deals and NO Deals
[clmNumber] [varchar](50) NOT NULL,
[Type] [varchar](50) NULL,
[OrderVal] [varchar](50) NULL,
[TotalCharges] [decimal](14,6) NULL,
[AdjustedTotalSavings] [decimal](14,6) NULL,
[PercentSavings] [decimal](14,6) NULL,
[DHSIncome] [decimal](14,6) NULL,
[SuccessRatio] [INT] NULL
)

--------------------------------------------------------------------------------------------------
INSERT INTO [sumALLNegoNonNegoClaimsWMY] ([type],[OrderVal],/** [Year], [Month],**/ clmNumber, TotalCharges, AdjustedTotalSavings, PercentSavings, DHSIncome)

--SELECT 'Weekly' AS Type,1 AS OrderVal,clm_id1 as clmNumber,clm_tchg as TotalCharges, CLM_H30 as AdjustedTotalSavings,clm_sppo as PercentSavings, AccessFeeFinal as DHSIncome
--FROM IMPACT_PROD.DBO.All_Clients_with_Discount_Mgmt_UPDATETEST
--(
SELECT Distinct 'W' AS Type,
1 AS OrderVal,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST((sum(clm_sppo)/ sum(clm_tchg)) AS decimal(8, 6)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO. vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated_Pick_List
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))--)

UNION ALL

SELECT Distinct 'M',2 AS OrderVal,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST((sum(clm_sppo)/ sum(clm_tchg)) AS decimal(8, 6)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO. vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated_Pick_List
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (MONTH(CLM_DOUT) = MONTH(GETDATE()))

UNION ALL

SELECT Distinct 'Y',3 AS OrderVal,
Count(distinct clm_id1) as clmNumber,
SUM(CAST(clm_tchg AS MONEY)) AS TotalCharges,
SUM(CAST(CLM_H30 AS MONEY)) AS AdjustedTotalSavings,
CAST((sum(clm_sppo)/ sum(clm_tchg)) AS decimal(8, 6)) AS PercentSavings,
SUM(CAST(AccessFeeFinal AS MONEY)) AS DHSIncome
FROM IMPACT_PROD.DBO. vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated_Pick_List
WHERE (YEAR(CLM_DOUT) = YEAR(GETDATE()))

-------------------------------------------------------------------------------------------
SET @NegotiatedCount = (SELECT COUNT(*) FROM IMPACT_PROD.DBO.vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated_Pick_List WHERE clm_nego <> 0.00)
UPDATE [sumALLNegoNonNegoClaimsWMY]
SET SuccessRatio = @NegotiatedCount / ClmNumber * 100
----------------------------------------------------------------------------------------------------

SELECT * FROM sumALLNegoNonNegoClaimsWMY ORDER BY OrderVal

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-04 : 15:12:09

Please provide sample Data.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-04 : 15:24:48

SET SuccessRatio = @NegotiatedCount*1.0 / ClmNumber * 100

Jim

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-04 : 15:43:02

Jim,
I did what you said to do, now the number is at 31090 instead of 31000. we are getting close I guess. Not sure why it is doing this.

quote:
Originally posted by jimf

SET SuccessRatio = @NegotiatedCount*1.0 / ClmNumber * 100

Jim

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-11-04 : 16:25:01

Testing tip: Temporarily replace your UPDATE statement with a SELECT to show you the values that the code has determined for @NegotiatedCount and ClmNumber. Are they 4 and 11 respectfully? I would guess they are not. But this will at least get you pointed in the right direction to figure out why they are not the numbers you think they should be.

---------------------------
EmeraldCityDomains.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:54:29

wat about this?

UPDATE [sumALLNegoNonNegoClaimsWMY]
SET SuccessRatio = (@NegotiatedCount  * 100.0/ ClmNumber)

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-05 : 09:03:58

Yeah that didn't work either, what I am thinking about doing is adding two columns one that says deals and one that says nodeals. I am not sure how to right that update statement though. This is what I have...

Set Deal = (SELECT COUNT(*) FROM IMPACT_PROD.DBO.vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated_Pick_List WHERE clm_nego <>0.00)
UPDATE [sumALLNegoNonNegoClaimsWMY]
Set Deal = @Deal

quote:
Originally posted by visakh16

wat about this?

UPDATE [sumALLNegoNonNegoClaimsWMY]
SET SuccessRatio = (@NegotiatedCount * 100.0/ ClmNumber)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:38:39

probabaly you could give some sample data to illustrate what values you need and what you're currently getting with source column values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources