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
 
need to get prior month in a query.

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

New User


Joined: 03 Jan 2008
Posts: 53
Location: Chennai

PostPosted: Mon Apr 21, 2008 4:44 pm    Post subject: need to get prior month in a query.
Reply with quote

Hi,
I have a 2 requirement in which

1)i need to get the data which belongs to previous month.Suppose if i run the Job this month (apr) it should fetch all the data of previous month(march) and this should happen for every month.

2) according to the month retrieved from the system at run time, I need to pass two values to the select query's where clause.

suppose the month retrieve at run time is apr thn i need to pass 15 and 17 to my where clause...if it is may i need to pass 21 and 25.... can i use a case statement to do this ?

pls suggest....


Thanks,
Sam.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Mon Apr 21, 2008 5:19 pm    Post subject:
Reply with quote

suggest you try.

why not use (current_month - 1) in your query?

and what are the 15/17, 21/25? tried to figure it out, but I see no rationale between apr/15/17 and may/21/25. but that is your business and yes you can case a lot of things.

but, it depends on what you want to do. you are not simply extracting last months data. you are extracting last month data depending on a value (21/25, 15/17) based on a month.

explain a little more, what do you actually want to do?

and will this be a qmf proc/cobol program,... ?
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1252
Location: Israel

PostPosted: Mon Apr 21, 2008 5:27 pm    Post subject:
Reply with quote

1) To get records from last month, I use the following WHERE statement:
Code:
WHERE                                                 
   DATE_FIELD BETWEEN LAST_DAY(CURRENT_DATE - 2 MONTH) + 1 DAY
                  AND LAST_DAY(CURRENT_DATE - 1 MONTH)


2) Yes, you can use CASE in the WHERE statement:
Code:
WHERE
   DEPTNO BETWEEN (CASE MONTH(DATE_FIELD)
                                 WHEN 01 THEN 10
                                 WHEN 02 THEN 19
                                 ELSE 98
                           END)
                       AND (CASE MONTH(DATE_FIELD)
                                 WHEN 01 THEN 15
                                 WHEN 02 THEN 21
                                 ELSE 99
                           END)
Back to top
View user's profile Send private message
samuel_Inba

New User


Joined: 03 Jan 2008
Posts: 53
Location: Chennai

PostPosted: Mon Apr 21, 2008 7:32 pm    Post subject:
Reply with quote

Thanks guys for your quick response...

Here is my requirements:

I have two tables A which has hours recorded with month as a primary field
and table B has hours with Fiscal week as primary field.
every month will have 5 fiscal week or 4 depending on the no of days...
jan will hv 5 Fiscal weeks and feb's first FW will be 6...
Now i need to find the diff of hours data for a particular month (say apr) from tbl A and tbl B (note tbl B has Fiscal Wk format).


thanks.
Sam.
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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm

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