Page 1 of 2
Logic to find no. of saturdays and sundays betwen two dates.
Posted: Sun May 22, 2011 7:04 pm
by saraswati
Please let me know a logic to find out the number of weekends ( Saturdays\Sundays) between two dates.
Re: Logic to find no. of saturdays and sundays betwen two da
Posted: Sun May 22, 2011 7:46 pm
by 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.
Re: Logic to find no. of saturdays and sundays betwen two da
Posted: Sun May 22, 2011 7:52 pm
by saraswati
I am looking for a logic to get it immediately implemented using parallel transformer.
Re: Logic to find no. of saturdays and sundays betwen two da
Posted: Sun May 22, 2011 8:11 pm
by SURA
DSX is not fast food shop
fine, let me know your source?
DS User
Posted: Sun May 22, 2011 8:40 pm
by 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.
Posted: Sun May 22, 2011 8:59 pm
by 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
Posted: Sun May 22, 2011 9:19 pm
by 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.
Posted: Mon May 23, 2011 12:14 am
by 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
Re: Logic to find no. of saturdays and sundays betwen two da
Posted: Mon May 23, 2011 7:12 am
by paultechm
saraswati wrote: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
Posted: Mon May 23, 2011 10:12 am
by 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.
Posted: Mon May 23, 2011 10:31 am
by paultechm
saraswati wrote: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
Posted: Mon May 23, 2011 1:46 pm
by vinothkumar
Why cant you write a simple server routine for this and call in your job.
Posted: Mon May 23, 2011 2:39 pm
by 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
Posted: Mon May 23, 2011 6:20 pm
by 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;
Posted: Mon May 23, 2011 8:33 pm
by 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.