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

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-09-28 : 10:03:33

Hi,

I have the following select statement which works fine, however, know I want it to match to columns and display the amoount on a final table, e.g.,

ProjectID Vision_PatId Paystatus FullFee PartFee

1 7633 Full Pay 100.00 50.00
2 9273 Part Pay 100.00 50.00
3 8887 Full Pay 100.00 50.00

The above example is provided by the select statement below, what I want to have also is another column called Gross Amount as

ProjectID Vision_PatId Paystatus FullFee PartFee G.Amount

1 7633 Full Pay 100.00 50.00 100.00
2 9273 Part Pay 100.00 50.00 50.00
3 7633 Full Pay 120.00 50.00 120.00

Notice it checks if its Full Pay then it inserts 100.00 and 120.00 but if it is Part Fee it inserts 50.00.

Finally, if the Vision_PatId is the same it sums them, and for the above example the answer will be 220.00 for vision_patid = 7633. Any help please............ Many thanks

SELECT     dbo.qrypreviewpayment.ProjectID, dbo.qrypreviewpayment.Vision_PatID, dbo.qrypreviewpayment.PayStatus, dbo.tblProjects.FullFee, 
                      dbo.tblProjects.PartFee
FROM         dbo.tblProjects INNER JOIN
                      dbo.qrypreviewpayment ON dbo.tblProjects.ProjectID = dbo.qrypreviewpayment.ProjectID

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-28 : 10:47:20

Try this:

SELECT dbo.qrypreviewpayment.ProjectID, dbo.qrypreviewpayment.Vision_PatID, dbo.qrypreviewpayment.PayStatus, dbo.tblProjects.FullFee,
dbo.tblProjects.PartFee,
Case when dbo.qrypreviewpayment.PayStatus = 'Full Pay' then dbo.tblProjects.FullFee
else
dbo.tblProjects.PartFee
End as GrossAmount

FROM dbo.tblProjects INNER JOIN
dbo.qrypreviewpayment ON dbo.tblProjects.ProjectID = dbo.qrypreviewpayment.ProjectID

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-09-28 : 11:32:50

Thanks, re-generated my code to

SELECT     TOP (100) PERCENT dbo.tblProjects.ProjectID, dbo.tblProjectPatients.Prac_no, dbo.tblProjectPatients.PayStatus,  
                      CASE WHEN dbo.tblProjectPatients.PayStatus = 'Full Pay' THEN dbo.tblProjects.FullFee WHEN dbo.tblProjectPatients.PayStatus = 'Part Pay' THEN dbo.tblProjects.PartFee
                       ELSE 0.00 END AS AmountPerPatient
FROM         dbo.tblProjects INNER JOIN
                      dbo.tblProjectPatients ON dbo.tblProjects.ProjectID = dbo.tblProjectPatients.ProjectID
GROUP BY dbo.tblProjects.ProjectID, dbo.tblProjectPatients.Prac_no, dbo.tblProjectPatients.PayStatus

..

This works fine.

One last thing, I may have a result like;

ProjectID Prac_no PayStatus Amountoperpatient
1 100 Full Pay 50.00
1 100 Part Pay 25.00
1 102 Full Pay 70.00

I would like to have a result as shown below;

ProjectID Prac_no GrossAmount
1 100 75.00
1 102 70.00

Note for prac_no = 100 it adds 50.00 + 25.00 = 75.00

many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:25:13

[code]
SELECT TOP (100) PERCENT dbo.tblProjects.ProjectID, dbo.tblProjectPatients.Prac_no,
SUM(CASE WHEN dbo.tblProjectPatients.PayStatus = 'Full Pay' THEN dbo.tblProjects.FullFee WHEN dbo.tblProjectPatients.PayStatus = 'Part Pay' THEN dbo.tblProjects.PartFee
ELSE 0.00 END) AS AmountPerPatient
FROM dbo.tblProjects INNER JOIN
dbo.tblProjectPatients ON dbo.tblProjects.ProjectID = dbo.tblProjectPatients.ProjectID
GROUP BY dbo.tblProjects.ProjectID, dbo.tblProjectPatients.Prac_no
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources