Increment count of working day for month

[joetan]

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

[priyadarshikunal]

Can you please tell us what you have tried till now.

[priyadarshikunal]

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.

[Sainath.Srinivasan]

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.

[ray.wurlod]

What about holidays?

You NEED a time/calendar dimension table, and you need a holiday table.