Distinct Rows

[jeffsturgeon2002]

In my Report (with the SQL code used below), I am pulling to this report the Doctor ID, the Facility ID and the Carrier ID.

What I have occuring right now is I am getting

Doc ID Fac ID Carrier ID
7 3 50
7 3 50
7 3 50

and so on .....

I want to condense them if all 3 match to one row and a count of how many occurances out beside it. So on this, I would have one row and a count of 3.

I hope this makes sense.


SQL CODE

SET NOCOUNT ON

SELECT DISTINCT
	pv.TicketNumber, pv.visit AS DOS, doc.dotid AS Doctor,fac.dotid AS facility, ic.dotid AS CarrierID, 
	pvp.code AS [CPT Code], ISNULL(mod.code,'') AS Modifier,pvpa.InsPayment + pvpa.PatPayment AS Payments, 
	pvpa.InsAdjustment + pvpa.PatAdjustment AS Adjustments, pvp.fee AS Charges 
	

FROM PatientVisit pv 	
	INNER JOIN DoctorFacility doc ON pv.doctorid = doc.doctorfacilityid
	INNER JOIN DoctorFacility fac ON pv.facilityid = fac.DoctorFacilityId
	LEFT JOIN InsuranceCarriers ic ON ic.InsuranceCarriersId = pv.PrimaryInsuranceCarriersId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId 
	LEFT JOIN MedLists mod ON pvp.Modifier1MID = mod.MedListsID 
	INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsID = pvpa.PatientVisitProcsID 

WHERE pvp.fee >= 0
	AND --- Filter on DOS
	(
	pv.visit >= ISNULL('01/01/2003','1/1/1900') AND pv.visit < dateadd(d, 1, ISNULL('01/31/2003','1/1/3000')) 
	)
ORDER BY pv.visit

Jeff

SELECT * FROM users WHERE clue > 0

 

[synapsevampire]

I answered your other post, please don't cross post the same question.

-k