December 6, 2010 at 5:35 am
Hello!
I hope you can help with this as it's driving me insane.
I am trying to work out the number of pupils who have active records with a specific need, for an academic year over a defined period (say 5 years). I am trying to create a matrix(crosstab) to show the following:
Row heading = need eg Autism, Hearing Impairment, Visual Impairment
Column heading = Academic Year eg 2004/2005, 2005/2006, 2006/2007, 2007/2008, 2008/2009
Data = countdistinct on student_id
The data in my query is as follows
Student_id
Statement_Startdate
Statement_Enddate
Need
I also have a table which lists the academic years and the startdate and enddate of the academic year but no relationship exists between the tables in my query and the academic year table.
The problem I have is I can calculate the number of pupils who have a statement_startdate or statement_enddate in each of the academic years but i want pupils to be counted in the academic years between the statement_startdate and statement_enddate also and not in the academic years after the statement_enddate.
I am happy to supply further information if this doesn't make sense.
If you have any ideas even to get me started i would be really grateful.
Many thanks
Kat
December 6, 2010 at 6:02 am
Kat
Please provide table DDL, sample data in the form of INSERT statements and expected results. That way we can visualise your problem properly and give you a tested solution.
Thanks
John
December 6, 2010 at 8:19 am
Hi John
I'm sorry if I have interpreted your request incorrectly. Hopefully this is what you need - apologies if it isn't. I have attached a copy of my query.
My query is fine and returns that data i need - example below:
Parameters: From Academic Year(:startdate) = 2007/2008 (Value = 01/09/2007)
To Academic Year (:Enddate) = 2010/2011 (Value = 31/08/2011)
STUD_ID Primary Need STAT_COMPLETE_DATE STAT_END_DATE
1 Autism 15/09/2007 11/08/2010
2 Autism 25/10/2009 Null
3 Autism 02/01/2009 25/06/2010
4 Hearing Impairment 04/09/2008 Null
An academic Year starts on 1st September and Ends on 31st August
From the example data above I would like the report to show:
2007/2008 2008/2009 2009/2010 2010/2011
Autisim 1 2 3 1
Hearing Impairment 1 1 1
In my head i think the easiset way would be to some how have another column in my query results which has the academic year for each year the statement is active. This may not be possible or might not be the best solution.
Many thanks
Kat
December 6, 2010 at 8:27 am
Kat
None of us are paid to do this and we do it in our own time, so the easier you make it for us to help you, the more likely you are to get a good solution. Please will you provide your table DDL in the form of CREATE TABLE statement(s) and your sample data in the form of INSERT statements. That way, we can go straight to helping you out, instead of having to spend time defining tables and data in our own environment. By the way, I can't read the picture you attached. Please will you post the code so that we can copy and paste it?
By the way, from looking at your expected results, I think you need to use the PIVOT statement.
John
December 7, 2010 at 7:06 am
Hey Kat,
I think you will need to use a case statement to tabulate your results the way you wanted. See BOL on how to use a case statement: http://msdn.microsoft.com/en-US/library/ms181765(v=SQL.90).aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply