Average Daily Census by Month in SQL Reporting Services

I am trying to make a report that will show the average daily census for our patients (health care org.)  

This is what the report would look like:

2006
June  238 (this number will be the average number of patients served per day in the month of June)
May  221
April 211
March 205
etc, rolling to 1 year previous

This is the data I have available:  the patient's Start of Care date, and patient's Discharge date

Is it possible to get the information I want?
LVL 1
xmavericAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DireOrbAntConnect With a Mentor Commented:
You also have to drop your temp table or use a table variable (would probably make the query faster)

DECLARE @MyDate AS DateTime
SET  @MyDate = GetDate() - 730
DECLARE @tmpCensus TABLE (CensusCount int, CensusDate datetime, BranchID int)
WHILE @MyDate <= getdate()
BEGIN
     INSERT INTO @tmpCensus (CensusCount, CensusDate, BranchID)
       SELECT    COUNT(DISTINCT patprogram.patid), @MyDate, patprogram.BranchID
       FROM       patprogram INNER JOIN patient ON patprogram.patid = patient.patid
       WHERE     StartofCare <= @MyDate AND (dischgdate > @MyDate OR dischgdate IS NULL) AND (Patient.SchedAreaID BETWEEN 6 AND 18)
       GROUP BY patprogram.BranchID
    SET @MyDate = @MyDate + 1
END

SELECT AVG(censuscount) AS avgcen, DateName(mm, CensusDate) AS cenmonth, DatePart(yyyy, CensusDate) AS centyear, BranchID, DatePart(mm, CensusDate) AS datesort
FROM @tmpCensus
GROUP BY DateName(mm, CensusDate), DatePart(yyyy, CensusDate), BranchID, DatePart(mm, CensusDate)
ORDER BY centyear DESC, datesort DESC
0
 
xmavericAuthor Commented:
Is there a way I can increase the point value of this question so that it gets a faster response?
0
 
ibostCommented:
try asking this question (or put a 20-pt pointer question) in the Microsoft SQL channel.

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
xmavericAuthor Commented:
Done.  Hopefully it will draw some inquiring (and intelligent) minds.
0
 
MageDribbleCommented:
if a patient is held over months do they count for each month?  Ex/ June 30 started and discharged July 1 does this count as a +1 for June and a +1 for July?
0
 
xmavericAuthor Commented:
Correct.
0
 
Einstine98Commented:
For the period you are looking for

1. Get the number of days the patient spent within a month
2. get the average of patient days for a given month...

This will have to be a set of queries with some temporary tables to hold the data for you.....

If you start something (sql code) I can help you out...

this will take a lot of processing
0
 
ArjiCommented:
006
June  238 (this number will be the average number of patients served per day in the month of June)
May  221
April 211
March 205
etc, rolling to 1 year previous

This is the data I have available:  the patient's Start of Care date, and patient's Discharge date

Use this as the query for the report:
Select *,  Count([Start of Care Date]) as NumberOfPatients from PatientsTable Order by [Start of Care]

If you have a PatientID in the PatientsTable then use that for the Count() function:
Select *,  Count(PatientID) as NumberOfPatients from PatientsTable Order by [Start of Care]

Then in the report design right-click on the report and select Grouping and Sorting.  In the first Field/Expression line select the [Start of Care date] field and sort Descending.  Then in the properties below the selected field select Group Header = Yes and Keep Together = Whole Group from their respective pulldown lists.  Then in the report, place the [Start of Care date] field in the newly created header for the [Start of Care date]. Then place the field NumberOfPatients in the detail portion of the report.  This should be close if not exactly what you are asking about.
0
 
Einstine98Commented:
Arji,

You are using an aggregate function (count) without any grouping by... as far as I know this does not work...

Also your count is simplistic not taking into account if a patient entered for a day and another entered for 30 days... how do you calculate the average daily visit then?
0
 
ArjiCommented:
Try running something like this in query designer: SELECT COUNT(fldProposal_ID) AS Expr1 FROM tblProposals. It works fine for me.
There's nothing wrong with a starting point.  It may stimulate ideas for the solution.  Instead of commenting on my code, you should be contributing to the solution.  That's what this site is about....cooperation.  I don't think my report design technique was bad either.  When I receive a response from xmaveric, I can adjust the code to fit the need.  I just presented a brainstormed idea that has potential to work even if it takes a few tweaks.  This encourages folks to think more deeply about their issue and often times, they'll come up with a solution on their with a little help from their friends.  I've collected a lot of points doing just what I am doing.
0
 
