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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 getting default value

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 data

it is like

select col1, col2, col3, col4
from 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 data

I tried doing this

SELECT
(
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.col
from (select mth = 'jan' union all select 'feb' union all select 'mar' ...) mth
left join table t
on t.mth = mth.mth

Maybe 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.
Go to Top of Page

Vishakha
Starting Member

40 Posts

Posted - 2006-12-12 : 06:29:23
Thanks,
It worked and this is exactly what I was looking for.
Go to Top of Page
   

- Advertisement -