Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Vishakha
Starting Member
40 Posts |
Posted - 2006-12-12 : 06:04:20
|
I have a table that I am querying for each month.my query is working fine for the months that has some datait is likeselect col1, col2, col3, col4from table I am getting the data for the months for which there is data which is expected, now I want to modify this query and want my query to output zeros for the months there is no dataI tried doing thisSELECT ( case when tableid = (@repyear + 1) then 'jan' when tableid = (@repyear + 2) then 'feb' when tableid = (@repyear + 3) then 'mar' when tableid = (@repyear + 4) then 'apr' when tableid = (@repyear + 5) then 'may' when tableid = (@repyear + 6) then 'jun' when tableid = (@repyear + 7) then 'jul' when tableid = (@repyear + 8) then 'aug' when tableid = (@repyear + 9) then 'sep' when tableid = (@repyear + 10) then 'oct' when tableid = (@repyear + 11) then 'nov' when tableid = (@repyear + 12) then 'dec' end ) as repmonth, col1, col2, col3, col4 from table where month = 'jan'It is still returning the data from rows that had data. I expected it to return a row for jan and nulls in all the columns as this month doesn't have any data. Then I would have checked for null and set the default value.Please let me know what I am doing incorrectly |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-12-12 : 06:11:27
|
Not sure what you are trying to do wit hthe query but you need a tally table which contains all the months and left join to your table to get the data.select mth.mth ,t.colfrom (select mth = 'jan' union all select 'feb' union all select 'mar' ...) mthleft join table ton t.mth = mth.mthMaybe you also need to group by the month and aggregate the results but that depends on your data.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Vishakha
Starting Member
40 Posts |
Posted - 2006-12-12 : 06:29:23
|
Thanks,It worked and this is exactly what I was looking for. |
|
|
|
|
|