Query help

[iskapalli]

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

[iskapalli]

Please help me

[jwiles]

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.

Code: Select all

DaysSinceFromDate(month_start_date,detail_date)/7 + 1

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,

[iskapalli]

Thanks jwiles.

I think no need to use any function since already I have calender table's which I mentioned above.

Please correct me I am worng

[jwiles]

Do the tables give you the week number? If not, how will you derive it?

[chulett]

Please help me

Please show some patience. You posted at 2AM my time and then two hours later got tired of waiting and posted again.