December 30, 2012 at 11:08 pm
I'm looking for advice on how to create a SQL Server 2005 query and line chart report for SSRS 2005.
I need to display the peak number of patients assigned to a medical practice each month for a 12 month period based on the end-user selecting a single month and year.
I've previously created a report that displays all patients assigned to the practice for any single month but I’m looking for advice on how to how to produce a resultset that shows the peak number of patients each month for a 12 month period. I thought about creating a query that returns the peak count for each month (based on my previously created report which displays all patients assigned to the practice for any single month) and then use a UNION statement to join all 12 months but I'm sure that isn't the most efficient way to do this. The other challenge with this approach (twelve resultsets combined via a UNION) is that the end-user needs to be able to select any month and year for the parameter and the report needs to display the 12 month period based on the month selected (the month selected would be the last month of the 12 month period).
For the report I’ve previously created that displays all patients assigned to the practice for any single month, the WHERE statement filters the resultset on two fields:
•Start Date - The date the patient was assigned to the practice. This field is never null or blank.
•End Date - The date the patient left the practice. This field can be null or blank as active patients assigned to the practice do not have an End Date. When the patient leaves the practice, the date the patient left is populated in this field.
Using these two fields I can return all patients assigned to the practice during Nov 2012 by looking for patients that meet the following criteria:
•start date prior to 11/30/2012 (using the last day of the month selected ensures patients added mid-month would be included)
AND
•end date is null or blank (indicates the patient is active) OR the end date is between 11/1/2012 -11/30/2012 (returns patients that leave during the month selected)
Regarding the query I need to create for the report that displays the peak count each month for 12 months, I'm looking for advice on how to count patients for each month the patient is assigned to the practice if the patient has been assigned for several months (which applies to most patients). Examples are:
•John Doe has a start date of 6/01/2012 and an End Date of 10/07/2012
•Sally Doe has a start date of 8/4/2012 and no End Date (the patient is still active)
•Jimmy Doe has a start of 7/3/2012 and an End Date of 9/2/2012
Given these examples how would I include John Doe in the peak monthly count each month for May - October, Sally Doe in the peak monthly count for August - December and Jimmy Doe in the peak monthly count for July – Sept if the end-user running the report selected December 2012 as the parameter?
Given the example above and the fact I'm creating a line chart I think the best way to create this report would be a resultset that looks like this:
Patient Name Months Assigned
John Doe June 2012
John Doe July012
John Doe Aug 2012
John Doe Sept 2012
John Doe Oct 2012
Sally Doe Aug 2012
Sally Doe Sept 2012
Sally Doe Oct 2012
Sally Doe Nov 2012
Sally Doe Dec 2012
Jimmy Doe July 2012
Jimmy Doe Aug 2012
Jimmy Doe Sept 2012
From the resultset above I could create another resultset that would count\group on month and year to return the peak count for each month:
June 2012 - 1
July 2012 – 2
Aug 2012 - 3
Sept 2012 - 3
Oct 2012 - 2
Nov 2012 - 1
Dec 2012 - 1
The resultset that displays the peak count for each month would be used to create the line chart (month would be the X axis and the count would be the y axis).
Does this sound like the best approach?
If so, any advice on how to create the resultset that lists each patient and each month they were assigned to the practice would be greatly appreciated. I do not have permissions to create SPs or Functions within the database but I can create temp tables.
I know how to create the peak monthly count query (derived from the query that lists each patient and month assigned) as well as the line chart.
Any advice or help is greatly appreciated.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply