Logic to find no. of saturdays and sundays betwen two dates.
Moderators: chulett, rschirm, roy
Logic to find no. of saturdays and sundays betwen two dates.
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
1. Change the date format.
2. In the TFM you can use to stage var to find the count by using your own logic.
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
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
DSX is not fast food shop ![Smile :)](./images/smilies/icon_smile.gif)
fine, let me know your source?
DS User
![Smile :)](./images/smilies/icon_smile.gif)
fine, let me know your source?
DS User
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Logic to find no. of saturdays and sundays betwen two da
Use the following parallel function. You have to handle all the special casessaraswati wrote:Please let me know a logic to find out the number of weekends ( Saturdays\Sundays) between two dates.
(YearweekFromDate(StringToDate('2011-05-31'))-YearweekFromDate(StringToDate('2011-05-09')))*2
-Paul
This will give you the no.of weekends in that 23 dayssaraswati 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.
(YearweekFromDate(DateFromDaysSince(23, StringToDate('2011-05-09')))-YearweekFromDate(StringToDate('2011-05-09')))*2
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.