Einstine98Commented:
Hi, I hope you are not taking this negatively... and personally it's not the points that I'm after...rather, giving accurate helpful advice when I can....

SELECT count(*) FROM TABLE : correct 100%
SELECT *, Count(*) FROM TABLE : Will not work

SELECT Field1, Count(*) FROM TABLE GROUP BY FIELD1 : Will work....

0
 
ArjiCommented:
Great! That's a good point.  I'd like to hear more from xmaveric.  I would love to see the fields in the patients table to see if there are other fields we can work with like maybe a PatientID.  I'm thinking we will also need to divide the number of monthly patients by the number of days in a particular month but I would appreciate more detail from xmaveric.  I have a function that quickly determines the number of days of any month.  To get the average we can use Avg() function but we won't get far without more info.
0
 
xmavericAuthor Commented:
Thank you for the direction.  I will give this a try tomorrow when I am back in the office and post my results here.
0
 
xmavericAuthor Commented:
Ok...here is what I have so far:

DECLARE @MyDate AS DateTime DECLARE @val1 AS int DECLARE @val2 AS datetime DECLARE @CenDate AS datetime DECLARE @val3 AS int
SET  @MyDate = GetDate() - 730 CREATE TABLE #tmpCensus(CensusCount int, CensusDate datetime, BranchID int) WHILE @MyDate <= getdate()
BEGIN
      SELECT     @val1 = COUNT(DISTINCT patprogram.patid), @val2 = @MyDate, @val3 = patprogram.BranchID
      FROM         patprogram INNER JOIN patient ON patprogram.patid = patient.patid
      WHERE     StartofCare <= @MyDate AND (dischgdate > @MyDate OR
                              dischgdate IS NULL) AND (Patient.SchedAreaID = '17' OR
                              Patient.SchedAreaID = '16' OR
                              Patient.SchedAreaID = '15' OR
                              Patient.SchedAreaID = '14' OR
                              Patient.SchedAreaID = '9' OR
                              Patient.SchedAreaID = '8' OR
                              Patient.SchedAreaID = '7' OR
                              Patient.SchedAreaID = '18' OR
                              Patient.SchedAreaID = '6' OR
                              Patient.SchedAreaID = '12' OR
                              Patient.SchedAreaID = '13' OR
                              Patient.SchedAreaID = '11' OR
                              Patient.SchedAreaID = '10')
      INSERT  INTO #tmpCensus(CensusCount, CensusDate, BranchID)
VALUES     (@val1, @val2, @val3)
SET           @MyDate = @MyDate + 1 END
      SELECT AVG(censuscount) AS avgcen, DateName(mm, CensusDate) AS cenmonth, DatePart(yyyy, CensusDate) AS centyear, branch, DatePart(mm, CensusDate) AS datesort
      FROM #tmpCensus
      GROUP BY DateName(mm, CensusDate), DatePart(yyyy, CensusDate), BranchID, DatePart(mm, CensusDate)
      ORDER BY centyear DESC, datesort DESC


It has gotten more complicated because I need to break down each month by branch.  Obviously, the query above isn't working because I get this error:

"Column 'patprogram.BranchID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

Note:  The query was working.  I got the info I wanted before adding adding the branch stuff, so the query was working. I just need to figure out how to add a way to add branch into this.
0
 
xmavericAuthor Commented:
Sorry, the query above is inaccurate, this is the current query:

DECLARE @MyDate AS DateTime DECLARE @val1 AS int DECLARE @val2 AS datetime DECLARE @CenDate AS datetime DECLARE @val3 AS int
SET  @MyDate = GetDate() - 730
CREATE TABLE #tmpCensus(CensusCount int, CensusDate datetime, BranchID int) WHILE @MyDate <= getdate()
BEGIN
      SELECT     @val1 = COUNT(DISTINCT patprogram.patid), @val2 = @MyDate, @val3 = patprogram.BranchID
      FROM         patprogram INNER JOIN patient ON patprogram.patid = patient.patid
      WHERE     StartofCare <= @MyDate AND (dischgdate > @MyDate OR
                              dischgdate IS NULL) AND (Patient.SchedAreaID = '17' OR
                              Patient.SchedAreaID = '16' OR
                              Patient.SchedAreaID = '15' OR
                              Patient.SchedAreaID = '14' OR
                              Patient.SchedAreaID = '9' OR
                              Patient.SchedAreaID = '8' OR
                              Patient.SchedAreaID = '7' OR
                              Patient.SchedAreaID = '18' OR
                              Patient.SchedAreaID = '6' OR
                              Patient.SchedAreaID = '12' OR
                              Patient.SchedAreaID = '13' OR
                              Patient.SchedAreaID = '11' OR
                              Patient.SchedAreaID = '10')
      GROUP BY patprogram.BranchID
      INSERT  INTO #tmpCensus(CensusCount, CensusDate, BranchID)
VALUES     (@val1, @val2, @val3)
SET           @MyDate = @MyDate + 1 END
      SELECT AVG(censuscount) AS avgcen, DateName(mm, CensusDate) AS cenmonth, DatePart(yyyy, CensusDate) AS centyear, BranchID, DatePart(mm, CensusDate) AS datesort
      FROM #tmpCensus
      GROUP BY DateName(mm, CensusDate), DatePart(yyyy, CensusDate), BranchID, DatePart(mm, CensusDate)
      ORDER BY centyear DESC, datesort DESC




The problem with this, is that it is only pulling BranchID 4.  (we have branch id's of 1, 2, 3, and 4.)
0
 
DireOrbAntCommented:
I would at least simplify it like this:
DECLARE @MyDate AS DateTime DECLARE @val1 AS int DECLARE @val2 AS datetime DECLARE @CenDate AS datetime DECLARE @val3 AS int
SET  @MyDate = GetDate() - 730
CREATE TABLE #tmpCensus(CensusCount int, CensusDate datetime, BranchID int) WHILE @MyDate <= getdate()
BEGIN
     SELECT     @val1 = COUNT(DISTINCT patprogram.patid), @val2 = @MyDate, @val3 = patprogram.BranchID
     FROM         patprogram INNER JOIN patient ON patprogram.patid = patient.patid
     WHERE     StartofCare <= @MyDate AND (dischgdate > @MyDate OR dischgdate IS NULL) AND (Patient.SchedAreaID BETWEEN 6 AND 18)
     GROUP BY patprogram.BranchID
     INSERT  INTO #tmpCensus(CensusCount, CensusDate, BranchID)
VALUES     (@val1, @val2, @val3)
SET           @MyDate = @MyDate + 1 END
     SELECT AVG(censuscount) AS avgcen, DateName(mm, CensusDate) AS cenmonth, DatePart(yyyy, CensusDate) AS centyear, BranchID, DatePart(mm, CensusDate) AS datesort
     FROM #tmpCensus
     GROUP BY DateName(mm, CensusDate), DatePart(yyyy, CensusDate), BranchID, DatePart(mm, CensusDate)
     ORDER BY centyear DESC, datesort DESC

You could also use:
AND Patient.SchedAreaID IN (6,7,8,9,10,11,12,13,14,15,16,17,18)
If the IDs have gaps in them.
0
 
DireOrbAntCommented:
Is BranchID related to SchedAreaID? If so, do BranchID 1,2 and 3 relates to these SchedAreaID: 6,7,8,9,10,11,12,13,14,15,16,17,18 ?

You can also simplify the whole thing by bypassing the val vars like:
DECLARE @MyDate AS DateTime
SET  @MyDate = GetDate() - 730
CREATE TABLE #tmpCensus(CensusCount int, CensusDate datetime, BranchID int) WHILE @MyDate <= getdate()
BEGIN
     INSERT  INTO #tmpCensus(CensusCount, CensusDate, BranchID)
       SELECT     COUNT(DISTINCT patprogram.patid), @MyDate, patprogram.BranchID
       FROM       patprogram INNER JOIN patient ON patprogram.patid = patient.patid
       WHERE     StartofCare <= @MyDate AND (dischgdate > @MyDate OR dischgdate IS NULL) AND (Patient.SchedAreaID BETWEEN 6 AND 18)
       GROUP BY patprogram.BranchID
    SET @MyDate = @MyDate + 1
END

SELECT AVG(censuscount) AS avgcen, DateName(mm, CensusDate) AS cenmonth, DatePart(yyyy, CensusDate) AS centyear, BranchID, DatePart(mm, CensusDate) AS datesort
FROM #tmpCensus
GROUP BY DateName(mm, CensusDate), DatePart(yyyy, CensusDate), BranchID, DatePart(mm, CensusDate)
ORDER BY centyear DESC, datesort DESC
0
 
xmavericAuthor Commented:
That's it!  It worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.