April 9, 2007 at 10:46 am
I have 2 tables. First table (Table1) has data based on a monthly budget balance. Second table (Table2) is my fiscal date info. In Table1, I generally do have monthly activity and the data from the source will reflect that. But, I may not have activity each month per account. So, months that do not have activity... house '0' in the corresponding field(s). This also goes for the 'MONTH' column. If there is no activity for that specific month, then a '0' shows in the 'Month' column (I know, it sucks, but this data comes from an outside source). I want to be able to update the columns for non-active months with the appropriate MONTH # (i.e. 1,2,3, etc.) and roll forward the prior months budget balance. While leaving active months alone. I have kicked the code around and am not able to get the cursor to loop properly (I don't think). Could someone please show me what I am missing? I'm starting to see stars from looking at this so long. I think I am right on the edge of break-through with it, but I think I am overlooking something small (maybe big... who knows). Here is my code:
DECLARE @data_mochar(2)
DECLARE data_cursor cursor for
select dept, budgetobject, Fiscal_key, MONTH, origbudget, monthlyact from Table1 where rec_type = 'F'
open data_cursor
fetch next from data_cursor
while @@fetch_status <> 0
-- set @data_mo = ''
BEGIN
set @data_mo = (select month from data_cursor)
DECLARE month_cursor cursor for
select DISTINCT(FISCAL_MONTH) from Table2 order by FISCAL_MONTH
open month_cursor
fetch next from month_cursor
while @@fetch_status <> -1
Begin
DECLARE @month char(2)
-- set @data_mo = ''
set @month = (select MONTH from month_cursor)
/*
fetch next from month_cursor -- into #dataob_month
while @@fetch_status < -1
if @month = @data_mo
-- insert into stg_frsom_dat7 (deptfund, buob, frsom_fiscal_key, month, data_ob, data_ma)
select dept, budgetobject, Fiscal_key, @data_mo, origbudget, monthlyact from data_cursor
*/
fetch next from month_cursor -- into #dataob_month
if @month <> @data_mo
-- insert into stg_frsom_dat7 (deptfund, buob, fiscal_key, month, data_ob, data_ma, data_mob)
select dept, budgetobject, Fiscal_key, (@data_mo + 1), origbudget, monthlyact from data_cursor
-- @@fetch_status = 1
end
close month_cursor
deallocate month_cursor
fetch next from data_cursor
end
close data_cursor
deallocate data_cursor
Forgive the mess of everything I have commented out. I've ran through debugging this for a while and just pasted out of frustration. I appreciate any help, anyone can give.
Thankx
April 9, 2007 at 12:19 pm
I think you have complicated things, unless I'm missing some details.
You want to insert into your accumulative table from a source. Did you try
insert into accum_table (fields...)
select (fields1, fieldn) from monthly_table
where not(monthly_table.month in(select accum_table.month from accum_table))
I don't think you need a cursor if the only condition is to insert those months not in the accum table.
April 9, 2007 at 12:28 pm
Actually, there are no records to insert from the source. I am creating the records with the next available 'Month' number (i.e. Month 05 has activity, Month 06 does not). Since there was no activity for Month 06, there is no record in the source for Month 06. I need to add a record for Month 06 and roll the balance of Month 05 to Month 06.
April 9, 2007 at 12:41 pm
First I think you are making things too complicated. Also if there is no activity in Month 06, then you need to add a record for Month 6 from month 5, that means you need to insert a record into a table or update a record in a table. Which table you need to update or insert?
Second please check your syntax for DECLARE CURSOR statement.
DECLARE @Month VARCHAR(2)
DECLARE month_cursor CURSOR FOR
SELECT DISTINCT(FISCAL_MONTH) FROM Table2 ORDER BY FISCAL_MONTH
OPEN month_cursor
FETCH NEXT FROM month_cursor INTO @Month
IF @@FETCH_STATUS = 0
BEGIN
......
FETCH NEXT FROM month_cursor INTO @Month
END
CLOSE month_cursor
DEALLOCATE month_cursor
April 9, 2007 at 12:56 pm
To find the missing month and roll up the budget from previous month.
I did not test this so I did not know if it would work.
SELECT a.dept, a.budgetobject, a.Fiscal_key, b.fiscalmonth,
a.origbudget, a.monthlyact
FROM (SELECT dept, budgetobject, Fiscal_key, [MONTH],
SUM(origbudget) origbudget, SUM(monthlyact) monthlyact
FROM Table1 t1
WHERE rec_type = 'F'
GROUP BY dept, budgetobject, Fiscal_key, [MONTH]) a
INNER JOIN (SELECT DISTINCT(FISCAL_MONTH) fiscalmonth
FROM Table2 t2
LEFT OUTER JOIN Table1 t1
ON t2.FISCAL_MONTH = t1.MONTH
WHERE t1.MONTH IS NULL) b
ON a.MONTH = (b.fiscalmonth - 1)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply