|
View previous topic :: View next topic
|
| Author |
Message |
samuel_Inba
New User
Joined: 03 Jan 2008
Posts: 53
Location: Chennai
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
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 |
|
|
samuel_Inba
New User
Joined: 03 Jan 2008
Posts: 53
Location: Chennai
|
|
|
|
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 |
|
|
|
|
