Count Query with multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count Query with multiple tables

Author

Topic

rdoghanna
Starting Member

6 Posts

Posted - 2009-04-21 : 12:41:00

Hi all, I do really well with simple select statements... but the more complicated ones make me buggy still. Since I've been trying without success for 2 days, I could really use some help. Please be very specific as again... simple statments I do very well with... :) Thank you in advance.

My project -

I am trying to pull a count of SERVICETYPE from one table. Which I have working. BUT, my database includes 461 TEST clients - and over 1153 SERCVICETYPE records for those TEST clients - which skew my data. I have to have EXACT data - so I want to EXCLUDE any clients with last name like "test" Additionally, I want to display the description of the service type... which is pulled from a THIRD table.

My tables:

CLIENTS
CLIENTPROGRESSNOTE
SERVICETYPE

My BASIC query - which returns count of ALL client service type:

SELECT
SERVICETYPEKEY,
COUNT (*)
FROM
CLIENTPROGRESSNOTE
WHERE
CLIENTPROGRESSNOTE.VOIDDATE IS NULL AND
CLIENTPROGRESSNOTE.NOSHOW='0' AND
(CLIENTPROGRESSNOTE.SERVICEDATE>='2009-01-01 00:00:00.000' AND
CLIENTPROGRESSNOTE.SERVICEDATE<='2009-03-31 00:00:00.000')
GROUP BY
CLIENTPROGRESSNOTE.SERVICETYPEKEY
ORDER BY
CLIENTPROGRESSNOTE.SERVICETYPEKEY

Now, with this...
1) I need to join to the CLIENTS table (CLIENTPROGRESSNOTE.CLIENTKEY=CLIENTS.ID) and filter
CLIENTS.CLIENTLASTNAME LIKE 'TEST%'.

AND,
2) Would like to attach to the SERVICETYPE table (CLIENTPROGRESSNOTE.SERVICETYPEKEY=SERVICETYPE.ID) to display the TYPEOFSERVICE and DESCRIPTION fields. When I do anything with this now, it seems to exponentially be changing my COUNT ... which I know is clearly wrong!

Right now my data - with TEST patients looks like this:

SERVICETYPEKEY________COUNT
56____________________34017
58____________________11
60____________________1
61____________________38

What I would like it to be is: LESS any TEST PATIENT progressnotes being counted.

DESCRIPTION_______SERVICETYPEKEY______COUNT
ServiceType1____________56____________34017
ServiceType2____________58____________11
ServiceType3____________60____________1
ServiceType4____________61____________38

I need it to connect to the SERVICETYPE table because I have more service types then are being used (and we're talking hundreds here)... and the SERVICETYPEKEY is the PK and is a unidentifiable number unless I go back to the SERVICETYPE table.

I know it's got to be simple and I'm just not putting something in the right spot... please help!!!!!!!!!!

Thank you!

Laura

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-21 : 13:03:58

I would like to see how you JOINed in the serviceType table which resulted in exponentially high counts. This code assumes that there is one servicetype row per ClientProgressNote.servicetypekey


select st.[description]
       ,cpn.servicetypekey
       ,count(*)
from   CLIENTPROGRESSNOTE cpn
inner join clients c 
       on c.[id] = cpn.CLIENTKEY
inner join serviceType st
       on st.[id] = cpn.servicetypekey
where  cpn.VOIDDATE IS NULL
and    cpn.NOSHOW='0' 
and    cpn.SERVICEDATE>='2009-01-01 00:00:00.000'
and    cpn.SERVICEDATE<='2009-03-31 00:00:00.000'
and    c.ClientLastName not like 'test%'
GROUP BY st.[description]
       ,cpn.servicetypekey

Be One with the Optimizer
TG

rdoghanna
Starting Member

6 Posts

Posted - 2009-04-21 : 13:51:54

TG - YOU'RE THE BEST! Thank you... that was EXACTLY what I was looking for! :)
I don't remember the exact syntax I was using... but it was ugly! I had done something about embedding the entire select
statement in another select statement. LOL... I guess... Live and learn!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-21 : 14:31:06

>>TG - YOU'RE THE BEST! Thank you... that was EXACTLY what I was looking for! :)

I so rarely get to hear a woman say that...thank YOU

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources