| View previous topic :: View next topic |
| Author |
Message |
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
 Posted: Mon Sep 28, 2009 1:11 pm Post subject: Get 12 Periods(Monthly, Quarterly or Yearly) from given date |
 |
|
Hi,
I am back with again a query on dates logic. However, It is not a problem. I already have the queries with me ready giving correct output. However, they are 3 different queries to get 12 periods from the given date for month, quarter or year. I wanted to know if anybody knows if these 3 different queries can be merged to one single query.
My requirement is to get 12 periods out of the given date. Either, monthly, quarterly or yearly. My Input is: Given_date and a parameter saying if it's monthly, quarterly or yearly.
fyi. I am not giving full queries. Just the main essence part of it.
MONTHLY | Code: | | LAST_DAY(Given_date) + 1 DAY - 12 MONTHS |
QUARTERLY | Code: | CASE
WHEN ( MONTH(Given_date) = 01
OR MONTH(Given_date) = 04
OR MONTH(Given_date) = 07
OR MONTH(Given_date) = 10)
THEN LAST_DAY(Given_date) + 1 DAY - 34 MONTHS
WHEN ( MONTH(Given_date) = 02
OR MONTH(Given_date) = 05
OR MONTH(Given_date) = 08
OR MONTH(Given_date) = 11)
THEN LAST_DAY(Given_date) + 1 DAY - 35 MONTHS
ELSE
LAST_DAY(Given_date) + 1 DAY - 36 MONTHS
END |
YEARLY: | Code: | | Given_date - ( (DAYOFYEAR(Given_date)) - 1) DAYS - 11 YEARS |
Thanks.
_________________
Thanks. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Sep 28, 2009 1:31 pm Post subject: |
|
|
seekaysk,
Is the 12 periods inclusive of the given date ? Also I don't get the quarterly logic. It would be better if you showed us an example.
ex: what do you expect the values for the following dates and explain the reason also for all the 3 formats
| Code: |
2009-01-01 - Januray 1st
2009-03-31 - quarter end
2009-04-01 - quarter begin
2009-06-30 quarter end
..
2009-09-28 current date
2008-02-29 leap year date feb 29
2009-12-31
|
What do you expect the final result to be for the above mentioned dates? |
|
| Back to top |
|
|
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
| Posted: Mon Sep 28, 2009 1:55 pm Post subject: |
|
|
Thanks Kolusu.
Yes, the 12 periods are inclusive of the given date. Ultimately, the From_date extracted out of these queries will help to get the necessary data from the other tables. These periods data will then be shown on the report and the report has only 12 columns limit to show. Hence, 12 months or 12 quarters or 12 years. The given_date is the To-date of the report and hence have to find the From_date of the report such that only 12 periods (or columns in the report) of data is extracted.
Running the queries that I provided, below are the result for the To_dates requested by you.
| Code: | PM_PRS_DT MONTHLY_FROM_DATE QUARTERLY_FROM_DATE YEARLY_FROM_DATE
---------+---------+---------+---------+---------+---------+---------
2008-02-29 2007-03-01 2005-04-01 1997-01-01
2009-01-01 2008-02-01 2006-04-01 1998-01-01
2009-03-31 2008-04-01 2006-04-01 1998-01-01
2009-04-01 2008-05-01 2006-07-01 1998-01-01
2009-06-30 2008-07-01 2006-07-01 1998-01-01
2009-09-28 2008-10-01 2006-10-01 1998-01-01
2009-12-31 2009-01-01 2007-01-01 1998-01-01 |
e.g. Let's take quarterly. If To_date is 2008-02-29, Then from_date has to be 2005-04-01. Similarly, if To_date is any day in 2008-Jan or 2008-March, even then the From_date will be 2005-april-01. In terms of Quarters, you would see that it is 12 quarters and hence 12 columns on the report.
Hope I am bit more clearer this time.
Thanks.
_________________
Thanks. |
|
| Back to top |
|
|
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
| Posted: Mon Sep 28, 2009 2:58 pm Post subject: |
|
|
To give more insight, this is the query I have build up. (Nothing but joining all 3 queries using CASE statement).
It is not embedded in program and hence not tested with any syntax or so errors. I am assuming here that nested case statements are allowed on db2 7 and higher.
| Code: | SELECT
CASE
WHEN :WS-MONTH-IND = 'Q'
THEN CASE
WHEN ( MONTH(:Given_date) = 01
OR MONTH(:Given_date) = 04
OR MONTH(:Given_date) = 07
OR MONTH(:Given_date) = 10)
THEN LAST_DAY(:Given_date) +
1 DAY - 34 MONTHS
WHEN ( MONTH(:Given_date) = 02
OR MONTH(:Given_date) = 05
OR MONTH(:Given_date) = 08
OR MONTH(:Given_date) = 11)
THEN LAST_DAY(:Given_date) +
1 DAY - 35 MONTHS
ELSE LAST_DAY(:Given_date) +
1 DAY - 36 MONTHS
END
WHEN :WS-MONTH-IND = 'Y'
THEN :Given_date -
((DAYOFYEAR(:Given_date)) - 1) DAYS -
11 YEARS
ELSE LAST_DAY(:Given_date) + 1 DAY -
12 MONTHS
END
INTO :WS-ADJUSTED-FROM-DATE
FROM SYSIBM.SYSDUMMY1 |
To re-iterate on requirement, instead of writing a CASE with 3 WHENs (for monthly, quarterly or yearly), can we have one single formula common to all three ?
Thanks.
_________________
Thanks. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Sep 28, 2009 4:47 pm Post subject: |
|
|
seekaysk,
The quarterly period date can be done without using the case statement
try this untested sql
| Code: |
SELECT DATE(Given_date) - DAYOFYEAR(Given_date) DAYS +
(QUARTER(Given_date) * 3) MONTHS + 1 DAY - 36 MONTHS
FROM TABLE
|
kolusu |
|
| Back to top |
|
|
seekaysk
Beginner
Joined: 31 Aug 2007
Posts: 49
Topics: 15
|
| Posted: Tue Sep 29, 2009 8:43 am Post subject: |
|
|
Thanks a lot Kolusu. This should be fine.
Also while testing I was receiving 01056 sqlstate on the formula given by you for date adjustments. Hence, for documentation I am presenting the modified one that does not give this sqlstate.
| Code: | SELECT DATE(Given_date) - DAYOFYEAR(Given_date) DAYS + 1 DAY +
(QUARTER(Given_date) * 3) MONTHS - 36 MONTHS
FROM TABLE |
Thanks again.
_________________
Thanks. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
