Group by all and Nulls

[janetb]

I have two tables - xtblProcedureKey: pkey, pdesc AND tblVisit: doctorID, postingDate, procedureKey (I may not need one of the tables - tblProcedureKey)

I want to select about 5 procedureKey from xtblProcedureKey and count the number of times they appear in tblVisit within a month's period, giving me a zero by pkey if the count is null - with the counts grouped by docID and pkey. I can get the counts, but it always omits any pkey not in tblV. (Second part of wish list would be a sum of the counts for just 211-213.)

For example
xTblProcedureKey.procedureKey = '99211','99212','99397', myDate range between 9/1/2004 and 9/30/2004
result should be:

doctorID, procedurekey, ProcedureKeycount
11 211 0
11 212 2
11 213 16
11 395 0
11 397 4
16 211 5
16 212 12
16 213 6
16 395 0
16 397 2

Latest try:
select v.doctorID, x.pkey, isNull(count(v.procedureKey),0) as myCount from fhc.dbo.tblVisit v,
(select p.procedureKey as pKey from fhc.dbo.xTblProcedure p where p.procedureKey in ('99211','99212','99213','99395','99396','99397')) as x
where v.procedureKey=x.pKey
group by v.doctorID, x.pkey
order by v.doctorID, x.pkey

I've tried a bunch of stuff, but no luck. Any help?

[Rawhide]

From your code, I suspect you are using SQL Server, not Access. Here's how I would do it:

Select v.doctorID, p.procedureKey, (Select Count(procedureKey) From tblVisit with(nolock) Where procedureKey = p.procedureKey) As myCount
From xTblProcedureKey As p with(nolock)
Left Join tblVisit As v with(nolock) On v.procedureKey = p.procedureKey
Where p.procedureKey In ('99211','99212','99213','99395','99396','99397')
Order By v.doctorID, p.procedureKey

[janetb]

Yeeeeha! Thanks Rawhide, but it's still not what I want. Your syntax gives me:

doctorID procedureKey myCount
3 99213 54232
9 99211 422
10 99213 54232
24 99213 54232
24 99213 54232

Notice I don't have each of the procedure keys for each doc (null=0) and the mycount totals are the same for each doc by procedure key.

Any other ideas?

[Rawhide]

Then those procedure keys do not exist in xTblProcedureKey if they are not getting returned.

Adjusted Query:

Select Distinct v.doctorID, p.procedureKey, (Select Count(procedureKey) From tblVisit with(nolock) Where procedureKey = p.procedureKey And doctorID = v.doctorID) As myCount
From xTblProcedureKey As p with(nolock)
Left Join tblVisit As v with(nolock) On v.procedureKey = p.procedureKey
Where p.procedureKey In ('99211','99212','99213','99395','99396','99397')
Order By v.doctorID, p.procedureKey

[janetb]

select procedureKey from xTblProcedure where procedureKey In ('99211','99212','99213','99395','99396','99397') gives me:
99211
99212
99213
99395
99396
99397

Your revised query gives me:
3 99213 1
9 99211 1
10 99213 1
24 99213 2
41 99213 1
44 99212 1
44 99213 11

What I need is:
3 99211 0
3 99212 0
3 99213 1
3 99395 0
3 99396 0
3 99397 0
9 99211 1
9 99212 0
etc.

I'm sure the fault is in my lack of clarification. Does this help clear it up? Thanks cowboy.

[Rawhide]

Are you wanting it to return a count for every doctor in vTable even if there is no entry for that doctor with that procedureKey?

[janetb]

xTblProcedure is basically a lookup table that contains all the procedure codes. I'm looking for just certain ones: subSetA

tblVisit or v contains all visits. A particular doctorID and/or procedureKey may be absent.

I would like all doctorID within a given month, with a count for each doctorID of subSetA procedures codes - 0 for a null.

I didn't include the date parameter before 'cause I couldn't get the first part and was working on a progression.

Any better?

Thanks

[Rawhide]

I think that was a yes. I'll have to use a cross join then. It will take quite a bit longer to run:


Select Distinct v.doctorID, p.procedureKey, (Select Count(procedureKey) From tblVisit with(nolock) Where procedureKey = p.procedureKey And doctorID = v.doctorID) As myCount
From xTblProcedureKey As p with(nolock)
Cross Join tblVisit As v with(nolock)
Where p.procedureKey In ('99211','99212','99213','99395','99396','99397')
Order By v.doctorID, p.procedureKey



What is the name of your date field?

[janetb]

postingDate and it's located within tblVisit

This is pretty close except that it appears to give a return of 0 for each of the procedureKeys selected if any code (not one of the subSetA) is found within the month.

Select Distinct v.doctorID, p.procedureKey, (Select Count(procedureKey) From tblVisit with(nolock) Where v.procedureKey = p.procedureKey And doctorID = v.doctorID) As myCount
From xTblProcedure As p with(nolock)
Cross Join tblVisit As v with(nolock)
Where p.procedureKey In ('99211','99212','99213','99395','99396','99397') and (v.postingDate between '9/1/2004' and '9/30/2004')
Order By v.doctorID, p.procedureKey

[Rawhide]

>> This is pretty close except that it appears to give a return of 0 for each of the procedureKeys selected if any code (not one of the subSetA) is found within the month

Isn't that what you want?

[janetb]

Nope. What I need is that if any doctor has any of the 99211...99397 codes (only) within the month, then a count (with null for 0) of each 99211...99397 codes by doctor.

Janet

[Rawhide]

So only show the doctor in the results if he/she has one of the selected codes for that month?

If so, test these two queries to see if either one gives you what you want.

Select Distinct v.doctorID, p.procedureKey, (Select Count(procedureKey) From tblVisit with(nolock) Where procedureKey = p.procedureKey And doctorID = v.doctorID) As myCount
From xTblProcedureKey As p with(nolock)
Cross Join tblVisit As v with(nolock)
Where p.procedureKey In ('99211','99212','99213','99395','99396','99397')
And v.procedureKey In ('99211','99212','99213','99395','99396','99397')
And v.postingDate between '9/1/2004' and '9/30/2004'
Order By v.doctorID, p.procedureKey



Select Distinct v.doctorID, p.procedureKey, (Select Count(procedureKey) From tblVisit with(nolock) Where procedureKey = p.procedureKey And doctorID = v.doctorID) As myCount
From xTblProcedureKey As p with(nolock)
Cross Join tblVisit As v with(nolock)
Where p.procedureKey In ('99211','99212','99213','99395','99396','99397')
And Exists(Select 1 From tblVisit with(nolock) Where procedureKey = p.procedureKey And doctorID = v.doctorID)
And v.postingDate between '9/1/2004' and '9/30/2004'
Order By v.doctorID, p.procedureKey

[janetb]

Haven't seen many folks use the "with(nolock)" - this because it's Access?

Thanks for the cross join tip, too. I haven't seen it much in forum postings.

Round 'em up, pard! First one is a Brahma Bull. Thanks so much, but there's just one more thing I gotta know.

Rawhide as in: Rowdy Yates, Mushy Mushgrove, or Toothless?

Really, thank you for all of your time.

[Rawhide]

That's T-SQL code for SQL Server, not Access. I always use the nolock optimizer hint for my select queries.

Rawhide as in my favorite scene in the movie The Blues Brothers. I am a proud native Texan, however.

[janetb]

Oops sh!%, Tex. This works great in sql query analyzer, but won't work in Access (my front-end so the co-worker can have those pretty reports).

According to the help, you can't do full outer joins (cross joins?) in Access. So, my only alternative is to create a new table each month on the sql side, populate it, run my report in Access, then delete the report?

Seems goofy, but might be my only solution?