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.
/* 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

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
It means that your query has clashed with another one and neither can complete without killing one or the other.
0
cyberkiwiCommented:
Here are some links you can read on
How to reduce: http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
How to troubleshoot now that it has occurred: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

A real live illustration of deadlock
John and Jane need to retrieve a document to update.  The cabinet requires two keys.  John asks for A then B and Jane asks for B.  The office boy gets A for John and B for Jane.  John then asks for B and Jane for A.  The backoffice looks at the key store and cannot find A and B so it waits for the keys to return to fulfil the request.  As you can tell, neither key will return because both holders are waiting for the other to complete their function.  This is a "deadlock".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.