Hi all,
I have a requirement to calculate the incremental working day for the month. An example like below
Cal_Date Day Col1 Col2
01/01/09 Thu N 0
02/01/09 Fri Y 1
03/01/09 Sat N 1
04/01/09 Sun N 1
05/01/09 Mon Y 2
06/01/09 Tue Y 3
07/01/09 Wed Y 4
08/01/09 Thu Y 5
09/01/09 Fri Y 6
10/01/09 Sat N 6
.
.
.
30/01/09 Fri Y 21
31/01/09 Sat N 21
01/02/09 Sun N 0
02/02/09 Mon Y 1
03/02/09 Tue Y 2
.
.
.
Col1 is the work day indicator and Col2 is the incremental count of work day for month.
I have try different means to calculate Col2 but in vain. Has anyone done this before? Appreciate any ideas if you have. Tks
Increment count of working day for month
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
It can be done using stage variable.
1. hash partition on a derived column containing only month and year, then sort on calendar date.
2. Use stage variables to get the counts.
reset the counter to 0 if the value in derived column changes.
Increment the value if indicator is Y else retain the last value.
1. hash partition on a derived column containing only month and year, then sort on calendar date.
2. Use stage variables to get the counts.
reset the counter to 0 if the value in derived column changes.
Increment the value if indicator is Y else retain the last value.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
I will do it in 3 steps.
1.) Load all non-weekend holidays into a sequential file
2.) Create a job to append all weekends for the expected period into the sequential file
3.) Create a job (or in same job as above) to reference (sparse) the count of holidays with between clause from 1st of month to current date.
Thus you have control to alter any holidays if required.
And also use the result as calendar dimension.
A server job will do the trick better.
1.) Load all non-weekend holidays into a sequential file
2.) Create a job to append all weekends for the expected period into the sequential file
3.) Create a job (or in same job as above) to reference (sparse) the count of holidays with between clause from 1st of month to current date.
Thus you have control to alter any holidays if required.
And also use the result as calendar dimension.
A server job will do the trick better.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: