Click here to Skip to main content
12,294,775 members (65,740 online)
Rate this:
 
Please Sign up or sign in to vote.
Hi,

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 31-Aug-12 2:29am
Edited 31-Aug-12 3:18am
v3
Comments
ssd_coolguy 31-Aug-12 8:33am
   
can you elaborate more?
__TR__ 31-Aug-12 8:40am
   
The data you have shown, is it the result of your query or is it the table data ?
__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.
ryanb31 31-Aug-12 8:42am
   
Depends on how your SQL currently is. Does it currently return a record if there is no data for that month? If so, use COALESCE. If not, you can join to a derived table or even UNION using SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY somefield) AS eachMonth FROM AnyTableThatHasAtLeast12Records.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.

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!
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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:
SELECT ...
FROM GenerateMonths(date1, date2) a
LEFT OUTER JOIN SomeTable b
ON a.SomeColumn = b.SomeColumn
WHERE ...
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

create a table,

monthList
month 
1    
2     
3   
.
.
.
12    

Now use below query
@year as varchar(4)

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!
Smile | :)
  Permalink  
v2
Comments
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 3 Sep 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100