Generate empty rows using cursor

Question

I want to generate a yearly report where a user inserts their account number, starting month and year. Using this data I want to generate a temporary table which starts giving user resource usage statistics from the starting month of the selected year till 12 months later.

I am using a cursor to do so. Now the problem arises when a user has not used the resource for a certain month. I still want the temporary table to generate empty columns for those months. How can I do it?

 declare @account_no varchar(max)                             
 declare @resourse_usage int 
 declare @month int
 declare @year int
 declare @month1 int
 declare @year1 int 

declare @tempdatatable table                                  
(    account_no varchar(max),                             
    resourse_usage int, 
    month int,
    year int,                             
)                        

DECLARE array1 cursor for                        
    select account_no,resourse_usage,month,year from tblstats where account_no=1 and month>@month and year=@year
open array1                                
fetch next from array1 into @account_no,@resourse_usage,@month1,@year1
while @@fetch_status=0                                
begin                           

 insert into @tempdatatable                              
 (account_no,resourse_usage,month,year)                              
values                              
 (@account_no,@resourse_usage,@month1,@year1)    
 fetch next from array1 into @account_no,@resourse_usage,@month1,@year1
end                                
close array1                                
deallocate array1 
select * from @tempdatatable

Answer 1

First, there is no reason to use a cursor for this; simply using select statements will allow the optimizer to do its job and, even without any added optimizations, not using a cursor will run faster. SQL is about processing sets of tuples (rows), and there is a lot of smart code that will work for you if you can formulate your problem in terms of sets.

In this case, the solution is to use a group by over the union of two sets. The first set is the one you select in your question (I'm assuming you have the target account # in a variable named @account):

select account_no,resourse_usage,month,year
from tblstats 
where account_no=@account and (
    month >= @month and year = @year or
    month <  @month and year = @year+1
)

The second set is the set of all of the months in the reporting period:

;With Months( Month ) as
(
    Select 1 as Month
    union all
    Select Month + 1
        from Months
        where month < 12
)
select @account as account_no, NULL as resourse_usage, month, 
case when month >= @month then @year else @year+1 end as year
from Months

Bringing this all together, we get:

;With Months( Month ) as
(
    Select 1 as Month
    union all
    Select Month + 1
        from Months
        where month < 12
)
select account_no, max(resourse_usage) as resourse_usage, month, year
from (
    select account_no, resourse_usage, month, year
    from tblstats 
    where account_no=@account and (
        month >= @month and year = @year or
        month <  @month and year = @year+1
    )
    union
    select @account as account_no, NULL as resourse_usage, month, 
    case when month >= @month then @year else @year+1 end as year
    from Months
) G
group by account_no, month, year

Answer 2

Try to declare temp table with months and change statment in cursor:

declare @tblMonths table(month int)
insert into @tblMonths (month) values (1), (2), (3), (4), (5), (6) ,(7), (8), (9), (10), (11), (12)

and cursor statment

select t.account_no,t.resourse_usage, isnull(t.month, m.month) as month, isnull(t.year, @year) 
from tblstats t 
    right join @tblMonths m on t.month = m.month
where t.account_no=1 and t.month>@month and t.year=@year