rexroxm rexroxm - 26 days ago 9
SQL Question

Generate empty rows using cursor

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

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