CALCULATING PERCENTAGE VALUE - 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 PERCENTAGE VALUE

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-08-29 : 02:50:55

Hi All,

I have requirement in which i need to calculate percentage value based on billamount and concession amount

Below is the qurey in which i need help to calculate the percentage part and show the o/p.

SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M IS NOT NULL
AND BM.TOTAL_AMOUNT_M IS NOT NULL
AND BM.CONCESSION_AMOUNT_M >0.00

Slno KIDNO PName BillAmt ConcessAmt Total %percentage
1 13203 ABC 49 21 70 30%
2 15475 XYZ 0 980 980 100%
3 13619 RTS 241.5 103.5 345 30%
4 15475 PQR 35 35 70 50%

Thank You,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 02:53:25

[code]
SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],
BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],BM.TOTAL_AMOUNT_M * 100.0/BM.CONCESSION_AMOUNT_M AS [%percentage]
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M IS NOT NULL
AND BM.TOTAL_AMOUNT_M IS NOT NULL
AND BM.CONCESSION_AMOUNT_M >0.00
[/code]

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

taj
Starting Member

39 Posts

Posted - 2013-08-29 : 03:28:42

THANKS FOR YOUR REPLY,

but why my percentage column is showing the output in below format

KIDNO PName BillAmt ConcessAmt Total %percentage
2 PILLAI 70 21 49 333.33
2 PILLAI 400 200 200 200
57 RAVI 400 200 200 200
58 PUSHPA 400 200 200 200
59 SAI 100 100 0 100

It should display as 30%, 50%, and 100%

Below is my query in which i made some alterations

Please give the query which displays the percentage in the above mentioned format

Thank You,

taj
Starting Member

39 Posts

Posted - 2013-08-29 : 03:30:03

quote:
Originally posted by taj

THANKS FOR YOUR REPLY,

but why my percentage column is showing the output in below format

KIDNO PName BillAmt ConcessAmt Total %percentage
2 PILLAI 70 21 49 333.33
2 PILLAI 400 200 200 200
57 RAVI 400 200 200 200
58 PUSHPA 400 200 200 200
59 SAI 100 100 0 100

It should display as 30%, 50%, and 100%

Below is my query in which i made some alterations

Please give the query which displays the percentage in the above mentioned format

Thank You,

SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME], BM.BILL_NUMBER_V AS [BILL_NUMBER],
BM.BILL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.TOTAL_AMOUNT_M AS [TOTAL_AMOUNT],
ROUND(BM.BILL_AMOUNT_M * 100.0/BM.CONCESSION_AMOUNT_M,2) AS [%PERCENTAGE],
BM.BILL_REMARKS_V AS [CONCESSION_REASON] FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M IS NOT NULL
--AND BM.CONCESSION_AMOUNT_M IS NOT NULL
AND BM.TOTAL_AMOUNT_M IS NOT NULL
AND BM.CONCESSION_AMOUNT_M >0.00

taj
Starting Member

39 Posts

Posted - 2013-08-29 : 03:43:18

quote:

Originally posted by visakh16


SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],
BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],BM.TOTAL_AMOUNT_M * 100.0/BM.CONCESSION_AMOUNT_M AS [%percentage]
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M IS NOT NULL
AND BM.TOTAL_AMOUNT_M IS NOT NULL
AND BM.CONCESSION_AMOUNT_M >0.00

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

Hi Visakh,

THANKS FOR YOUR REPLY,

but why my percentage column is showing the output in below format

KIDNO PName BillAmt ConcessAmt Total %percentage
2 PILLAI 70 21 49 333.33
2 PILLAI 400 200 200 200
57 RAVI 400 200 200 200
58 PUSHPA 400 200 200 200
59 SAI 100 100 0 100

It should display as 30%, 50%, and 100%

Below is my query in which i made some alterations
SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME], BM.BILL_NUMBER_V AS [BILL_NUMBER],
BM.BILL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.TOTAL_AMOUNT_M AS [TOTAL_AMOUNT],
ROUND(BM.BILL_AMOUNT_M * 100.0/BM.CONCESSION_AMOUNT_M,2) AS [%PERCENTAGE],
BM.BILL_REMARKS_V AS [CONCESSION_REASON] FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M IS NOT NULL
--AND BM.CONCESSION_AMOUNT_M IS NOT NULL
AND BM.TOTAL_AMOUNT_M IS NOT NULL
AND BM.CONCESSION_AMOUNT_M >0.00

Please give the query which displays the percentage in the above mentioned format

Thank You,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 03:56:31

it depends on your requirement. I gave the query as per your explanation. probably what you need is this??


SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME], BM.BILL_NUMBER_V AS [BILL_NUMBER],
BM.BILL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.TOTAL_AMOUNT_M AS [TOTAL_AMOUNT],
ROUND(BM.CONCESSION_AMOUNT_M * 100.0/BM.BILL_AMOUNT_M ,2)  AS [%PERCENTAGE],
BM.BILL_REMARKS_V AS [CONCESSION_REASON] FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'
AND BM.BILL_CANCELLED_C='N'
AND BM.BILL_AMOUNT_M IS NOT NULL
--AND BM.CONCESSION_AMOUNT_M IS NOT NULL
AND BM.TOTAL_AMOUNT_M IS NOT NULL
AND BM.CONCESSION_AMOUNT_M >0.00

------------------------------------------------------------------------------------------------------
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