Last working day from last year!

PedroCGD

Dear Friends,

How can I retrieve the last working day from the currentyear-1

Thanks

Phil Brammer


In SQL, or an expression inside SSIS

Jamie Thomson

Not sure what this has got to do with SSIS.

Which country are you in Because different countries have different public holidays around the end of the year and this obviously makes a difference.

-Jamie

PedroCGD

I need to create a new column with the last working day for each row in the datasource... as for example inside a derived column...

How can I do it

And I have the problem about holidays... but could be resolved if I have a table wich indicate the holidays for this case in my country Portugal... or have to apply for all the cases using a key to each country...

could help me

Thanks!!

PedroCGD

How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column
Help me!!

Phil Brammer

PedroCGD wrote:

How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column Help me!!

What is considered the last working day of the year The last Friday

PedroCGD

YES

Benda Fenda

This is usually a table-driven information item such as a time dimension table. The DIMTIME records would be flagged for weekdays.

As Jamie mentioned, holidays are a regional thing so you would join entries in a DIMTIME table against a local holiday table to derive a list of working days. Then you could query the join to find the last working day of a given year.

This is not a SSIS issue, but a TSQL or engine question.

PedroCGD

No Benda, forget the holidays....

I need to transform each row, to add a new column that for each date I need to get the Reference Rate that is the last day of the last year for the each row of the data source....

thanks

Phil Brammer

This assumes you have a VARIABLE containing the year. If you don't have that, and need to dynamically read the year, replace the variable here with DATEPART("yyyy",[your_date_field])

So you'd replace "(DT_WSTR,4)@[User::Year]" with "DATEPART("yyyy",[your_date_field])" if that's the case.

DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@[User::Year])) < 6 DATEADD("d",-1 * (DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@[User::Year])) + 1),(DT_DBDATE)("12/31/" + (DT_WSTR,4)@[User::Year])) : DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@[User::Year])) > 6 DATEADD("d",-1,(DT_DBDATE)("12/31/" + (DT_WSTR,4)@[User::Year])) : (DT_DBDATE)("12/31/" + (DT_WSTR,4)@[User::Year])

PedroCGD

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code

Thanks!

Phil Brammer

PedroCGD wrote:

Phil, i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday... My last working day of a week is friday not saturday! :-) Where you define it in your code Thanks!

That's a locale issue then... DATEPART("dw",[datefield]) == 6 for Fridays here in the US.

Never the less, you should be able to adjust the logic by playing around with it a bit. Try to read the expression I've built for you and work on it your own. It's pretty simple logic, really, despite the fact the expression looks complicated.

PedroCGD

your code in US give you the friday

Phil Brammer

PedroCGD wrote:

your code in US give you the friday

Yes, absolutely. I tested it for several years. 2006 = 12/29/2006 = Friday

PedroCGD

your code for me gives me 2006 = 12/30/2006 = Saturday!

:-(

bbrrr.... :-(