Updating Variables 2

[Sahubba]

Hello,

I am scheduling a monthly ETL on the 5th and 20th.
The begin date should always be the first day of the previous month if the ETL runs on the 5th
The End Date should always be t-1
The begin date for the ETL that runs on the 20th should always be the 1st of that month.
Example

ETL Run DateBegin DateEnd Date
3/5/2016 2/1/2016 3/4/2016
3/20/2016 3/1/2016 3/19/2016

I can not figure out how to do this...
This is the variable that I need to update

DECLARE
@AutoMonthFlag VARCHAR(1)
,@BeginDate DATETIME
,@EndDate DATETIME
SET @AutoMonthFlag = ?
SET @BeginDate = ?
SET @EndDate = ?

SELECT
CASE WHEN @AutoMonthFlag = 'Y' THEN CAST(DATEADD(MONTH,-1,DATEADD(DAY,-(DATEPART(DAY,GETDATE())-1),GETDATE())) AS DATE)
ELSE @BeginDate END AS BeginDT
,CASE WHEN @AutoMonthFlag = 'Y' THEN CAST(DATEADD(DAY,-DATEPART(DAY,GETDATE()),GETDATE()) AS DATE)
ELSE @EndDate END AS EndDT

Any help would be much appreciated!!

 

[johnherman]

You can add 15 days to the current date, then take the previous month. That works for both the 5th and 20th.
Or you can subtract 15 days from the current date, then take the current month. that works for both as well.
Either way it's the DATEADD Function that you'll probably want to use.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[gmmastros]

Here's a way to calculate your date range.

Declare @RunDate DateTime

Set @RunDate = '3/20/2016'  -- Change this to '3/5/2016'

Select @RunDate,
       DateAdd(Month, DateDiff(Month, 0, DateAdd(Day, -6, @RunDate)), 0) As BeginDate,
       DateAdd(Day, -1, @RunDate) As EndDate

This code should also work if you go back to the previous year. For example, if you set @RunDate to Jan 5, 2016, this code will return Dec 1, 2015 as the start date.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sahubba]

Thanks for responding!

johnherman the reason why I didn't use a set day like 15 days subtract or add is because not every month have 30 days.

gmmastros I am using SSIS to schedule the ETL on 5th and 20th of every month and part of the issues that I am having is that I cannot change the variables that have already been set in the SSIS package. The @AutoMonthFlag has flag set to "Y" within the Job that I am not able to modify. Unfortunately I am changing someone else's ETL but I am not able to change what has already been declared.

 

[Sahubba]

gmmastros thanks for the logic!!!
I was able to get this working!!!!

 

[johnherman]

It doesn't matter whether each month has 30 days or not, all you need to make sure is that the "minus 15" works for the two cases that you have identified, and it does. Subtracting 15 days from the 5th and 20th works regardless of whether the month has 28, 29, 30, or 31 days. If your date wasn't fixed at the 5th and 20th, and instead was something like "1st and 3rd Tuesdays", then you might need more specific logic.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[gmmastros]

I basically did what JohnHerman suggested, but I used -6 instead of 15. Same results.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom