Distinct Rows and Counting

[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]

Create a formula of:

{Doc ID} & {Fac ID} & {Carrier ID}

Group on the field

Suppress the details and the group footer.

Plce on field in the details, right click it and select Inset->Summary->Count and select for the group level.

-k

 

[jeffsturgeon2002]

Create one formula or three? (Confused)

Am I creating one for Doc ID, one for Fac ID and one Carrier ID? Or are you telling me one formula with "{Doc ID} & {Fac ID} & {Carrier ID}" under the x-2 button?

Jeff

SELECT * FROM users WHERE clue > 0

 

[synapsevampire]

A formula means one, not three, and I don't recall chatting about the X2 button.

Stop listening to "the voices"

Concatenating the fields into one provides the distinct properties you seek.

So create a formula and group on it and follow the other instructions.

For clarification I typo'd the last line, it should be:

Place one field in the details, right click it and select Inset->Summary->Count and select for the group level.


-k