Substract Business Days

[shiva_reddys447]

Hi ,

I have a requirement to substract 3 business days from dates received from source by looking upon a table (this table will have all business days , it doesnt contain any holidays ).Please check the below scenarios.

i/p date : 15-oct-2010 and lets say that 13-oct-2010 is a holiday as per the look up table ,then my o/p date :11-oct-2010

i/p date : 18-oct-2010 and lets say 16 & 17(sat&sun) are holidays as per the look up table ,then my o/p date :13-oct-2010

i/p date : 18-oct-2010 and lets say 15-oct-2010 is a holiday and 16&17 ar also holidays(sat&sun) as per the look up table, then my o/p date :12-oct-2010

i/p date :16-oct-2010(holiday (sat) as per look up table) then my o/p date :12-oct-2010.

Please provide your valuable approaches on this requirement

[ray.wurlod]

The analysis looks good. Simply use the number of rows returned from a lookup against the business days table. If you can use a sparse lookup, you might even return COUNT(*) from the table.

[agpt]

Sorry Ray,

could you please elaborate what does "Simply use the number of rows returned from a lookup against the business days table" mean?

[jcthornton]

I don't know the structure of your business-day table, but if it has a surrogate key - and you can have that key created as a monotonically increasing number by day, then your lookup becomes an easy one.

Just grab the date with a SK that is 3 less than the SK for the input date.

This can be faked in DS, but it requires the use of an extra couple of stages to pull off. (First solution that comes to mind is a fork-join using a transformer and a lookup)

[ray.wurlod]

When you do your range lookup, zero or more rows will be returned. Count them (for example in an Aggregator stage, if you don't use COUNT in the retrieval SQL).

[shiva_reddys447]

Hi All,

Thanks for all the responses .

@jcthornton : Even we dont have surrogate key in table , i am using row_number to generate in fly and get the -3 date from look up table ..