SELECT DISTINCT	'JBC' AS ClientCode,
		PaymentDetail.PatientNumber,
		Procedures.CaseNumber,
		COALESCE(Procedures.TicketNumber, Procedures.PatientNumber + CONVERT(CHAR(8), Procedures.DateOfService, 112) AS ClaimNumber,
		PaymentDetail.PaymentCounter,
		PaymentDetail.TransAmount,
		Payments.PaymentDate,
		PaymentDetail.AccountingDate,
		PaymentDetail.TransDate,
		payments.PaymentType + '-' + Adjustments.AdjustmentCode AS CombinedPmtType,
		Payments.PaymentType,
		Payments.PaymentCode,
		Adjustments.AdjustmentCode,
		PaymentCodes.BriefDescription,
		PaymentCodes.LongDescription,
		Payments.CarrierCode,
		Payments.Remarks,
		Procedures.ProcedureCode,
		Procedures.DateOfService,
		PaymentDetail.DetailCounter
FROM		PaymentDetail
LEFT JOIN	Procedures ON Procedures.Counter = PaymentDetail.PaymentCounter
			AND Procedures.AccountingDate = PaymentDetail.AccountingDate
			AND Procedures.PatientNumber = PaymentDetail.PatientNumber
			AND Procedures.CaseNumber = 0
LEFT JOIN	Payments ON Payments.AccountingDate = PaymentDetail.TransDate
			AND Payments.Counter = PaymentDetail.TransCounter
			AND Payments.PatientNumber = PaymentDetail.PatientNumber
LEFT JOIN	PaymentCodes ON PaymentCodes.PaymentCode = Payments.PaymentCode
LEFT JOIN	Adjustments ON Adjustments.AccountingDate = PaymentDetail.TransDate
			AND Adjustments.PatientNumber = PaymentDetail.PatientNumber
LEFT JOIN	AdjustmentCodes ON AdjustmentCodes.AdjustmentCode = Adjustments.AdjustmentCode
WHERE		PaymentDetail.TransAmount <> 0
		AND Payments.PaymentType IS NULL
		AND	(
				PaymentDetail.DetailCounter = 2
			OR
				PaymentDetail.DetailCounter = 5
				AND AdjustmentCodes.InsuranceIndicator = 'N'
			)

quote:

---

Originally posted by crever

The only advice I can offer you on this is make sure you're putting the most selective elements of the where clause first. Then the rest of the conditions will have fewer rows to pick through.

---