Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
SQL 2005 Error. No clue what this means. Any insight is appreciated.
Msg 1205, Level 13, State 56, Line 35
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 1205, Level 13, State 56, Line 35
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
/* Monthly Charge Summary by Insurance/Financial Statistics Update 9/25/2004
Updated 1/5/2007 for different Ledger groupings based upon year */
SET NOCOUNT ON
CREATE TABLE #Summary(
HIVVisit varchar(1),
PatientProfileID int,
PatientFacility varchar(100),
PFProgram varchar(12),
DoctorName varchar(100),
DoctorProgram varchar(12),
Specialty varchar(100),
TypeofService varchar(70),
FacilityName varchar(100),
FinancialClass varchar(100),
PolicyType varchar(100),
Entry datetime,
Visit datetime,
TotalFee money,
TotalAdjustment money,
TotalFQHCCharges money,
TicketNumber varchar(25),
Payments money,
Program varchar(255),
OriginalProgram varchar(12),
PlaceofService varchar(6),
AdjustmentType varchar(75),
Encounter int
)
-- Get All Charges and Adjustments for DOE between dates with all adjustments regardless of date (no FQHC CPT Codes)
INSERT INTO #Summary
SELECT 'N',
pp.PatientProfileID,
ISNULL(pf.ListName , 'No Facility'),
ISNULL(clpf.Ledger , '999'),
d.ListName,
cld.Ledger,
ISNULL(spec.Description , 'None'),
ISNULL(tos.Description , 'None'),
f.ListName,
ISNULL(ic.ListName , 'Self Pay'),
CASE WHEN pt.Code IN ( 'MW' , 'MA' , 'MP' ) THEN 'Medicare'
WHEN pt.Description LIKE 'PPO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Comm%' THEN 'Commercial'
WHEN pt.Description LIKE 'Blue%' THEN 'Commercial'
WHEN pt.Description LIKE 'HMO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Medicaid%' THEN 'Medicaid'
ELSE 'Self Pay'
END,
pvp.DateofEntry,
pv.Visit,
SUM(pvp.TotalFee),
0,
0,
pv.TicketNumber,
0,
CASE WHEN pos.Code LIKE '21%'
OR pos.Code LIKE '22%' THEN ISNULL(cld.Ledger , 'None')
ELSE ISNULL(clf.Ledger , 'None')
END,
ISNULL(clf.Ledger , 'None'),
CASE WHEN pos.Code LIKE '21%' THEN '21/22'
WHEN pos.Code LIKE '22%' THEN '21/22'
ELSE ISNULL(pos.Code , 'None')
END,
'',
CASE WHEN LEFT(q.Description , 4) = 'HCPC'
AND pvp.Units < 0 THEN -1
WHEN LEFT(q.Description , 4) = 'HCPC' THEN 1
ELSE 0
END
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
LEFT OUTER JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT OUTER JOIN MedLists pt ON pt.MedListsId = ic.PolicyTypeMId
LEFT OUTER JOIN MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
LEFT OUTER JOIN MedLists spec ON d.SpecialtyMID = spec.MedListsID
LEFT OUTER JOIN MedLists tos ON pvp.TypeofServiceMID = tos.MedListsID
LEFT OUTER JOIN DoctorFacility pf ON pp.FacilityID = pf.DoctorFacilityID
LEFT OUTER JOIN cusLedgerCode1 clpf ON pf.DoctorFacilityID = clpf.DoctorFacilityID AND clpf.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 cld ON d.DoctorFacilityID = cld.DoctorFacilityID AND cld.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 clf ON f.DoctorFacilityID = clf.DoctorFacilityID AND clf.LastDate = '12/31/2006'
LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
LEFT OUTER JOIN MedLists q ON p.CPTProcedureCodeQualifierMID = q.MedListsID
WHERE (pvp.DateofEntry >= '03/01/2010' AND pvp.DateofEntry < DATEADD(d,1,'03/05/2010' ) )
AND ((LEFT(pvp.CPTCode,3) <> '520' AND SUBSTRING(pvp.CPTCode,1,1) NOT IN ('W','T'))
OR pvp.CPTCode IS NULL) AND pvp.TotalFee <> 0
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY
pp.PatientProfileID,
d.ListName,
spec.Description,
tos.Description,
f.ListName,
ic.ListName,
pt.Description,
pvp.DateofEntry,
pv.Visit,
pv.TicketNumber,
clf.Ledger,
pos.Code,
pt.Code,
pf.ListName,
clpf.Ledger,
cld.Ledger,
q.Description,
pvp.Units
-- Erie wants these amounts netted against Contractual Adjustments 02/09/04
-- Now we need to capture FQHC Charges to Reconcile to Transaction Summary in Centricity 9/25/2004
-- Get All Charges for DOE between dates with all adjustments (just FQHC CPT Codes)
INSERT INTO #Summary
SELECT
'N',
pp.PatientProfileID,
ISNULL(pf.ListName , 'No Facility'),
ISNULL(clpf.Ledger , '999'),
d.ListName,
cld.Ledger,
ISNULL(spec.Description , 'None'),
ISNULL(tos.Description , 'None'),
f.ListName,
ISNULL(ic.ListName , 'FQHC'),
CASE WHEN pt.Code IN ( 'MW' , 'MA' , 'MP' ) THEN 'Medicare'
WHEN pt.Description LIKE 'PPO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Comm%' THEN 'Commercial'
WHEN pt.Description LIKE 'Blue%' THEN 'Commercial'
WHEN pt.Description LIKE 'HMO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Medicaid%' THEN 'Medicaid'
ELSE 'Self Pay'
END,
pvp.DateofEntry,
pv.Visit,
0,
-SUM(pvp.TotalFee),
SUM(pvp.TotalFee),
pv.TicketNumber,
0,
CASE WHEN pos.Code LIKE '21%'
OR pos.Code LIKE '22%' THEN ISNULL(cld.Ledger , 'None')
ELSE ISNULL(clf.Ledger , 'None')
END,
ISNULL(clf.Ledger , 'None'),
CASE WHEN pos.Code LIKE '21%' THEN '21/22'
WHEN pos.Code LIKE '22%' THEN '21/22'
ELSE ISNULL(pos.Code , 'None')
END,
'',
CASE WHEN LEFT(q.Description , 4) = 'HCPC'
AND pvp.Units < 0 THEN -1
WHEN LEFT(q.Description , 4) = 'HCPC' THEN 1
ELSE 0
END
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
LEFT OUTER JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
LEFT OUTER JOIN MedLists pt ON pt.MedListsId = ic.PolicyTypeMId
LEFT OUTER JOIN MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
LEFT OUTER JOIN MedLists spec ON d.SpecialtyMID = spec.MedListsID
LEFT OUTER JOIN MedLists tos ON pvp.TypeofServiceMID = tos.MedListsID
LEFT OUTER JOIN DoctorFacility pf ON pp.FacilityID = pf.DoctorFacilityID
LEFT OUTER JOIN cusLedgerCode1 clpf ON pf.DoctorFacilityID = clpf.DoctorFacilityID AND clpf.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 cld ON d.DoctorFacilityID = cld.DoctorFacilityID AND cld.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 clf ON f.DoctorFacilityID = clf.DoctorFacilityID AND clf.LastDate = '12/31/2006'
LEFT OUTER JOIN Procedures p ON pvp.ProceduresID = p.ProceduresID
LEFT OUTER JOIN MedLists q ON p.CPTProcedureCodeQualifierMID = q.MedListsID
WHERE (pvp.DateofEntry >= '03/01/2010' AND pvp.DateofEntry < DATEADD(d,1,'03/05/2010' ) ) AND
(LEFT(pvp.CPTCode,3) = '520' OR SUBSTRING(pvp.CPTCode,1,1) IN ('W','T')) AND pvp.TotalFee <> 0
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY
pp.PatientProfileID,
d.ListName,
spec.Description,
tos.Description,
f.ListName,
ic.ListName,
pt.Description,
pvp.DateofEntry,
pv.Visit,
pv.TicketNumber,
clf.Ledger,
pos.Code,
pt.Code,
pf.ListName,
clpf.Ledger,
cld.Ledger,
q.Description,
pvp.Units
-- Get all adjustments for DOE based upon Date of Entry for all except FQHC codes
INSERT INTO #Summary
SELECT
'N',
pp.PatientProfileID,
ISNULL(pf.ListName , 'No Facility'),
ISNULL(clpf.Ledger , '999'),
d.ListName,
cld.Ledger,
ISNULL(spec.Description , 'None'),
ISNULL(tos.Description , 'None'),
f.ListName,
ISNULL(icbilled.ListName , 'Self Pay'),
CASE WHEN pt.Code IN ( 'MW' , 'MA' , 'MP' ) THEN 'Medicare'
WHEN pt.Description LIKE 'PPO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Comm%' THEN 'Commercial'
WHEN pt.Description LIKE 'Blue%' THEN 'Commercial'
WHEN pt.Description LIKE 'HMO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Medicaid%' THEN 'Medicaid'
ELSE 'Self Pay'
END,
pm.DateofEntry,
pv.Visit,
0,
SUM(td.Amount),
0,
pv.TicketNumber,
0,
CASE WHEN pos.Code LIKE '21%'
OR pos.Code LIKE '22%' THEN ISNULL(cld.Ledger , 'None')
ELSE ISNULL(clf.Ledger , 'None')
END,
ISNULL(clf.Ledger , 'None'),
CASE WHEN pos.Code LIKE '21%' THEN '21/22'
WHEN pos.Code LIKE '22%' THEN '21/22'
ELSE ISNULL(pos.Code , 'None')
END,
ISNULL(adj.Description , 'No Adjustment Type'),
0
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON pv.DoctorId = d .DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t .VisitTransactionsId
INNER JOIN TransactionDistributions td ON t .TransactionsId = td.TransactionsId
LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
LEFT OUTER JOIN InsuranceCarriers icbilled ON pv.PrimaryInsuranceCarriersID = icbilled.InsuranceCarriersID
LEFT OUTER JOIN MedLists pt ON icbilled.PolicyTypeMID = pt.MedListsID
LEFT OUTER JOIN MedLists spec ON d.SpecialtyMID = spec.MedListsID
LEFT OUTER JOIN MedLists paid ON icbilled.PolicyTypeMID = paid.MedListsID
LEFT OUTER JOIN MedLists tos ON pvp.TypeofServiceMID = tos.MedListsID
LEFT OUTER JOIN MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
LEFT OUTER JOIN DoctorFacility pf ON pp.FacilityID = pf.DoctorFacilityID
LEFT OUTER JOIN Medlists adj ON t.ActionTypeMID = adj.MedListsID
LEFT OUTER JOIN cusLedgerCode1 clpf ON pf.DoctorFacilityID = clpf.DoctorFacilityID AND clpf.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 cld ON d.DoctorFacilityID = cld.DoctorFacilityID AND cld.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 clf ON f.DoctorFacilityID = clf.DoctorFacilityID AND clf.LastDate = '12/31/2006'
WHERE (pm.DateofEntry >= '03/01/2010' AND pm.DateofEntry < DATEADD(d,1,'03/05/2010' ) ) AND
((LEFT(pvp.CPTCode,3) <> '520' AND SUBSTRING(pvp.CPTCode,1,1) NOT IN ('W','T'))
OR pvp.CPTCode IS NULL) AND td.Amount <> 0 AND [Action] = 'A'
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY
pp.PatientProfileID,
d.ListName,
spec.Description,
tos.Description,
f.ListName,
icbilled.ListName,
pt.Description,
pm.DateofEntry,
pv.Visit,
pv.TicketNumber,
clf.Ledger,
pos.Code,
pt.Code,
pf.ListName,
clpf.Ledger,
cld.Ledger,
adj.Description
--Get all adjustments for DOS based upon Date of Entry for FQHC codes
INSERT INTO #Summary
SELECT
'N',
pp.PatientProfileID,
ISNULL(pf.ListName , 'No Facility'),
ISNULL(clpf.Ledger , '999'),
d.ListName,
cld.Ledger,
ISNULL(spec.Description , 'None'),
ISNULL(tos.Description , 'None'),
f.ListName,
ISNULL(icbilled.ListName , 'Self Pay'),
CASE WHEN pt.Code IN ( 'MW' , 'MA' , 'MP' ) THEN 'Medicare'
WHEN pt.Description LIKE 'PPO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Comm%' THEN 'Commercial'
WHEN pt.Description LIKE 'Blue%' THEN 'Commercial'
WHEN pt.Description LIKE 'HMO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Medicaid%' THEN 'Medicaid'
ELSE 'Self Pay'
END,
pm.DateofEntry,
pv.Visit,
0,
SUM(td.Amount),
0,
pv.TicketNumber,
0,
CASE WHEN pos.Code LIKE '21%'
OR pos.Code LIKE '22%' THEN ISNULL(cld.Ledger , 'None')
ELSE ISNULL(clf.Ledger , 'None')
END,
ISNULL(clf.Ledger , 'None'),
CASE WHEN pos.Code LIKE '21%' THEN '21/22'
WHEN pos.Code LIKE '22%' THEN '21/22'
ELSE ISNULL(pos.Code , 'None')
END,
ISNULL(adj.Description , 'No Adjustment Type'),
0
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON pv.DoctorId = d .DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t .VisitTransactionsId
INNER JOIN TransactionDistributions td ON t .TransactionsId = td.TransactionsId
LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
LEFT OUTER JOIN InsuranceCarriers icbilled ON pv.PrimaryInsuranceCarriersID = icbilled.InsuranceCarriersID
LEFT OUTER JOIN MedLists pt ON icbilled.PolicyTypeMID = pt.MedListsID
LEFT OUTER JOIN MedLists spec ON d.SpecialtyMID = spec.MedListsID
LEFT OUTER JOIN MedLists paid ON icbilled.PolicyTypeMID = paid.MedListsID
LEFT OUTER JOIN MedLists tos ON pvp.TypeofServiceMID = tos.MedListsID
LEFT OUTER JOIN MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
LEFT OUTER JOIN DoctorFacility pf ON pp.FacilityID = pf.DoctorFacilityID
LEFT OUTER JOIN Medlists adj ON t.ActionTypeMID = adj.MedListsID
LEFT OUTER JOIN cusLedgerCode1 clpf ON pf.DoctorFacilityID = clpf.DoctorFacilityID AND clpf.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 cld ON d.DoctorFacilityID = cld.DoctorFacilityID AND cld.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 clf ON f.DoctorFacilityID = clf.DoctorFacilityID AND clf.LastDate = '12/31/2006'
WHERE (pm.DateofEntry >= '03/01/2010' AND pm.DateofEntry < DATEADD(d,1,'03/05/2010' ) ) AND
(LEFT(pvp.CPTCode,3) = '520' OR SUBSTRING(CPTCode,1,1) IN ('W','T'))
AND td.Amount <> 0 AND [Action] = 'A'
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY
pp.PatientProfileID,
d.ListName,
spec.Description,
tos.Description,
f.ListName,
icbilled.ListName,
pt.Description,
pm.DateofEntry,
pv.Visit,
pv.TicketNumber,
clf.Ledger,
pos.Code,
pt.Code,
pf.ListName,
clpf.Ledger,
cld.Ledger,
adj.Description
--Now we need to get payments
INSERT INTO #Summary
SELECT
'N',
pp.PatientProfileID,
ISNULL(pf.ListName , 'No Facility'),
ISNULL(clpf.Ledger , '999'),
d.ListName,
cld.Ledger,
ISNULL(spec.Description , 'None'),
ISNULL(tos.Description , 'None'),
f.ListName,
ISNULL(icbilled.ListName , 'Self Pay'),
CASE WHEN pt.Code IN ( 'MW' , 'MA' , 'MP' ) THEN 'Medicare'
WHEN pt.Description LIKE 'PPO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Comm%' THEN 'Commercial'
WHEN pt.Description LIKE 'Blue%' THEN 'Commercial'
WHEN pt.Description LIKE 'HMO%' THEN 'Commercial'
WHEN pt.Description LIKE 'Medicaid%' THEN 'Medicaid'
ELSE 'Self Pay'
END,
pm.DateofEntry,
pv.Visit,
0,
0,
0,
pv.TicketNumber,
SUM(td.Amount),
CASE WHEN pos.Code LIKE '21%'
OR pos.Code LIKE '22%' THEN ISNULL(cld.Ledger , 'None')
ELSE ISNULL(clf.Ledger , 'None')
END,
ISNULL(clf.Ledger , 'None'),
CASE WHEN pos.Code LIKE '21%' THEN '21/22'
WHEN pos.Code LIKE '22%' THEN '21/22'
ELSE ISNULL(pos.Code , 'None')
END,
'',
0
FROM PatientVisit pv
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
INNER JOIN DoctorFacility d ON pv.DoctorId = d .DoctorFacilityId
INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitid
INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
INNER JOIN Transactions t ON vt.VisitTransactionsId = t .VisitTransactionsId
INNER JOIN TransactionDistributions td ON t .TransactionsId = td.TransactionsId
LEFT OUTER JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
LEFT OUTER JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
LEFT OUTER JOIN InsuranceCarriers icbilled ON pv.PrimaryInsuranceCarriersID = icbilled.InsuranceCarriersID
LEFT OUTER JOIN MedLists pt ON icbilled.PolicyTypeMID = pt.MedListsID
LEFT OUTER JOIN MedLists spec ON d.SpecialtyMID = spec.MedListsID
LEFT OUTER JOIN MedLists paid ON icbilled.PolicyTypeMID = paid.MedListsID
LEFT OUTER JOIN MedLists tos ON pvp.TypeofServiceMID = tos.MedListsID
LEFT OUTER JOIN MedLists pos ON pvp.PlaceofServiceMID = pos.MedListsID
LEFT OUTER JOIN DoctorFacility pf ON pp.FacilityID = pf.DoctorFacilityID
LEFT OUTER JOIN cusLedgerCode1 clpf ON pf.DoctorFacilityID = clpf.DoctorFacilityID AND clpf.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 cld ON d.DoctorFacilityID = cld.DoctorFacilityID AND cld.LastDate = '12/31/2006'
LEFT OUTER JOIN cusLedgerCode1 clf ON f.DoctorFacilityID = clf.DoctorFacilityID AND clf.LastDate = '12/31/2006'
WHERE (pm.DateofEntry >= '03/01/2010' AND pm.DateofEntry < DATEADD(d,1,'03/05/2010' ) ) AND
td.Amount <> 0 AND [Action] = 'P'
AND --Filter on doctor
(
(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
GROUP BY
pp.PatientProfileID,
d.ListName,
spec.Description,
tos.Description,
f.ListName,
icbilled.ListName,
pt.Description,
pm.DateofEntry,
pv.Visit,
pv.TicketNumber,
clf.Ledger,
pos.Code,
pt.Code,
pf.ListName,
clpf.Ledger,
cld.Ledger
--Now we need to update those HIV Patients
EXEC cusUpdateHIVDx
DECLARE @ppID int
DECLARE c CURSOR FOR
SELECT DISTINCT(PatientProfileID) AS PatientProfileID
FROM cusCustomControlPatientData
WHERE CustomControlDetailID = 68 AND bDataValue = 1
OPEN c
FETCH NEXT FROM c INTO @ppID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Summary SET HIVVisit = 'Y' , Program = '205'
WHERE PatientProfileID = @ppID
FETCH NEXT FROM c INTO @ppID
END
CLOSE c
DEALLOCATE c
--No need to do Internal Medicine, Department 203 is default for Inpatient and Outpatient
--Now we need to put labels on the programs
UPDATE #Summary SET Program = CASE WHEN Program = '202' THEN '202-Peds'
WHEN Program = '203' THEN '203-Adults'
WHEN Program = '204' THEN '204-MAFC'
WHEN Program = '205' THEN '205-HIV/AIDS'
WHEN Program = '206' THEN '206-SBH'
WHEN Program = '207' THEN '207-MAFCHP'
WHEN Program = '208' THEN '208-Adol'
WHEN Program = '209' THEN '209-MAFCHH'
WHEN Program = '210' THEN '210-CBH'
WHEN Program = '214' THEN '214-SBHWS'
WHEN Program = '215' THEN '215-SBHFZ'
WHEN Program = '216' THEN '216-SBHDD'
WHEN Program = '220' THEN '220-PEDSHP'
WHEN Program = '221' THEN '221-PEDSEHH'
WHEN Program = '223' THEN '223-ADLTEHP'
WHEN Program = '224' THEN '224-WHEHP'
WHEN Program = '225' THEN '225-WHEHH'
ELSE Program END
--Now deliver the records to the report
--Now we need to reclassify DUI Visits
UPDATE #Summary SET Program = '210-DUI' WHERE FinancialClass LIKE 'DUI%'
SELECT
HIVVisit,
PatientProfileID,
PatientFacility,
PFProgram,
DoctorName,
DoctorProgram,
ISNULL(Specialty,'None') AS Specialty,
TypeofService,
FacilityName,
FinancialClass,
Entry,
Visit,
PolicyType,
SUM(TotalFee) AS TotalFee,
SUM(TotalAdjustment) AS TotalAdjustment,
SUM(TotalFQHCCharges) AS TotalFQHCCharges,
TicketNumber,
SUM(Payments) AS Payments,
Program,
OriginalProgram,
PlaceofService,
AdjustmentType,
CASE WHEN SUM(Encounter) >= 1 THEN 1 WHEN SUM(Encounter) < -1 THEN -1 ELSE SUM(Encounter) END AS Encounter
FROM
#Summary
GROUP BY
HIVVisit,
DoctorName,
Specialty,
PatientProfileID,
TypeofService,
FacilityName,
FinancialClass,
Entry,
Visit,
PolicyType,
TicketNumber,
Program,
PlaceofService,
PatientFacility,
PFProgram,
OriginalProgram,
DoctorProgram,
AdjustmentType
ORDER BY
DoctorName,
FacilityName,
Visit,
Entry,
PolicyType,
FinancialClass,
TicketNumber
DROP TABLE #Summary
cyberkiwi
It means that your query has clashed with another one and neither can complete without killing one or the other.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.