Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Average Daily Census by Month in SQL Reporting Services

Posted on 2006-07-03
18
4,154 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to Parse a Database Field With Multiple Values Into Separate Columns 3 550
SSRS Row visibility 2 364
web based reporting not a saas 9 62
CRM 2011 Missing Fields in Dataset 13 91
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
How to increase the row limit in Jasper Server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

861 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