Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

CSS
month year    Date                    percentage        Total
7   2012    2012-07-31 23:59:59.000 2.28876597367919    90.5969864581347
8   2012    2012-08-31 23:59:59.000 8.25863055502575    98.8556170131604
12  2012    2012-12-31 23:59:59.000 1.1443829868396     100


If there is no data for the month of sept , oct , nov then we put 0's for all the columns.

How to do this can any one help me to do this,


Thank's in advance....
Posted
Updated 31-Aug-12 3:18am
v3
Comments
ssd_coolguy 31-Aug-12 8:33am    
can you elaborate more?
[no name] 31-Aug-12 8:36am    
i want to display monthly report by using line Graph.
For that i display all the months data if there is no data for any month it cosider as zero.But it's not like that that's want i'm asking
__TR__ 31-Aug-12 8:40am    
The data you have shown, is it the result of your query or is it the table data ?
[no name] 31-Aug-12 8:47am    
query only
__TR__ 31-Aug-12 9:19am    
can you post your tables used in the query and some sample data if possible?
And also the query you wrote.

You simply create table with all the months (simply January to December). You can use that to select all month, even when there is no value for that month.

SQL
SELECT m.*, t.* FROM Months m, MyTable t
  WHERE t.year = 2012 
    AND m.month BETWEEN 6 AND 12
    AND t.month = m.month;

You might want to adjust it to your needs but hopefully the basic idea is clear.

Good luck!
 
Share this answer
 
A said you first need to get/create the data for all the rows you need to include in the result set since rows cannot be created from nothing. After that you would have an outer join to join the months to existing rows in your data.

While you can create a table containing the months, another way is to generate date on-the-fly. For example have a look at the following: Using Table-Valued Functions in SQL Server[^]

When using TVF as a 'table', your query could look something like:
SQL
SELECT ...
FROM GenerateMonths(date1, date2) a
LEFT OUTER JOIN SomeTable b
ON a.SomeColumn = b.SomeColumn
WHERE ...
 
Share this answer
 
create a table,

monthList
CSS
month 
1    
2     
3   
.
.
.
12    


Now use below query
@year as varchar(4)

SQL
select a.month, b.year, isnull(b.date,convert(datetime,@year + '-' + a.month + '-01')), isnull(per,0),isnull(total,0) 

from monthlist a
left join yourTbl b on a.month=b.month 

Happy Coding!
:)
 
Share this answer
 
v2
Comments
[no name] 3-Sep-12 0:04am    
for ex in a year i want more than 1 year data means how to do this.
like 2010,2011,2012,2013,2014....
Aarti Meswania 3-Sep-12 1:52am    
refer updated solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900