Hi every,
I did create the following store procedure , but when I execute I get a timeout error and nothing done even I ran it in local db
-----------------------------------------
CREATE PROCEDURE create_mat AS
declare @loop_cnt int,
@max_cnt int,
@cur_month char(6),
@from_month char(6),
@to_month char(6);
select @max_cnt=2
select @loop_cnt = 1
TRUNCATE table F_IMS_Sales_24_Months_MAT
WHILE (@loop_cnt < @max_cnt)
BEGIN
select @cur_month = CONVERT(char(6), DATEADD(month, - (@loop_cnt-1), GETDATE()), 112)
select @from_month = CONVERT(char(6), DATEADD(month, - @loop_cnt, GETDATE()), 112)
select @to_month = CONVERT(char(6), DATEADD(month, - (@loop_cnt+12), GETDATE()), 112)
INSERT INTO F_IMS_Sales_24_Months_MAT
SELECT IMS_Outlet_Code, IMS_Outlet_Type_Code, IMS_Pack_Code, IMS_Brick_Code, @cur_month AS Date, SUM(Sales) AS Sales, SUM(Units) AS Units
FROM F_IMS_Sales_5_Years
WHERE ([Date] >= @from_month) AND ([Date] <= @to_month)
GROUP BY IMS_Outlet_Code, IMS_Outlet_Type_Code, IMS_Pack_Code, IMS_Brick_Code
select @loop_cnt=@loop_cnt+1
END
GO
--------------------------------
Is any problem on the above script.
Thanks in advance for any help
Regards,
luk