dabdowb
asked on
How to setup month and year in stored procedure to react to current date
The title is a bit misleading, but here we go. I have stored procedures that are inside of DTS packages (soon to be moved to SSIS packages) where it looks to a date to identify where to load certain data points in the final table. I have supplied the code below to help understand the scenario. Really kind of blowing out columnar data to tabular data within SQL.
Anyway, here is an example statement: exec sp_logility_load_demand_lv
So as you can see, the last part of the call to the stored procedure is the date 'yyyy-mm-dd', but the day value is ALWAYS to be defaulted to 01, the first day of the current month. I would like to somehow make this call statement in a fashion that would simply check the system date to automatically load the correct values for current month and year.
I am sure it can be done, but I am new to SQL, and dates seem to elude me.
Help?!?
Thanks
Anyway, here is an example statement: exec sp_logility_load_demand_lv
So as you can see, the last part of the call to the stored procedure is the date 'yyyy-mm-dd', but the day value is ALWAYS to be defaulted to 01, the first day of the current month. I would like to somehow make this call statement in a fashion that would simply check the system date to automatically load the correct values for current month and year.
I am sure it can be done, but I am new to SQL, and dates seem to elude me.
Help?!?
Thanks
USE [demand_plan]
GO
/****** Object: StoredProcedure [dbo].[sp_logility_load_demand_lvl1] Script Date: 12/28/2009 13:11:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_logility_load_demand_lvl1]
@target_month_date smalldatetime
as
truncate table rep_logility_demand_lvl1
declare
@dmnd01 int,
@dmnd02 int,
@dmnd03 int,
@dmnd04 int,
@dmnd05 int,
@dmnd06 int,
@dmnd07 int,
@dmnd08 int,
@dmnd09 int,
@dmnd10 int,
@dmnd11 int,
@dmnd12 int,
@dmnd13 int,
@dmnd14 int,
@dmnd15 int,
@dmnd16 int,
@dmnd17 int,
@dmnd18 int,
@dmnd19 int,
@dmnd20 int,
@dmnd21 int,
@dmnd22 int,
@dmnd23 int,
@dmnd24 int,
@dmnd25 int,
@dmnd26 int,
@dmnd27 int,
@dmnd28 int,
@dmnd29 int,
@dmnd30 int,
@dmnd31 int,
@dmnd32 int,
@dmnd33 int,
@dmnd34 int,
@dmnd35 int,
@dmnd36 int
set @dmnd01 = year(dateadd(month, -1, @target_month_date))*100 + month(dateadd(month, -1, @target_month_date))
set @dmnd02 = year(dateadd(month, -2, @target_month_date))*100 + month(dateadd(month, -2, @target_month_date))
set @dmnd03 = year(dateadd(month, -3, @target_month_date))*100 + month(dateadd(month, -3, @target_month_date))
set @dmnd04 = year(dateadd(month, -4, @target_month_date))*100 + month(dateadd(month, -4, @target_month_date))
set @dmnd05 = year(dateadd(month, -5, @target_month_date))*100 + month(dateadd(month, -5, @target_month_date))
set @dmnd06 = year(dateadd(month, -6, @target_month_date))*100 + month(dateadd(month, -6, @target_month_date))
set @dmnd07 = year(dateadd(month, -7, @target_month_date))*100 + month(dateadd(month, -7, @target_month_date))
set @dmnd08 = year(dateadd(month, -8, @target_month_date))*100 + month(dateadd(month, -8, @target_month_date))
set @dmnd09 = year(dateadd(month, -9, @target_month_date))*100 + month(dateadd(month, -9, @target_month_date))
set @dmnd10 = year(dateadd(month, -10, @target_month_date))*100 + month(dateadd(month, -10, @target_month_date))
set @dmnd11 = year(dateadd(month, -11, @target_month_date))*100 + month(dateadd(month, -11, @target_month_date))
set @dmnd12 = year(dateadd(month, -12, @target_month_date))*100 + month(dateadd(month, -12, @target_month_date))
set @dmnd13 = year(dateadd(month, -13, @target_month_date))*100 + month(dateadd(month, -13, @target_month_date))
set @dmnd14 = year(dateadd(month, -14, @target_month_date))*100 + month(dateadd(month, -14, @target_month_date))
set @dmnd15 = year(dateadd(month, -15, @target_month_date))*100 + month(dateadd(month, -15, @target_month_date))
set @dmnd16 = year(dateadd(month, -16, @target_month_date))*100 + month(dateadd(month, -16, @target_month_date))
set @dmnd17 = year(dateadd(month, -17, @target_month_date))*100 + month(dateadd(month, -17, @target_month_date))
set @dmnd18 = year(dateadd(month, -18, @target_month_date))*100 + month(dateadd(month, -18, @target_month_date))
set @dmnd19 = year(dateadd(month, -19, @target_month_date))*100 + month(dateadd(month, -19, @target_month_date))
set @dmnd20 = year(dateadd(month, -20, @target_month_date))*100 + month(dateadd(month, -20, @target_month_date))
set @dmnd21 = year(dateadd(month, -21, @target_month_date))*100 + month(dateadd(month, -21, @target_month_date))
set @dmnd22 = year(dateadd(month, -22, @target_month_date))*100 + month(dateadd(month, -22, @target_month_date))
set @dmnd23 = year(dateadd(month, -23, @target_month_date))*100 + month(dateadd(month, -23, @target_month_date))
set @dmnd24 = year(dateadd(month, -24, @target_month_date))*100 + month(dateadd(month, -24, @target_month_date))
set @dmnd25 = year(dateadd(month, -25, @target_month_date))*100 + month(dateadd(month, -25, @target_month_date))
set @dmnd26 = year(dateadd(month, -26, @target_month_date))*100 + month(dateadd(month, -26, @target_month_date))
set @dmnd27 = year(dateadd(month, -27, @target_month_date))*100 + month(dateadd(month, -27, @target_month_date))
set @dmnd28 = year(dateadd(month, -28, @target_month_date))*100 + month(dateadd(month, -28, @target_month_date))
set @dmnd29 = year(dateadd(month, -29, @target_month_date))*100 + month(dateadd(month, -29, @target_month_date))
set @dmnd30 = year(dateadd(month, -30, @target_month_date))*100 + month(dateadd(month, -30, @target_month_date))
set @dmnd31 = year(dateadd(month, -31, @target_month_date))*100 + month(dateadd(month, -31, @target_month_date))
set @dmnd32 = year(dateadd(month, -32, @target_month_date))*100 + month(dateadd(month, -32, @target_month_date))
set @dmnd33 = year(dateadd(month, -33, @target_month_date))*100 + month(dateadd(month, -33, @target_month_date))
set @dmnd34 = year(dateadd(month, -34, @target_month_date))*100 + month(dateadd(month, -34, @target_month_date))
set @dmnd35 = year(dateadd(month, -35, @target_month_date))*100 + month(dateadd(month, -35, @target_month_date))
set @dmnd36 = year(dateadd(month, -36, @target_month_date))*100 + month(dateadd(month, -36, @target_month_date))
insert into rep_logility_demand_lvl1(target_date, FCST_ID,
DMND01, DMND02, DMND03, DMND04, DMND05, DMND06,
DMND07, DMND08, DMND09, DMND10, DMND11, DMND12,
DMND13, DMND14, DMND15, DMND16, DMND17, DMND18,
DMND19, DMND20, DMND21, DMND22, DMND23, DMND24,
DMND25, DMND26, DMND27, DMND28, DMND29, DMND30,
DMND31, DMND32, DMND33, DMND34, DMND35, DMND36
)
select @target_month_date,
FCST_ID,
sum(case when FCST_YR_PRD = @dmnd01 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd02 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd03 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd04 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd05 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd06 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd07 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd08 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd09 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd10 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd11 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd12 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd13 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd14 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd15 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd16 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd17 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd18 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd19 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd20 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd21 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd22 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd23 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd24 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd25 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd26 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd27 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd28 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd29 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd30 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd31 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd32 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd33 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd34 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd35 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd36 then DMD_ACTL_QTY else 0 end)
from logex_demand_36_lvl1
group by FCST_ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not sure how you tried that, please copy and paste the exact code that will work
USE [demand_plan]
GO
/****** Object: StoredProcedure [dbo].[sp_logility_load_de
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_logility_load_de
as
truncate table rep_logility_demand_lvl1
declare
@dmnd01 int,
@dmnd02 int,
@dmnd03 int,
@dmnd04 int,
@dmnd05 int,
@dmnd06 int,
@dmnd07 int,
@dmnd08 int,
@dmnd09 int,
@dmnd10 int,
@dmnd11 int,
@dmnd12 int,
@dmnd13 int,
@dmnd14 int,
@dmnd15 int,
@dmnd16 int,
@dmnd17 int,
@dmnd18 int,
@dmnd19 int,
@dmnd20 int,
@dmnd21 int,
@dmnd22 int,
@dmnd23 int,
@dmnd24 int,
@dmnd25 int,
@dmnd26 int,
@dmnd27 int,
@dmnd28 int,
@dmnd29 int,
@dmnd30 int,
@dmnd31 int,
@dmnd32 int,
@dmnd33 int,
@dmnd34 int,
@dmnd35 int,
@dmnd36 int
declare @target_month_date smalldatetime
SET @target_month_date = DATEADD(month, DATEDIFF(month, '', getdate() ) , '')
set @dmnd01 = year(dateadd(month, -1, @target_month_date))*100 + month(dateadd(month, -1, @target_month_date))
set @dmnd02 = year(dateadd(month, -2, @target_month_date))*100 + month(dateadd(month, -2, @target_month_date))
set @dmnd03 = year(dateadd(month, -3, @target_month_date))*100 + month(dateadd(month, -3, @target_month_date))
set @dmnd04 = year(dateadd(month, -4, @target_month_date))*100 + month(dateadd(month, -4, @target_month_date))
set @dmnd05 = year(dateadd(month, -5, @target_month_date))*100 + month(dateadd(month, -5, @target_month_date))
set @dmnd06 = year(dateadd(month, -6, @target_month_date))*100 + month(dateadd(month, -6, @target_month_date))
set @dmnd07 = year(dateadd(month, -7, @target_month_date))*100 + month(dateadd(month, -7, @target_month_date))
set @dmnd08 = year(dateadd(month, -8, @target_month_date))*100 + month(dateadd(month, -8, @target_month_date))
set @dmnd09 = year(dateadd(month, -9, @target_month_date))*100 + month(dateadd(month, -9, @target_month_date))
set @dmnd10 = year(dateadd(month, -10, @target_month_date))*100 + month(dateadd(month, -10, @target_month_date))
set @dmnd11 = year(dateadd(month, -11, @target_month_date))*100 + month(dateadd(month, -11, @target_month_date))
set @dmnd12 = year(dateadd(month, -12, @target_month_date))*100 + month(dateadd(month, -12, @target_month_date))
set @dmnd13 = year(dateadd(month, -13, @target_month_date))*100 + month(dateadd(month, -13, @target_month_date))
set @dmnd14 = year(dateadd(month, -14, @target_month_date))*100 + month(dateadd(month, -14, @target_month_date))
set @dmnd15 = year(dateadd(month, -15, @target_month_date))*100 + month(dateadd(month, -15, @target_month_date))
set @dmnd16 = year(dateadd(month, -16, @target_month_date))*100 + month(dateadd(month, -16, @target_month_date))
set @dmnd17 = year(dateadd(month, -17, @target_month_date))*100 + month(dateadd(month, -17, @target_month_date))
set @dmnd18 = year(dateadd(month, -18, @target_month_date))*100 + month(dateadd(month, -18, @target_month_date))
set @dmnd19 = year(dateadd(month, -19, @target_month_date))*100 + month(dateadd(month, -19, @target_month_date))
set @dmnd20 = year(dateadd(month, -20, @target_month_date))*100 + month(dateadd(month, -20, @target_month_date))
set @dmnd21 = year(dateadd(month, -21, @target_month_date))*100 + month(dateadd(month, -21, @target_month_date))
set @dmnd22 = year(dateadd(month, -22, @target_month_date))*100 + month(dateadd(month, -22, @target_month_date))
set @dmnd23 = year(dateadd(month, -23, @target_month_date))*100 + month(dateadd(month, -23, @target_month_date))
set @dmnd24 = year(dateadd(month, -24, @target_month_date))*100 + month(dateadd(month, -24, @target_month_date))
set @dmnd25 = year(dateadd(month, -25, @target_month_date))*100 + month(dateadd(month, -25, @target_month_date))
set @dmnd26 = year(dateadd(month, -26, @target_month_date))*100 + month(dateadd(month, -26, @target_month_date))
set @dmnd27 = year(dateadd(month, -27, @target_month_date))*100 + month(dateadd(month, -27, @target_month_date))
set @dmnd28 = year(dateadd(month, -28, @target_month_date))*100 + month(dateadd(month, -28, @target_month_date))
set @dmnd29 = year(dateadd(month, -29, @target_month_date))*100 + month(dateadd(month, -29, @target_month_date))
set @dmnd30 = year(dateadd(month, -30, @target_month_date))*100 + month(dateadd(month, -30, @target_month_date))
set @dmnd31 = year(dateadd(month, -31, @target_month_date))*100 + month(dateadd(month, -31, @target_month_date))
set @dmnd32 = year(dateadd(month, -32, @target_month_date))*100 + month(dateadd(month, -32, @target_month_date))
set @dmnd33 = year(dateadd(month, -33, @target_month_date))*100 + month(dateadd(month, -33, @target_month_date))
set @dmnd34 = year(dateadd(month, -34, @target_month_date))*100 + month(dateadd(month, -34, @target_month_date))
set @dmnd35 = year(dateadd(month, -35, @target_month_date))*100 + month(dateadd(month, -35, @target_month_date))
set @dmnd36 = year(dateadd(month, -36, @target_month_date))*100 + month(dateadd(month, -36, @target_month_date))
insert into rep_logility_demand_lvl1(t
DMND01, DMND02, DMND03, DMND04, DMND05, DMND06,
DMND07, DMND08, DMND09, DMND10, DMND11, DMND12,
DMND13, DMND14, DMND15, DMND16, DMND17, DMND18,
DMND19, DMND20, DMND21, DMND22, DMND23, DMND24,
DMND25, DMND26, DMND27, DMND28, DMND29, DMND30,
DMND31, DMND32, DMND33, DMND34, DMND35, DMND36
)
select @target_month_date,
FCST_ID,
sum(case when FCST_YR_PRD = @dmnd01 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd02 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd03 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd04 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd05 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd06 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd07 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd08 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd09 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd10 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd11 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd12 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd13 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd14 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd15 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd16 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd17 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd18 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd19 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd20 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd21 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd22 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd23 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd24 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd25 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd26 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd27 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd28 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd29 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd30 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd31 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd32 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd33 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd34 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd35 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd36 then DMD_ACTL_QTY else 0 end)
from logex_demand_36_lvl1
group by FCST_ID
GO
/****** Object: StoredProcedure [dbo].[sp_logility_load_de
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_logility_load_de
as
truncate table rep_logility_demand_lvl1
declare
@dmnd01 int,
@dmnd02 int,
@dmnd03 int,
@dmnd04 int,
@dmnd05 int,
@dmnd06 int,
@dmnd07 int,
@dmnd08 int,
@dmnd09 int,
@dmnd10 int,
@dmnd11 int,
@dmnd12 int,
@dmnd13 int,
@dmnd14 int,
@dmnd15 int,
@dmnd16 int,
@dmnd17 int,
@dmnd18 int,
@dmnd19 int,
@dmnd20 int,
@dmnd21 int,
@dmnd22 int,
@dmnd23 int,
@dmnd24 int,
@dmnd25 int,
@dmnd26 int,
@dmnd27 int,
@dmnd28 int,
@dmnd29 int,
@dmnd30 int,
@dmnd31 int,
@dmnd32 int,
@dmnd33 int,
@dmnd34 int,
@dmnd35 int,
@dmnd36 int
declare @target_month_date smalldatetime
SET @target_month_date = DATEADD(month, DATEDIFF(month, '', getdate() ) , '')
set @dmnd01 = year(dateadd(month, -1, @target_month_date))*100 + month(dateadd(month, -1, @target_month_date))
set @dmnd02 = year(dateadd(month, -2, @target_month_date))*100 + month(dateadd(month, -2, @target_month_date))
set @dmnd03 = year(dateadd(month, -3, @target_month_date))*100 + month(dateadd(month, -3, @target_month_date))
set @dmnd04 = year(dateadd(month, -4, @target_month_date))*100 + month(dateadd(month, -4, @target_month_date))
set @dmnd05 = year(dateadd(month, -5, @target_month_date))*100 + month(dateadd(month, -5, @target_month_date))
set @dmnd06 = year(dateadd(month, -6, @target_month_date))*100 + month(dateadd(month, -6, @target_month_date))
set @dmnd07 = year(dateadd(month, -7, @target_month_date))*100 + month(dateadd(month, -7, @target_month_date))
set @dmnd08 = year(dateadd(month, -8, @target_month_date))*100 + month(dateadd(month, -8, @target_month_date))
set @dmnd09 = year(dateadd(month, -9, @target_month_date))*100 + month(dateadd(month, -9, @target_month_date))
set @dmnd10 = year(dateadd(month, -10, @target_month_date))*100 + month(dateadd(month, -10, @target_month_date))
set @dmnd11 = year(dateadd(month, -11, @target_month_date))*100 + month(dateadd(month, -11, @target_month_date))
set @dmnd12 = year(dateadd(month, -12, @target_month_date))*100 + month(dateadd(month, -12, @target_month_date))
set @dmnd13 = year(dateadd(month, -13, @target_month_date))*100 + month(dateadd(month, -13, @target_month_date))
set @dmnd14 = year(dateadd(month, -14, @target_month_date))*100 + month(dateadd(month, -14, @target_month_date))
set @dmnd15 = year(dateadd(month, -15, @target_month_date))*100 + month(dateadd(month, -15, @target_month_date))
set @dmnd16 = year(dateadd(month, -16, @target_month_date))*100 + month(dateadd(month, -16, @target_month_date))
set @dmnd17 = year(dateadd(month, -17, @target_month_date))*100 + month(dateadd(month, -17, @target_month_date))
set @dmnd18 = year(dateadd(month, -18, @target_month_date))*100 + month(dateadd(month, -18, @target_month_date))
set @dmnd19 = year(dateadd(month, -19, @target_month_date))*100 + month(dateadd(month, -19, @target_month_date))
set @dmnd20 = year(dateadd(month, -20, @target_month_date))*100 + month(dateadd(month, -20, @target_month_date))
set @dmnd21 = year(dateadd(month, -21, @target_month_date))*100 + month(dateadd(month, -21, @target_month_date))
set @dmnd22 = year(dateadd(month, -22, @target_month_date))*100 + month(dateadd(month, -22, @target_month_date))
set @dmnd23 = year(dateadd(month, -23, @target_month_date))*100 + month(dateadd(month, -23, @target_month_date))
set @dmnd24 = year(dateadd(month, -24, @target_month_date))*100 + month(dateadd(month, -24, @target_month_date))
set @dmnd25 = year(dateadd(month, -25, @target_month_date))*100 + month(dateadd(month, -25, @target_month_date))
set @dmnd26 = year(dateadd(month, -26, @target_month_date))*100 + month(dateadd(month, -26, @target_month_date))
set @dmnd27 = year(dateadd(month, -27, @target_month_date))*100 + month(dateadd(month, -27, @target_month_date))
set @dmnd28 = year(dateadd(month, -28, @target_month_date))*100 + month(dateadd(month, -28, @target_month_date))
set @dmnd29 = year(dateadd(month, -29, @target_month_date))*100 + month(dateadd(month, -29, @target_month_date))
set @dmnd30 = year(dateadd(month, -30, @target_month_date))*100 + month(dateadd(month, -30, @target_month_date))
set @dmnd31 = year(dateadd(month, -31, @target_month_date))*100 + month(dateadd(month, -31, @target_month_date))
set @dmnd32 = year(dateadd(month, -32, @target_month_date))*100 + month(dateadd(month, -32, @target_month_date))
set @dmnd33 = year(dateadd(month, -33, @target_month_date))*100 + month(dateadd(month, -33, @target_month_date))
set @dmnd34 = year(dateadd(month, -34, @target_month_date))*100 + month(dateadd(month, -34, @target_month_date))
set @dmnd35 = year(dateadd(month, -35, @target_month_date))*100 + month(dateadd(month, -35, @target_month_date))
set @dmnd36 = year(dateadd(month, -36, @target_month_date))*100 + month(dateadd(month, -36, @target_month_date))
insert into rep_logility_demand_lvl1(t
DMND01, DMND02, DMND03, DMND04, DMND05, DMND06,
DMND07, DMND08, DMND09, DMND10, DMND11, DMND12,
DMND13, DMND14, DMND15, DMND16, DMND17, DMND18,
DMND19, DMND20, DMND21, DMND22, DMND23, DMND24,
DMND25, DMND26, DMND27, DMND28, DMND29, DMND30,
DMND31, DMND32, DMND33, DMND34, DMND35, DMND36
)
select @target_month_date,
FCST_ID,
sum(case when FCST_YR_PRD = @dmnd01 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd02 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd03 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd04 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd05 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd06 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd07 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd08 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd09 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd10 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd11 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd12 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd13 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd14 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd15 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd16 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd17 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd18 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd19 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd20 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd21 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd22 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd23 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd24 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd25 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd26 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd27 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd28 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd29 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd30 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd31 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd32 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd33 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd34 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd35 then DMD_ACTL_QTY else 0 end),
sum(case when FCST_YR_PRD = @dmnd36 then DMD_ACTL_QTY else 0 end)
from logex_demand_36_lvl1
group by FCST_ID
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, this will work until we get to 5 digit years. :)
set @dmnd01 = CONVERT(varchar(6), dateadd(month, -1, @target_month_date)), 112)
set @dmnd01 = CONVERT(varchar(6), dateadd(month, -1, @target_month_date)), 112)
Type-o:
set @dmnd01 = CONVERT(varchar(6), dateadd(month, -1, @target_month_date), 112)
Copy and pasted extra ) previously. Should just be one. Much reduced code than had previously. What it does is add (subtract) the number of months you want and then get date as "200911" for example. This will convert to INT 200911 by setting as value of INT variable @dmnd01.
set @dmnd01 = CONVERT(varchar(6), dateadd(month, -1, @target_month_date), 112)
Copy and pasted extra ) previously. Should just be one. Much reduced code than had previously. What it does is add (subtract) the number of months you want and then get date as "200911" for example. This will convert to INT 200911 by setting as value of INT variable @dmnd01.
ASKER
It was hard for me to read the original text, and trying to copy/paste didn't work, so the error came from putting a double quote in the place of to single quote marks next to each other. As soon as I changed that it fired up like a charm...very nice!!
As it is currently set, I don't have the date coming from inside the stored procedure, it is coming from the call, BUT, I didn't write the code and never have liked it, so putting it in the code is just fine by me.
I just tested it with great success. Very nice!
As it is currently set, I don't have the date coming from inside the stored procedure, it is coming from the call, BUT, I didn't write the code and never have liked it, so putting it in the code is just fine by me.
I just tested it with great success. Very nice!
ASKER
mwvisa1,
what is the 112 in your proposed formula for the main body of the stored procedure?
really, I don't quite get the date stuff in general. I have come to learn that SQL makes you dateadd and datediff to fluctuate off of the getdate() feature (why can't it just be like in Access or VBA...date() and DONE!!!...same company you think they could keep it simple...whatever).
Anyway, I am not one that likes to just copy and paste solutions, so could you help me understand the formulas you both came up with?
Thanks,
what is the 112 in your proposed formula for the main body of the stored procedure?
really, I don't quite get the date stuff in general. I have come to learn that SQL makes you dateadd and datediff to fluctuate off of the getdate() feature (why can't it just be like in Access or VBA...date() and DONE!!!...same company you think they could keep it simple...whatever).
Anyway, I am not one that likes to just copy and paste solutions, so could you help me understand the formulas you both came up with?
Thanks,
Sorry, I meant to explain a little better.
The CONVERT() function can be used to format the datetime object as a string. 112 is the code for the ISO format YYYYMMDD which today would be 20091228 for example. Using CONVERT(), as you can see the first parameter is data type and since VARCHAR can be given a specific length I have chosen 6 which will thus take the first 6 characters 200912.
Books Online Reference:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Hope that helps.
The CONVERT() function can be used to format the datetime object as a string. 112 is the code for the ISO format YYYYMMDD which today would be 20091228 for example. Using CONVERT(), as you can see the first parameter is data type and since VARCHAR can be given a specific length I have chosen 6 which will thus take the first 6 characters 200912.
Books Online Reference:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Hope that helps.
And glad you found Aneesh's solution successful as it would be the better way to go if you can change the stored procedure if you are always wanting this run for the current month.
@Aneesh, great work as usual, sir!
@dabdowb:
>>Anyway, I am not one that likes to just copy and paste solutions, so could you help me understand the formulas you both came up with?
That is a great attitude to have; you will learn much more that way! Also to answer your question on the second portion as I see you wanted to understand Aneesh's solution as well, that formula is simply this:
DATEDIFF( month, '', getdate() )
'' or 0 casts to '1900-01-01' which is the base date for SQL Server datetime. Therefore, you are getting the number of months from date 0 to the current date.
DATEADD( month, n, '' )
Given above, you are starting from '1900-01-01' and adding n number of months to it. If you substitute n with the formula using datediff, you will add the number of months to get to the current month. Since you have not specified anything further, it will remain on the 1st since that is the day which date 0 starts. Hopefully that is clear.
Best regards and happy coding,
--isa
@Aneesh, great work as usual, sir!
@dabdowb:
>>Anyway, I am not one that likes to just copy and paste solutions, so could you help me understand the formulas you both came up with?
That is a great attitude to have; you will learn much more that way! Also to answer your question on the second portion as I see you wanted to understand Aneesh's solution as well, that formula is simply this:
DATEDIFF( month, '', getdate() )
'' or 0 casts to '1900-01-01' which is the base date for SQL Server datetime. Therefore, you are getting the number of months from date 0 to the current date.
DATEADD( month, n, '' )
Given above, you are starting from '1900-01-01' and adding n number of months to it. If you substitute n with the formula using datediff, you will add the number of months to get to the current month. Since you have not specified anything further, it will remain on the 1st since that is the day which date 0 starts. Hopefully that is clear.
Best regards and happy coding,
--isa
ASKER
Both experts, Aneeshattingal and mwvisa1, supplied accurate results. Aneesh went an added step and showed me how to construct the code inside the stored procedure. Mwvisa1, went the extra mile and not only explained how both results worked, but streamlined my code as well inside the stored procedure...above and beyond the question itself. Awesome work from both so split the points equally as both were excellent resources for help. Thanks!
ASKER
Msg 174, Level 15, State 1, Line 1
The datediff function requires 3 argument(s).
Also, are you saying I would put this statement inside the sp itself? If so, where? I mean, I know it needs to be assigned to target_month_date after it is declared type smalldatetime, but how would I assign the result of a SELECT statement to that?