Solved

Average Daily Census by Month in SQL Reporting Services

Posted on 2006-07-03
18
4,110 Views
Last Modified: 2012-01-05
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?
0
Comment
Question by:xmaveric
  • 7
  • 3
  • 3
  • +3
18 Comments
 
LVL 1

Author Comment

by:xmaveric
ID: 17043421
Is there a way I can increase the point value of this question so that it gets a faster response?
0
 
LVL 10

Expert Comment

by:ibost
ID: 17043810
try asking this question (or put a 20-pt pointer question) in the Microsoft SQL channel.

0
 
LVL 1

Author Comment

by:xmaveric
ID: 17044785
Done.  Hopefully it will draw some inquiring (and intelligent) minds.
0
 
LVL 5

Expert Comment

by:MageDribble
ID: 17044795
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
 
LVL 1

Author Comment

by:xmaveric
ID: 17045008
Correct.
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17047397
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
 
LVL 17

Expert Comment

by:Arji
ID: 17056512
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17057131
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
 
LVL 17

Expert Comment

by:Arji
ID: 17059858
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 12

Expert Comment

by:Einstine98
ID: 17061185
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
 
LVL 17

Expert Comment

by:Arji
ID: 17061489
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
 
LVL 1

Author Comment

by:xmaveric
ID: 17069214
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
 
LVL 1

Author Comment

by:xmaveric
ID: 17153771
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
 
LVL 1

Author Comment

by:xmaveric
ID: 17153961
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
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17208351
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
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17208385
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
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 500 total points
ID: 17208393
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
 
LVL 1

Author Comment

by:xmaveric
ID: 17254322
That's it!  It worked perfectly!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to increase the row limit in Jasper Server.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now