Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Extract Data till prev hour

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Fri Aug 25, 2017 9:25 pm    Post subject: Extract Data till prev hour
Reply with quote

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
View user's profile Send private message

prino

Senior Member


Joined: 07 Feb 2009
Posts: 1067
Location: Oostende, Belgium

PostPosted: Fri Aug 25, 2017 10:49 pm    Post subject:
Reply with quote

Use timestamps
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Fri Aug 25, 2017 11:11 pm    Post subject:
Reply with quote

Hi Prino,

Can you please help on Where/how to use timestamp in this query ?

Thanks
Balaji
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1817
Location: NY,USA

PostPosted: Mon Aug 28, 2017 11:04 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Mon Aug 28, 2017 11:17 pm    Post subject: Reply to: Extract Data till prev hour
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 408
Location: USA

PostPosted: Mon Aug 28, 2017 11:45 pm    Post subject: Reply to: Extract Data till prev hour
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Tue Aug 29, 2017 1:14 am    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Tue Aug 29, 2017 1:25 am    Post subject:
Reply with quote

Rahul,

I tried it but it pull sales for all dates with sales hour number <= current time(prev hour)
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 408
Location: USA

PostPosted: Tue Aug 29, 2017 1:28 am    Post subject:
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 408
Location: USA

PostPosted: Tue Aug 29, 2017 1:44 am    Post subject: Reply to: Extract Data till prev hour
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1247
Location: Israel

PostPosted: Tue Aug 29, 2017 5:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1817
Location: NY,USA

PostPosted: Tue Aug 29, 2017 9:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Wed Aug 30, 2017 9:20 am    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Wed Aug 30, 2017 9:58 am    Post subject: Reply to: Extract Data till prev hour
Reply with quote

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
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1247
Location: Israel

PostPosted: Wed Aug 30, 2017 3:06 pm    Post subject:
Reply with quote

I can add only one word: KISS
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1922
Location: UK

PostPosted: Wed Aug 30, 2017 4:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 118

PostPosted: Wed Aug 30, 2017 7:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm
No new posts How to Extract BMS Marco Source from ... Howard Hinman CICS 5 Tue Oct 31, 2017 11:13 pm
This topic is locked: you cannot edit posts or make replies. Can a alphanumeric data be moved to a... nico_neoz COBOL Programming 10 Mon Oct 30, 2017 8:28 am
No new posts Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us