Calculating the Percentages - SQL Server Forums

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

Calculating the Percentages

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-08-31 : 04:34:50

Hi All,

SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
WHEN SM.SERVICE_TYPE_N = 6 THEN 'PHYSIOTHERAPY'
WHEN SM.SERVICE_TYPE_N = 7 THEN 'CARDIOLOGY'
WHEN SM.SERVICE_TYPE_N = 8 THEN 'OPHTHALMOLOGY'
ELSE ' ' END
AS [SERVICE_GROUP],SM.SERVICE_TYPE_N,ROUND(SUM(BM.BILL_AMOUNT_M),2)AS [BILL_AMOUNT],ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [TOTAL_CONCESSION_GIVEN]
FROM SERVICE_MASTER SM
INNER JOIN BILL_DETAILS BD ON BD.SERVICE_CODE_N=SM.SERVICE_ID_N
INNER JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V= BD.BILL_NUMBER_V
WHERE CONVERT(VARCHAR(10),YEAR(BM.BILL_DATE_D),101)= CONVERT(VARCHAR(10),@YEAR,101)
AND CONVERT(VARCHAR(10),MONTH(BM.BILL_DATE_D),101)=CONVERT(VARCHAR(10),@MONTH,101)
AND BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M>0.00
AND BM.CONCESSION_AMOUNT_M>0.00
GROUP BY SM.SERVICE_TYPE_N
ORDER BY SM.SERVICE_TYPE_N

In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?

1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M

2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30

3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-31 : 04:42:00

[code]
SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
WHEN SM.SERVICE_TYPE_N = 6 THEN 'PHYSIOTHERAPY'
WHEN SM.SERVICE_TYPE_N = 7 THEN 'CARDIOLOGY'
WHEN SM.SERVICE_TYPE_N = 8 THEN 'OPHTHALMOLOGY'
ELSE ' ' END
AS [SERVICE_GROUP],SM.SERVICE_TYPE_N,ROUND(SUM(BM.BILL_AMOUNT_M),2)AS [BILL_AMOUNT],ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [TOTAL_CONCESSION_GIVEN],
CASE WHEN ROUND(SUM(BM.BILL_AMOUNT_M),2) = ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [100PERCENT],
CASE WHEN ROUND(SUM(BM.BILL_AMOUNT_M),2)*0.30 = ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [30PERCENT],
CASE WHEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) <> ROUND(SUM(BM.BILL_AMOUNT_M),2)*0.30
OR ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) <> ROUND(SUM(BM.BILL_AMOUNT_M),2)
THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [OTHERCONCESSIONPERCENT]
FROM SERVICE_MASTER SM
INNER JOIN BILL_DETAILS BD ON BD.SERVICE_CODE_N=SM.SERVICE_ID_N
INNER JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V= BD.BILL_NUMBER_V
WHERE CONVERT(VARCHAR(10),YEAR(BM.BILL_DATE_D),101)= CONVERT(VARCHAR(10),@YEAR,101)
AND CONVERT(VARCHAR(10),MONTH(BM.BILL_DATE_D),101)=CONVERT(VARCHAR(10),@MONTH,101)
AND BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M>0.00
AND BM.CONCESSION_AMOUNT_M>0.00
GROUP BY SM.SERVICE_TYPE_N
ORDER BY SM.SERVICE_TYPE_N
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

taj
Starting Member

39 Posts

Posted - 2013-08-31 : 07:24:54

quote:

Originally posted by visakh16


SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY' 
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY' 
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
WHEN SM.SERVICE_TYPE_N = 6 THEN 'PHYSIOTHERAPY'
WHEN SM.SERVICE_TYPE_N = 7 THEN 'CARDIOLOGY'
WHEN SM.SERVICE_TYPE_N = 8 THEN 'OPHTHALMOLOGY'
ELSE ' ' END	
AS [SERVICE_GROUP],SM.SERVICE_TYPE_N,ROUND(SUM(BM.BILL_AMOUNT_M),2)AS [BILL_AMOUNT],ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [TOTAL_CONCESSION_GIVEN],
CASE WHEN ROUND(SUM(BM.BILL_AMOUNT_M),2) = ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [100PERCENT],
CASE WHEN ROUND(SUM(BM.BILL_AMOUNT_M),2)*0.30 = ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [30PERCENT],
CASE WHEN  ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) <> ROUND(SUM(BM.BILL_AMOUNT_M),2)*0.30
       OR  ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) <> ROUND(SUM(BM.BILL_AMOUNT_M),2) 
     THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [OTHERCONCESSIONPERCENT]
FROM SERVICE_MASTER SM 
INNER JOIN BILL_DETAILS BD ON BD.SERVICE_CODE_N=SM.SERVICE_ID_N 
INNER JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V= BD.BILL_NUMBER_V
WHERE CONVERT(VARCHAR(10),YEAR(BM.BILL_DATE_D),101)= CONVERT(VARCHAR(10),@YEAR,101) 
AND CONVERT(VARCHAR(10),MONTH(BM.BILL_DATE_D),101)=CONVERT(VARCHAR(10),@MONTH,101) 
AND BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M>0.00
AND BM.CONCESSION_AMOUNT_M>0.00
GROUP BY SM.SERVICE_TYPE_N
ORDER BY SM.SERVICE_TYPE_N

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks for your reply, i want to display the 100%, 30% and other than 100% and 30 % in other concession column as shown below.

ServiceGroups Bill Amt 100% 30% Other con Tot con
Laboratory 1000.00 200.00 200.00
Podiatry 2000.00 600.00 500.00 100.00 1200.00
Administartion 3000.00 0.00
Diet 4000.00 800.00 300.00 400.00 1500.00
Dental 5000.00 700.00 700.00
Physiotherapy 6000.00 900.00 900.00
Cardiology 7000.00 400.00 400.00
Ophthalmology 8000.00 1000.00 1000.00

if i add the below code in my query it returns no 100% and 30% rows for all the service groups,it will consider all the concessions from 0 to 100 in other concessions only. but my table consists data of 100% and 30% concession also.

CASE WHEN ROUND(SUM(BM.BILL_AMOUNT_M),2) = ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [100PERCENT],
CASE WHEN ROUND(SUM(BM.BILL_AMOUNT_M),2)*0.30 = ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [30PERCENT],
CASE WHEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) <> ROUND(SUM(BM.BILL_AMOUNT_M),2)*0.30
OR ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) <> ROUND(SUM(BM.BILL_AMOUNT_M),2)
THEN ROUND(SUM(BM.CONCESSION_AMOUNT_M),2) END AS [OTHERCONCESSIONPERCENT]

my condition for 100% concession should be BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M

for 30% concession
BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30

rest all the concessions other than 100% and 30% should be consider under other concessions.

Please help me in fixing this requirement.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:24:40

i've given suggestion as per your explanation. If that doest work it may be due to how data is present which I cant guess. Post some data and show what do yo mean by not working

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources