Count of patients and wounds by month

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

General SQL Server Forums

New to SQL Server Programming

Count of patients and wounds by month

Author

Topic

cwildeman
Starting Member

40 Posts

Posted - 2011-11-10 : 08:57:55

Hi,
I am attempting to write a query for a stored procedure for an SSRS report. I have a table called HAPU_Input with AcctNum and ConsultDate where AcctNum is the primary key. I have a second table called HAPU_WoundDetails that has several fields including AcctNum. The same AcctNum can appear more than once if a patient has more than one wound. I want to write a query or queries that will provide a count of patients by month (based on ConsultDate) and a count of wounds by month (again based on ConsultDate). The count of patients would be the count of AcctNum in HAPU_Input and count of wounds would be the count of AcctNum in HAPU_WoundDetails. Can someone help?
Thanks,
Chuck

Chuck W

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-10 : 09:34:20

select convert(varchar(8),h.ConsultDate,112), count(distinct h.AcctNum), count(distinct HAPU_WoundDetails)
from HAPU_Input h
left join HAPU_WoundDetails w
on h.AcctNum = w.AcctNum
group by convert(varchar(8),h.ConsultDate,112)
order by convert(varchar(8),h.ConsultDate,112)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 09:38:21

do like


SELECT DATENAME(mm,DATEADD(mm,DATEDIFF(mm,0,ConsultDate),0)),
COUNT(DISTINCT AcctNum) AS AccntCnt,
COUNT(*) AS WoundCnt
FROM HAPU_Input i
INNER JOIN HAPU_WoundDetails w
ON w.AcctNum = i.AcctNum 
GROUP BY DATENAME(mm,DATEADD(mm,DATEDIFF(mm,0,ConsultDate),0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwildeman
Starting Member

40 Posts

Posted - 2011-11-10 : 10:07:26

Thanks for your response. For nigelrivett's suggestion, I get an error stating "Invalid column name 'HAPU_WoundDetails'" which is the name of the table so I added count(distinct HAPU_WoundDetails.AcctNum) but when I do this I get an error stating "The multi-part identifier "HAPU_WoundDetails.AcctNum" could not be bound.".

For visakh16's suggestion, I get the error message Ambiguous column name 'AcctNum' so I added HAPU_Input.AcctNum but when I do this, I get the error message "The multi-part identifier "HAPU_Input.AcctNum" could not be bound.".

Should I be adding my table names before the field names?
Thanks,
Chuck

Chuck W

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-10 : 10:16:28

sorry

select convert(varchar(8),h.ConsultDate,112), count(distinct h.AcctNum), count(w.AcctNum)
from HAPU_Input h
left join HAPU_WoundDetails w
on h.AcctNum = w.AcctNum
group by convert(varchar(8),h.ConsultDate,112)
order by convert(varchar(8),h.ConsultDate,112)

the left join is to cater for patients who don't have wornds. If you want to exclude tyhem or they don't exist then take out the left.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-10 : 10:22:07

for Visakh16's

SELECT DATENAME(mm,DATEADD(mm,DATEDIFF(mm,0,i.ConsultDate),0)),
COUNT(DISTINCT i.AcctNum) AS AccntCnt,
COUNT(*) AS WoundCnt
FROM HAPU_Input i
INNER JOIN HAPU_WoundDetails w
ON w.AcctNum = i.AcctNum
GROUP BY DATENAME(mm,DATEADD(mm,DATEDIFF(mm,0,i.ConsultDate),0))

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cwildeman
Starting Member

40 Posts

Posted - 2011-11-10 : 11:24:29

Thanks. Both of these suggestions worked. Chuck

Chuck W

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources