Format a stored procedure

I have a stored procedure that runs based upon a weekly and daily dates. On Monday this procedure runs a weekly file for the previous week and then the daily file. Any assistance on how to format this to work like I need it would be greatly appreciated.



DECLARE
@StartDate_Weekly VARCHAR(30),
@EndDate_Weekly VARCHAR(30),
@StartDate_Daily VARCHAR(30),
@EndDate_Daily VARCHAR(30),
@TodayDayOfWeek INT,
@EndOfPrevWeek DateTime,
@StartOfPrevWeek DateTime


----daterange for previous day
SELECT @StartDate_Daily = dateadd(day, datediff(day, 1, getdate()), 0)
SELECT @EndDate_Daily = dateadd(day, datediff(day, 0, getdate()), 0)

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndDate_Weekly = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartDate_Weekly = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

--Runs on Monday and includes weekly file for previous week and daily file for previous day.

IF @TodayDayOfWeek = datepart(dw, GetDate()) = 1 THEN

Begin

Exec [dbo].[d_w_etl] @StartDate_Weekly, @EndDate_Weekly
Exec [dbo].[d_w_etl] @StartDate_Daily, @EndDate_Daily

End

--Runs Tuesday thru Thursday for daily file for previous day.

Else

IF @TodayDayOfWeek = datepart(dw, GetDate()) <> 1

Begin

Exec [dbo].[d_w_etl] @StartDate_Daily, @EndDate_Daily

End
earngreenAsked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
I didn't do much here. Give this a whirl.

DECLARE
@StartDate_Weekly VARCHAR(30),
@EndDate_Weekly VARCHAR(30),
@StartDate_Daily VARCHAR(30),
@EndDate_Daily VARCHAR(30),
@TodayDayOfWeek INT,
@EndOfPrevWeek DateTime,
@StartOfPrevWeek DateTime


----daterange for previous day
SELECT @StartDate_Daily = dateadd(day, datediff(day, 1, getdate()), 0)
SELECT @EndDate_Daily = dateadd(day, datediff(day, 0, getdate()), 0)

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndDate_Weekly = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartDate_Weekly = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

--Runs on Monday and includes weekly file for previous week and daily file for previous day. 

IF @TodayDayOfWeek  = 1 

Begin

Exec [dbo].[d_w_etl] @StartDate_Weekly, @EndDate_Weekly
Exec [dbo].[d_w_etl] @StartDate_Daily, @EndDate_Daily

End

--Runs Tuesday thru Thursday for daily file for previous day. 

Else 

IF @TodayDayOfWeek  <> 1 

Begin

Exec [dbo].[d_w_etl] @StartDate_Daily, @EndDate_Daily

End

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.