Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 6968
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: 1232
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 Reg:Displaying the particular month o... bhavana yalavarthi All Other Mainframe Topics 4 Sat Jul 08, 2017 8:13 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us