July 14, 2008 at 9:48 am
hi friends,
i am writing simple query as below.
select count(total) as total, monthname, type, standard from V_Table
where type = 'failed'
and standard = '5'
and fulldate between DATEADD(mm, -12, '20071231') and DATEADD(mm, 0, '20071231')
group by monthname, type, standard
order by monthname
This query gives output of result for each month for year 2007 by each month. It gives result of any failed student for any month. I cant manipulate table (V_table) to include all month data. I need to write something which can give output for all 12 months of 2007. if there is no failed student then it should give 0.
Current output as below.
it gives output as below
total monthname type standard
1 02/2007 Failed 5
1 07/2007 Failed 5
3 12/2007 Failed 5
expected output should be
total monthname type standard
0 01/2007 Failed 5
1 02/2007 Failed 5
0 03/2007 Failed 5
0 04/2007 Failed 5
0 05/2007 Failed 5
0 06/2007 Failed 5
1 07/2007 Failed 5
0 08/2007 Failed 5
0 09/2007 Failed 5
0 10/2007 Failed 5
0 11/2007 Failed 5
3 12/2007 Failed 5
i would like to manipuate existing query but wondering how can i get each month with record and if not with 0.
thanks.
July 14, 2008 at 10:33 am
Your query needs to include a source of all months. This can take several forms, but should be something that will reliably give you all the dates that you need.
Many applications will include a calendar table with a record for every day for some period like 10 or 20 years. This allows more information to be stored for a day -- e.g. this is/is not a school day, is/is not a holiday etc. I believe that AdventureWorks includes such a table.
It is likely that V_Table in your query contains records for every month in the report range so it might be a candidate for your months. Just create a derived table in the query that selects the months from it and join that back with V_Table for the failures.
A table that can provide a sequence of numbers will also work. There is a system table called spt_values that contains various information under different keys. At least one of those could be used to get the numbers from 1-24 and could be turned into 2 years of months.
Finally (though many would frown on it), you can create a simple table function that will fill a table with dates in a range by running a loop. This is not the most efficient, but it is safe and flexible and not really too bad if this report is not run constantly.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply