MS SQL Server - Generate 'calculated values' for rows within Stored Procedure

Author

Generate 'calculated values' for rows within Stored Procedure

The Gekkster via SQLMonster.com

2005-04-27, 8:23 pm

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:

#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630

More... 676 .34602709630
#######

The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:

#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######

The SP is as follows, where the function [dbo. udf_Current_Inventor
yFunction
()] provides the 'total' count (which in the examples above is 8,193).

#######
ALTER PROCEDURE dbo. usp_VClass_Breakdown

AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass
) AS NUMERIC)
FROM dbo. vw_VClass_BreakdownV
iew
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo. udf_Current_Inventor
yFunction
()) AS Expr1
FROM dbo. vw_VClass_BreakdownV
iew
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######

Any thoughts or suggestions would be appreciated. Thanks.

--
Message posted via http://www.sqlmonster.com

Chandra

2005-04-28, 3:23 am

Hi,
The Query can be re-written as:
====================
=========
ALTER PROCEDURE dbo. usp_VClass_Breakdown

AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_Inventor
yFunction() PercentOfVP
FROM dbo. vw_VClass_BreakdownV
iew
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================

I believe this addressed your question. If there are any more problem please
revert back

thanks and regards
Chandra

"The Gekkster via SQLMonster.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
>
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
>
> More... 676 .34602709630
> #######
>
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
>
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
>
> The SP is as follows, where the function [dbo. udf_Current_Inventor
yFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
>
> #######
> ALTER PROCEDURE dbo. usp_VClass_Breakdown

> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass
) AS NUMERIC)
> FROM dbo. vw_VClass_BreakdownV
iew
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo. udf_Current_Inventor
yFunction
> ()) AS Expr1
> FROM dbo. vw_VClass_BreakdownV
iew
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
>
> Any thoughts or suggestions would be appreciated. Thanks.
>
> --
> Message posted via http://www.sqlmonster.com
>

The Gekkster via SQLMonster.com

2005-04-28, 9:23 am

Hi Chandra,

Thanks for the assist - that took care of it.

--
Message posted via http://www.sqlmonster.com

Chandra

2005-04-29, 3:23 am

Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.

This can be done by answering "Was this post helpful to you?"

"The Gekkster via SQLMonster.com" wrote:

> Hi Chandra,
>
> Thanks for the assist - that took care of it.
>
> --
> Message posted via http://www.sqlmonster.com
>

Sponsored Links