DSXchange

Hi,

I have a situation where I am looking up days between Target Date and Task Complete date. In the transformer I am looking up workdays and holidays in between. I need to pass the Target Date and Task Complete date and it returns the number of workdays or holidays.

Now the issue is that even though the source has Target Date as a date the actual date I should look up is Target Date + 1 day. I can't simply do that in the source query as I don't want to store this new date but just use it for the look up. I have tried the following:

Source Target Date = 2008-10-14 01:00:00
Task Complete Date = 2008-10-16 00:00:00

In the transformer
Target Date = oconv((Iconv(TaskInfo.SO_TARGET_DATE, "D-YMD") +1), "D-YMD[4,2,2]"): " 00:00:00"
Task Complete Date = 2008-10-16 00:00:00

The look up query is:
workdays = SELECT :1, :2, count(*) AS WRK_DAYS,
case when trunc(TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS')) = trunc(TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS')) AND TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS') > (trunc(TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS') ) + 17/24) THEN 'TRUE' ELSE 'FALSE' END AS TASK_AFTER_5
FROM DATE_DIM
WHERE (DT_CAL_DATE > TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS') AND DT_CAL_DATE <= TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS')) AND TO_CHAR(DT_CAL_DATE, 'D') IN ('2', '3', '4', '5', '6')

holidays = SELECT :1, :2, count(*)
FROM REGION_HOLIDAY WHERE HOLIDAY_DATE > TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS') AND HOLIDAY_DATE <= TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS') AND TO_CHAR(HOLIDAY_DATE, 'D') IN ('2', '3', '4', '5', '6') AND NODE = 'VIC'

when I have workdays-holidays as my dayslate the job returns 10544 which is incorrect. It should return 1 day late.

Please help me, I just want to add a day to the Target Date, it can't be so difficult.

Nita

What values do you get back for workdays and holidays?

What do you get for Target Date after you have done the oconv?

As far as adding days to a date is concerned, this is far easier than you imagine. Get the date into internal format and add the days. Whether you need to convert it back to external format depends on what you want to do with the new date subsequently.

If your "source target date" comes in as a full timestamp, you'll need to adjust your transform code slightly to chop off the time and just work with the date portion:

Target Date = Oconv((Iconv(TaskInfo.SO_TARGET_DATE[1,10], "D-YMD") +1), "D-YMD[4,2,2]") : " 00:00:00"