CODEWITHSUNDEEP
sql-serverdatessisexpressionetl
Baldie47
Baldie47

Reputation: 1264

SSIS expression for getting last friday and last friday 4 weeks ago

I'm looking for the expression for using in SSIS to get the most current Friday and the last Friday from 4 weeks ago.

I was able to get this:

DATEPART( "dw", GETDATE() ) == 2 ? GETDATE() : DATEADD( "day", -( DATEPART( "dw", GETDATE() ) +1), GETDATE() )

but I'm not sure if it will work for example next week properly, and I don't know how to use it for 4 Fridays ago (I tried adding 28 but that doesn't seem to make the trick)

enter image description here

Upvotes: 2

Views: 796

Answers (2)

Ritika
Ritika

Reputation: 104

You can use the SQL script to do the same. Add execute SQL Task to your Process. Create an Object type variable as you will need that to store the two dates.

Then if you run the below script, it will give you last friday and friday before 4 weeks. (You can choose the fridays any number of weeks ago by changing the days value) 

(
  SELECT getdate() DateValue
  UNION ALL
  SELECT  DateValue - 1
  FROM    mycte   
  WHERE   DateValue - 1 > DATEADD(day,-34, GETDATE())
)                                                                                           
select DateValue from (
SELECT  DateValue,DATEPART(DW, DateValue) as weekday,ROW_NUMBER() over (order by datevalue desc) as Most_Recent_Friday
FROM    mycte
where DATEPART(DW, DateValue) = 6                                                        ) a where Most_Recent_Friday in (1,5)
OPTION (MAXRECURSION 0)

The original results would be:

DateValue                       |  weekday    |   Most_Recent_Friday
2019-08-16 09:46:25.330         |  6          |   1
2019-08-09 09:46:25.330         |  6          |   2
2019-08-02 09:46:25.330         |  6          |   3
2019-07-26 09:46:25.330         |  6          |   4
2019-07-19 09:46:25.330         |  6          |   5

From which I have chosen Friday number 1(last most recent) and Friday number 5 (4 weeks ago), you may chose as your requirement

You will get below output from the given query:

2019-08-16 09:46:25.330
2019-07-19 09:46:25.330

Last Friday will be Most_Recent_Friday = 1, and 4 weeks ago Friday would be Most_Recent_Friday = 5 or may be 4 whichever you need You can store the results in object variable created and use it in your process

Upvotes: 1

Robert Sheahan
Robert Sheahan

Reputation: 2100

OK, there are 2 approaches you can take here, the one I think is easiest is to put it in a script task that writes to the variables. The second, is to embed the SQL code in a SQL script, and pull the results out into a result set. If you're using it as input to another data stream, the second might be worth exploring, but I'll concentrate on the first because it's easier (at least for me). So here goes.

Method 1:

Step 1) Start with 2 variables, both of type DateTime. I named mine User::varLastFriday and User::var4FriAgo, but you can name them anything you want. Keep in mind that unlike most of SQL and Visual Studio, these are case sensitive!

Step 2) Create a "Script Task". Note that there a many similar sounding tasks, this is the one with the description "Performs functions that are not provided by the standard Integration Services tasks. ..."

Step 3) Edit your script, set the "Language" to Microsoft Visual Basic (to use my sample, you can do the same in C if you're more comfortable) of whatever year your editor is, I'm using 2015 but this should be similar across other versions, 2008 at least was identical.

Step 4) Still in the script properties, drop down the "Read/Write variables" and check both of your variables, you can't update them if they aren't selected here.

Step 5) Still in the script properties, click the button that says "Edit Script", it will open a Visual Studio window with a skeleton project. You'll modify the code in the "Main" procedure. Here is the code. Note that the declaration of Main and the return value should already exist, you're only inserting what I have between my "Start/End of my custom code" comments

Public Sub Main()

      'Start of my custom code
      Dim nDayOfWeek As Integer = DatePart(DateInterval.Weekday, Now)
      Dim nOffset As Integer = 0
      If nDayOfWeek >= 6 Then
         nOffset = 6 - nDayOfWeek
      Else
         nOffset = -1 - nDayOfWeek
      End If
      Dts.Variables("User::varLastFriday").Value = DateAdd(DateInterval.Day, nOffset, Now)
      Dts.Variables("User::var4FriAgo").Value = DateAdd(DateInterval.Day, nOffset - 28, Now)
      'End of my custom code

      Dts.TaskResult = ScriptResults.Success
    End Sub

Step 6) Save the script, save the task, and connect the task to your other steps so it runs first. It's fast, but you don't want a race condition

That's it, when you run it, it updates both variables with the dates of last Friday and the Friday 4 weeks ago, respectively.

Method 2: The logic can be written in SQL and put in a "Execute SQL Task" where you can get the results back from a result set. I have a hard time getting this method to work, I can usually fiddle around enough to get it to work eventually but you're probably better off getting help from somebody else if you want to go that route. Here's the code that will generate the dates, though, it will at least give you a start.

--DECLARE @TestDate DATE = '2019-08-21' 
DECLARE @TestDate DATE = GETDATE() 
--08-23 is Friday, WEEKDAY 6, 25 is Sunday Weekday 1
--use GETDATE for today
DECLARE @LastFri DATE 
-- >=6 means running on Friday returns today, 
--use >6 if you want running on Friday to return last friday
SET @LastFri = DATEADD(day, IIF(DATEPART(WEEKDAY, @TestDate) >= 6 
    ,6-DATEPART(WEEKDAY, @TestDate)
    ,-1-DATEPART(WEEKDAY, @TestDate)),@TestDate)
SELECT @TestDate as IfDayIs , @LastFri as ThenLastFriIs, DATEADD(day, -28, @LastFri ) as Fri4Ago

--Fridays in August: 30, 23, 16, 9, 2 July: 26, 19, 12

Upvotes: 2

Related Questions