Inserting a row based on condition

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)

Inserting a row based on condition

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-11-15 : 10:23:01

Question: First and foremost, the SQL is handled dynamically by the SQL Server, therefore some coding in the WHERE clause's will look alittle odd to you - disregard this, as its not the issue.

What I need help with is adding in a row to my output, based upon a summary of (Payments and Adjustments) against the summary of (Rev/Charges). My client would like to insert an additional row assign it a specific number and description and then insert a balance in either the off-setting (Payments and Adjustments) or (Rev/Charges) to balance out.

For example, the total (Rev/Charges) = $380.00 and the total (Payments and Adjustments) = $160.00, therefore an entry of $220.00 was input on the (Payments and Adjustments) side to balance to the (Rev/Charges) side.

[CODE]
SET NOCOUNT ON

--- Bring in the Charges

SELECT
CASE
WHEN pv.PrimaryInsuranceCarriersId IS NULL THEN LEFT(comp.Ledger,2) + '.' + LEFT(doc.Ledger,4) + '.' + LEFT(fac.Ledger,2) + LEFT(fin.Ledger,2)
ELSE LEFT(comp.Ledger,2) + '.' + LEFT(doc.Ledger,4) + '.' + LEFT(fac.Ledger,2) + LEFT(ig.Ledger,2)
END AS [GL Number],
'Rev/Charges' AS [GL Description],
-- comp.Ledger AS [Company Ledger],
-- comp.ListName AS [Company],
-- fac.Ledger AS [Facility Ledger],
-- fac.ListName AS [Facility],
-- doc.Ledger AS [Doctor Ledger],
-- doc.ListName AS [Doctor],
-- ISNULL(ic.ListName,'') AS [Insurance Carrier],
-- ic.Ledger AS [Insurance Carrier Ledger],
-- ISNULL(ig.Name, '')AS [Insurance Group],
-- ig.Ledger AS [Insurance Group Ledger],
-- fin.Ledger AS [Financial Class Ledger],
-- fin.Description AS [Financial Class Name],
-- pp.PatientId AS [Patient ID],
-- pv.TicketNumber AS [Ticket Number],
-- pvp.Code AS [Code],
-- pvp.CPTCode AS [CPTCode],
-- p.Ledger AS [Procedure Ledger],
-- Procs.Ledger AS [Department Ledger],
-- procs.Ledger AS [Transaction Ledger],
-- 'Charge',
SUM(pvp.totalfee) AS [Amount],
b.Name AS [BatchName],
b.Entry AS [Entry]

FROM
PatientVisit pv
INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
INNER JOIN Batch b ON pvp.BatchID = b.BatchID
INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsID = pvpa.PatientVisitProcsID
LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
LEFT JOIN MedLists Procs ON p.DepartmentMId = procs.MedListsId

WHERE
b.entry >= ISNULL(NULL,'1/1/1900')
AND b.entry < DATEADD(DAY,1,ISNULL(NULL,'1/1/3000')
)
AND --Filter on batch
(
('133' IS NOT NULL AND b.BatchID IN (133)) OR
('133' IS NULL)
)

GROUP BY
comp.Ledger,
pv.PrimaryInsuranceCarriersId,
fin.Ledger,
doc.Ledger,
fac.Ledger,
ig.Ledger,
b.Name,
b.Entry

-- Insert Payments and Adjustments

UNION ALL

--- Bring in Adjustment Sum

SELECT
LEFT(comp.Ledger,2) + '.' + '1030.0150' AS [GL Number],
'Adjustment' AS [GL Description],
-- comp.Ledger AS [Company Ledger],
-- comp.ListName AS [Company],
-- fac.Ledger AS [Facility Ledger],
-- fac.ListName AS [Facility],
-- doc.Ledger AS [Doctor Ledger],
-- doc.ListName AS [Doctor],
-- ISNULL(ic.ListName,'') AS [Insurance Carrier],
-- ic.Ledger AS [Insurance Carrier Ledger],
-- ISNULL(ig.Name, '')AS [Insurance Group],
-- ig.Ledger AS [Insurance Group Ledger],
-- fin.Ledger AS [Financial Class Ledger],
-- fin.Description AS [Financial Class Name],
-- pp.PatientId AS [Patient ID],
-- pv.TicketNumber AS [Ticket Number],
-- pvp.Code AS [Code],
-- pvp.CPTCode AS [CPTCode],
-- p.Ledger AS [Procedure Ledger],
-- Procs.Ledger AS [Department Ledger],
-- CASE
-- WHEN t.Action = 'P' THEN pymt.Ledger
-- WHEN t.Action = 'A' THEN adj.Ledger
-- ELSE ''
-- END AS [Transaction Ledger],
-- CASE
-- WHEN t.Action = 'P' THEN 'Payment'
-- WHEN t.Action = 'A' THEN 'Adjustment'
-- ELSE ''
-- END AS [Transaction Description],
SUM(td.amount) AS [Amount],
-- CASE
-- WHEN t.Action = 'P' THEN -td.amount
-- WHEN t.Action = 'A' THEN -td.amount
-- ELSE 0
-- END AS [Amount],
b.Name AS [BatchName],
b.Entry AS [Entry]

FROM
PatientVisit pv
INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
INNER JOIN Batch b ON pm.BatchID = b.BatchID
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
LEFT OUTER JOIN TransactionDistributions td ON t .TransactionsId = td.TransactionsId
LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
LEFT OUTER JOIN MedLists dp ON p.DepartmentMID = dp.MedListsID
LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
LEFT JOIN MedLists Procs ON p.DepartmentMId = procs.MedListsId
LEFT OUTER JOIN (select * from medlists where tablename = 'AdjustmentTypes') adj ON t.ActionTypeMId = adj.MedlistsId
LEFT OUTER JOIN (select * from medlists where tablename = 'PaymentTypes') pymt ON t.ActionTypeMId = pymt.MedlistsId

WHERE
t.Action = 'A'
AND NOT EXISTS (SELECT 1 FROM PaymentMethod pm1 WHERE vt.PaymentMethodId = pm1.PaymentMethodId AND pm1.InsuranceTransfer = 1)
AND b.entry >= ISNULL(NULL,'1/1/1900')
AND b.entry < DATEADD(DAY,1,ISNULL(NULL,'1/1/3000')
)
AND --Filter on batch
(
('133' IS NOT NULL AND b.BatchID IN (133)) OR
('133' IS NULL)
)

GROUP BY
comp.Ledger,
b.Name,
b.Entry

UNION ALL

--- Bring in Payment Sum

SELECT
LEFT(comp.Ledger,2) + '.' + '1030.0150' AS [GL Number],
'Payment' AS [GL Description],
-- comp.Ledger AS [Company Ledger],
-- comp.ListName AS [Company],
-- fac.Ledger AS [Facility Ledger],
-- fac.ListName AS [Facility],
-- doc.Ledger AS [Doctor Ledger],
-- doc.ListName AS [Doctor],
-- ISNULL(ic.ListName,'') AS [Insurance Carrier],
-- ic.Ledger AS [Insurance Carrier Ledger],
-- ISNULL(ig.Name, '')AS [Insurance Group],
-- ig.Ledger AS [Insurance Group Ledger],
-- fin.Ledger AS [Financial Class Ledger],
-- fin.Description AS [Financial Class Name],
-- pp.PatientId AS [Patient ID],
-- pv.TicketNumber AS [Ticket Number],
-- pvp.Code AS [Code],
-- pvp.CPTCode AS [CPTCode],
-- p.Ledger AS [Procedure Ledger],
-- Procs.Ledger AS [Department Ledger],
-- CASE
-- WHEN t.Action = 'P' THEN pymt.Ledger
-- WHEN t.Action = 'A' THEN adj.Ledger
-- ELSE ''
-- END AS [Transaction Ledger],
-- CASE
-- WHEN t.Action = 'P' THEN 'Payment'
-- WHEN t.Action = 'A' THEN 'Adjustment'
-- ELSE ''
-- END AS [Transaction Description],
SUM(td.amount) AS [Amount],
-- CASE
-- WHEN t.Action = 'P' THEN -td.amount
-- WHEN t.Action = 'A' THEN -td.amount
-- ELSE 0
-- END AS [Amount],
b.Name AS [BatchName],
b.Entry AS [Entry]

FROM
PatientVisit pv
INNER JOIN DoctorFacility comp ON pv.CompanyId = comp.DoctorFacilityId
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
LEFT OUTER JOIN Medlists fin ON pv.FinancialClassMId = fin.MedListsId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
INNER JOIN Batch b ON pm.BatchID = b.BatchID
INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId
LEFT OUTER JOIN TransactionDistributions td ON t .TransactionsId = td.TransactionsId
LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
LEFT OUTER JOIN MedLists dp ON p.DepartmentMID = dp.MedListsID
LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
LEFT JOIN MedLists Procs ON p.DepartmentMId = procs.MedListsId
LEFT OUTER JOIN (select * from medlists where tablename = 'AdjustmentTypes') adj ON t.ActionTypeMId = adj.MedlistsId
LEFT OUTER JOIN (select * from medlists where tablename = 'PaymentTypes') pymt ON t.ActionTypeMId = pymt.MedlistsId

WHERE
t.Action = 'P'
AND NOT EXISTS (SELECT 1 FROM PaymentMethod pm1 WHERE vt.PaymentMethodId = pm1.PaymentMethodId AND pm1.InsuranceTransfer = 1)
AND b.entry >= ISNULL(NULL,'1/1/1900')
AND b.entry < DATEADD(DAY,1,ISNULL(NULL,'1/1/3000')
)
AND --Filter on batch
(
('133' IS NOT NULL AND b.BatchID IN (133)) OR
('133' IS NULL)
)

GROUP BY
comp.Ledger,
b.Name,
b.Entry
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources