Logic to find no. of saturdays and sundays betwen two dates.

[saraswati]

Please let me know a logic to find out the number of weekends ( Saturdays\Sundays) between two dates.

[SURA]

1. Change the date format.
2. In the TFM you can use to stage var to find the count by using your own logic.

[saraswati]

I am looking for a logic to get it immediately implemented using parallel transformer.

[SURA]

DSX is not fast food shop

fine, let me know your source?

DS User

[saraswati]

I am only concerned about the working days( Mon-Fri) and not holidays.

I will give you an example.

Start_date = 20110509
End_date= 20110518

So, there is only one weekend between these days. (14th and 15th May).

I need to shift the start day two days before 20110509. Now,the challenge is 20110507 and 20110508 both are weekends.So, those two days should also be calculated .

The expected start_date should be 20110505 as 20110509 is subtracted by 4 days(7th,8th,14th and 15th May).

If we get how many days need to be subtracted then we are all set.

[ray.wurlod]

Use a Lookup stage and a calendar table (perhaps a time dimension table) to ascertain day name, then count them conditionally.

Code: Select all

If Left(InLink.DayName,1) = "S" Then svWeekendCount + 1 Else svWeekendCount

[saraswati]

I am using Oracle database.

I will explain you the business requirements.

A delivery date is there for a material. There is a certain intransit days for shipment for a particular material.Currently,the shipment start date = delivery date - Intransit days.

So, the number of saturdays and sundays which are falling between shipment start date and delivery date need to be calculated. This number need to be subtracted from shipment start date. Basically, we need to also calculate the shipment start date by eliminating the saturdays and sundays which comes while subtracting the number of saturdays and sundays which falls between shipment start date and delivery date.

I will give you an example.

Delivery date = 20110509
Intransit days=23
Start Date=20110531

There are 6 weekend days (14th,15th,21st,22nd,28th,29th).

So, 6 days need to subtracted from 20110509. So,the 6 days will be 29th April, 2nd-6th May. We are not considering the two weekends (30th April, 1st May,7th May,8th May )while subtracting the 6 days.

So,the correct shipment start date =20110428.

Let me know if you need more info.

[ray.wurlod]

Use a Lookup stage and a calendar table (perhaps a time dimension table) to ascertain day name, then count them conditionally.

Code: Select all

If Left(InLink.DayName,1) = "S" Then svWeekendCount + 1 Else svWeekendCount 

[paultechm]

Please let me know a logic to find out the number of weekends ( Saturdays\Sundays) between two dates.

Use the following parallel function. You have to handle all the special cases

(YearweekFromDate(StringToDate('2011-05-31'))-YearweekFromDate(StringToDate('2011-05-09')))*2

-Paul

[saraswati]

The above logic doesn't work out.

Basically, I want logic to subtract N weekdays from a given date.While subtracting,we will not consider Saturdays and Sundays.

[paultechm]

The above logic doesn't work out.

Basically, I want logic to subtract N weekdays from a given date.While subtracting,we will not consider Saturdays and Sundays.

This will give you the no.of weekends in that 23 days

(YearweekFromDate(DateFromDaysSince(23, StringToDate('2011-05-09')))-YearweekFromDate(StringToDate('2011-05-09')))*2

[vinothkumar]

Why cant you write a simple server routine for this and call in your job.

[ray.wurlod]

Use a Lookup stage and a calendar table (perhaps a time dimension table) to ascertain day name, then count them conditionally.

Code: Select all

If Left(InLink.DayName,1) = "S" Then svWeekendCount + 1 Else svWeekendCount 

[saraswati]

Code: Select all

CREATE OR REPLACE FUNCTION FUNC_START_DATE (delivery_date DATE, 
in_transit_days NUMBER) RETURN DATE IS
 Counter NATURAL := 0;
 CurDate DATE := delivery_date;
 DayNum POSITIVE;
 SkipCntr NATURAL := 0;
 Direction INTEGER := 1;  -- days after delivery_date
 BusinessDays NUMBER := in_transit_days;
BEGIN
  IF in_transit_days < 0 THEN
    Direction := - 1; -- days before delivery_date
    BusinessDays := (-1) * BusinessDays;
  END IF;


  WHILE Counter < BusinessDays LOOP
    CurDate := CurDate + Direction;
    DayNum := TO_CHAR( CurDate, 'D');


    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
  END LOOP;

[ray.wurlod]

That is quite database-specific, it's not a general solution

A general solution could employ similar code within a DataStage routine.

But a calendar table not only solves the weekend question, it also provides a solution to the holiday question. And it offers the flexibility to handle things like Islamic cultures, where Friday is one of the weekend days, being dedicated to prayer.