SQL 2005 - Query Assistance - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 - Query Assistance

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-01-01 : 23:08:52

The code contained with-in my Code Snippet, returns visits where the patient balance < 0 (credits).

What my client has requested is to provide a report that "would give us balances on patient accounts who have an even dollar amount overpaid (credit) to an even dollar amount owed".

For example, if you had two visits with the doctor (PatientVisitId), and on one day you had a $5.00 balance due but on another visit you had a $5.00 credit. The net result may or may not always = $0.00. This is just one example with two off setting balances where the net balance = 0.00.

I will also need it in a scenario like this:

DOS 01/01/2009 - (- $50.00)
DOS 02/01/2009 - $5.00
DOS 03/01/2009 - $10.00
DOS 04/01/2009 - $10.00

The net result for this if you take the 3 balances due would be (- $25.00).

So basically, I need to find all visits first that have a negative balance. Then I need to take that PatientVisitId and have it look at the patients other visits and see if there are any active visits with a balance < 0 to offset this overpayment. I hope I made myself clear, if not, please ask and ill elaborate more. As always, I appreciate any and all assistance i get.

[CODE]
SET NOCOUNT ON

SELECT
dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS [Patient Name] ,
pp.PatientId ,
ppa.PatBalance AS [Total Patient Balance] ,
pv.PatientVisitId ,
pv.TicketNumber ,
ISNULL(CONVERT(VARCHAR(20) , pv.visit , 101) , '') AS [Date Of Service] ,
pva.PatBalance AS [Patient Visit Balance]
FROM
PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
WHERE
pva.PatBalance < 0.00
ORDER BY
[Patient Name]
[/CODE]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-08 : 07:43:05

You may need to give details of table structure with some sample date.
But - would something like this help?

Select * from (
SELECT
dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS [Patient Name] ,
pp.PatientId ,
SUM(ppa.PatBalance) AS [Total] ,
pv.PatientVisitId ,
pv.TicketNumber ,
ISNULL(CONVERT(VARCHAR(20) , pv.visit , 101) , '') AS [Date Of Service] ,
pva.PatBalance AS [Patient Visit Balance]
FROM
PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
INNER JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId
Group by pp.PatientId
)as W where total <0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources