?
Solved

Format a stored procedure

Posted on 2012-09-18
1
Medium Priority
?
243 Views
Last Modified: 2012-10-03
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
0
Comment
Question by:earngreen
1 Comment
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38411359
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question