Hi,
I need to calculate week in month no by useing below condition.
count(WEND) from D_CM_ACCT_CAL where D_CM_ACCT_CAL_DAY.WEND = D_CM_ACCT_CAL.WEND and D_CM_ACCT_CAL_DAY.PERIOD = D_CM_ACCT_CAL.PERIOD
D_CM_ACCT_CAL(Table by Week),D_CM_ACCT_CAL_DAY(Table by month) are calender tables.
D_CM_ACCT_CAL (sample data):
WDATE WSTART WEND PERIOD
1/3/1992 12/30/1991 1/5/1992 199201
12/30/1991 12/30/1991 1/5/1992 199201
1/1/1992 12/30/1991 1/5/1992 199201
1/18/1992 1/13/1992 1/19/1992 199201
1/22/1992 1/20/1992 1/26/1992 199201
1/24/1992 1/20/1992 1/26/1992 199201
2/2/1992 1/27/1992 2/2/1992 199201
D_cm_acct_cal_day(sample data)
WSTART WEND PERIOD
12/30/1991 0:00 1/5/1992 0:00 199,201
1/6/1992 0:00 1/12/1992 0:00 199,201
1/13/1992 0:00 1/19/1992 0:00 199,201
1/20/1992 0:00 1/26/1992 0:00 199,201
1/27/1992 0:00 2/2/1992 0:00 199,201
DTL_TBL_DT = 5/7/2012
CNT = 1131
ACCT_WEEK_NO = 2993
WEEK_IN_YR_NO = 20
WEEK_IN_MO_NO = 2
WSTART = 5/4/2012
WEND = 5/10/2012
MO_NBR = 5
MON_BEGIN_DATE = 4/27/2012 - This is the month begin date that 5/7/2012 belongs to
MON_END_DATE = 5/31/2012
PERIOD = 201205
MTD_HRS = 192
GFY = 2012
FYEAR = 2012
FMONTH = 2012M05
FQTR = 2012Q2
PRD_WK_IN_MO = 2012052
LAST_MO_STR_DT = 3/30/2012 - This is the previous month based on sysdate
LAST_MO_END_DT = 4/26/2012
LAST_3_STR_DT = 1/27/2012 - This is the previous 3 months based on sysdate
LAST_3_END_DT = 4/26/2012
CURR_YEAR = Y
CURR_MONTH = Y
CURR_WEEK = N
LAST_MO_FLG = N
LAST_3_FLG = N
ITD_FLG = N
Please help me
Query help
Moderators: chulett, rschirm, roy
Do you have some rules (such as a mathematical formula) that you have been given to use in calculating the week? If not, one possibility is to divide the number of days between the month start date and your detail date by 7 and then add 1 (the column or stage variable holding the results of this calculation should be an integer!). DaysSinceFromDate() will give you the number of days between the dates.
You can probably perform a Range lookup with the Lookup stage to find the month your detail date belongs to if that has not already been determined prior to running your job.
Regards,
Code: Select all
DaysSinceFromDate(month_start_date,detail_date)/7 + 1
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.