I need to pull up sales for the last 2 days(includes current date) until last hour (current date) . This extract query job will be scheduled to run every 10th minute of an hour . There is also a need to ensure if it runs on 12:10 AM it should pick up sales- previous day 23rd hour sales correctly .

I come up with the below SQL Query BUT with a need to handle for the last hour of current date.

Code:

SELECT DEPT_NBR, SALES_AMT,SALES_DATE,SALES_HR_NBR
FROM DEPT_SALES TABLE
WHERE DEPT = 100
AND SALES_DATE BETWEEN (CURRENT DATE - 2 DAYS) AND CURRENT DATE
AND SALES_HR_NBR BETWEEN 0 AND 23
AND SALES_HR_NBR <= HOUR(CURRENT TIME - 1 HOUR)
ORDER BY SALES_DATE,SALES_HR_NBR
WITH UR

In above Query,i need to handle the condition of SALES_HR_NBR only when SALES_DATE equals to current date and not for prev days. I have written a cobol code to handle/limit the hour sales by remove the condition of SALES_HR_NBR . I request to know if this can handles in Extract SQL Query itself and also to take care on 0th hour . Request your assistance/help on handling this condition.

I didnt get your question. My understanding is You're asking whether i have a column timestamp ? yes we have a column Update_ts. I need to have this condition " current time - 1 hour " only when my sales date is equal to current date otherwise i need to pick up sales info of all hours of prev day . How to have this condition achieved using time stamp ? can you pls explain?

Sure i will try this and keep you posted . I guess the below " 3 DAYS ' is typo.

(SALES_DATE BETWEEN (CURRENT DATE - 3 DAYS) AND CURRENT DATE)

No, it wasn't a typo.

I don't think the query will achieve what I think you want BUT the idea is that you have to go back 3 days (time 23:10) if your current hour is 0.

for example, if your job is running on 4th of August 00:10 then you would need the starting date as 1st of August i.e. CURRENT DATE - 3 DAYS, but if your job is running on 4th of August 01:10 then you would only need to go back 2 days.

You can also do one thing,
Before you hit the sql above manipulate "CURRENT DATE " upfront based on your rules.
for e.g. if you want 12:10 AM of current day still to be treated as previous day then so be it, move current date -1 day else current date to some host variable and use it instead of "Current date: in you query in stead of a complex and tricky logic.
Check with scheduling team too if they can help you with something partially or fully to solve this timing issues.

Thanks for every suggestion. As said earlier i am using COBOL code to handle the hour logic . This is how i handle and which is working for me.

Take date format like 'YYYYMMDD' from sales date and concat with Sales HR NBR . Take current date in YYYYMMDD and concat with Current hour .

If Current hour = 0 then move '23' to current hour else do nothing .
IF SALESDATE('YYYYMMDDHH') < CURRENT DATE(YYYYMMDDHH) then allow data to process else elminate those sales .

I am thinking now to perform the above by using Month, DAY,YEAR DB2 builtin functions but handling on current hour = 0 is still in question in extract Query . Can any one suggest whether to go with program or it can be handled in extract SQL Query .

Can any one please suggest me how we can handle the comparison when hour(current time = 0 ) AND <> 0 in extract Query as per program logic that i specified in the post . Mean while i will also try from my end.

If you have it working with a CCOBOL/SQL solution the stick to that - it is probably the optimal solution. Putting more work onto DB2 is going to slow down your data retrieval and, over the ages, impact every DB2 user/application.

If you have it working with a CCOBOL/SQL solution the stick to that - it is probably the optimal solution. Putting more work onto DB2 is going to slow down your data retrieval and, over the ages, impact every DB2 user/application.