12,294,775 members (65,740 online)
Rate this:
See more:
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,

Posted 31-Aug-12 2:29am
Edited 31-Aug-12 3:18am
v3
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:

## 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!
Rate this:

## 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 ...```
Rate this:

## 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!
v2
aarti meswania 3-Sep-12 1:52am

refer updated solution

Top Experts
Last 24hrsThis month
 OriginalGriff 390 CHill60 330 Dave Kreskowiak 224 Richard MacCutchan 165 ppolymorphe 155
 OriginalGriff 8,795 ppolymorphe 5,362 CHill60 5,260 KARTHIK Bangalore 4,576 Sergey Alexandrovich Kryukov 4,476