|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Team,
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.
Thanks
Balaji |
|
| Back to top |
|
 |
prino
Senior Member
Joined: 07 Feb 2009
Posts: 1306
Location: Vilnius, Lithuania
|
|
|
|
| Use timestamps |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Prino,
Can you please help on Where/how to use timestamp in this query ?
Thanks
Balaji |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| What is not covered in the SQL, where do you think the query is not working? Do you have a timestamp column anywhere? |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Rohit,
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? |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
Can you try something like:
| Code: |
SELECT DEPT_NBR, SALES_AMT,SALES_DATE,SALES_HR_NBR
FROM DEPT_SALES TABLE
WHERE DEPT = 100
AND
(
(SALES_DATE BETWEEN (CURRENT DATE - 3 DAYS) AND CURRENT DATE
AND SALES_HR_NBR <= HOUR(CURRENT TIME - 1 HOUR)
AND HOUR(CURRENT TIME) = 0)
OR
(SALES_DATE BETWEEN (CURRENT DATE - 2 DAYS) AND CURRENT DATE
AND SALES_HR_NBR <= HOUR(CURRENT TIME - 1 HOUR)
AND HOUR(CURRENT TIME) <> 0)
)
ORDER BY SALES_DATE,SALES_HR_NBR
WITH UR
|
|
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Rahul,
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) |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Rahul,
I tried it but it pull sales for all dates with sales hour number <= current time(prev hour) |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| balaji81_k wrote: |
Rahul,
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 may need to do something with the hours though.
. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
Try this:
| Code: |
SELECT DEPT_NBR, SALES_AMT,SALES_DATE,SALES_HR_NBR
FROM DEPT_SALES TABLE
WHERE DEPT = 100
AND
(
(SALES_DATE = (CURRENT DATE - 3 DAYS)
AND SALES_HR_NBR >= HOUR(CURRENT TIME - 1 HOUR)
AND HOUR(CURRENT TIME) = 0)
OR
(SALES_DATE = (CURRENT DATE - 2 DAYS)
AND HOUR(CURRENT TIME) = 0)
)
OR
(SALES_DATE = (CURRENT DATE - 1 DAYS)
AND HOUR(CURRENT TIME) = 0)
)
OR
(SALES_DATE = (CURRENT DATE - 2 DAYS)
AND SALES_HR_NBR >= HOUR(CURRENT TIME - 1 HOUR)
AND HOUR(CURRENT TIME) <> 0)
OR
(SALES_DATE = (CURRENT DATE - 1 DAYS)
AND HOUR(CURRENT TIME) <> 0)
)
)
ORDER BY SALES_DATE,SALES_HR_NBR
WITH UR |
*edited to change BETWEEN to = |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| prino wrote: |
| Use timestamps |
What prino meant was:
| Code: |
WHERE TIMESTAMP(SALES_DATE,CONCAT(SALES_HR_NBR,':00:00'))
BETWEEN (CURRENT TIMESTAMP - 2 DAYS)
AND (CURRENT TIMESTAMP - 1 HOURS) |
Adjust the DAYS and HOURS until you're satisfied.
If you plan to write more DB2, then you must have this book open on your desktop: SQL Reference (search for the one corresponding to your DB2 version) |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
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. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Every one ,
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 .
Thanks
Balaji |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
Hi Guys,
I come up with below queries to convert SALES_DATE and Current Date for the required format . Need to handle only for HOUR(CURRENT TIME ) = 0 th hour.
| Code: |
SELECT INT(REPLACE(CHAR(current date, ISO),'-','')) || HOUR(CURRENT TIME)
FROM SALES_TABLE
SELECT INT(REPLACE(CHAR(SALES_DATE, ISO),'-','')) || SUBSTR(DIGITS(SALES_HR_NBR),4,2)
FROM SALES_TABLE
|
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.
Thanks
Balaji |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| I can add only one word: KISS |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| Nic Clouston wrote: |
| 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. |
Thanks Nic for the Advise. |
|
| Back to top |
|
|
|
|
