MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Select to return Quarter End Dates

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
PostPosted: Mon Sep 29, 2008 10:49 am    Post subject: Select to return Quarter End Dates Reply with quote
I'm trying to come with a query to return the previous quarter end date.
I want this to be dynamic and never need to change. No hard coding.
Any help would be appreciated.
For 2008 the dates would be:
2008-03-31
2008-06-30
2008-09-30
2008-12-31
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
jsharon1248
Intermediate


Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
PostPosted: Mon Sep 29, 2008 11:26 am    Post subject: Reply with quote
The mm-dd values never change for quarter end; only the year. If you're willing to ease up on the
Quote:
No hard coding
requirement, DB2 has a QUARTER function that returns an integer that represents the current quarter. Using a CASE, you should be able to string the current year and the appropriate hard-coded mm-dd based on the value passed back from the QUARTER function. Something like this should work:

Code:
SELECT  QUARTER(DATE('2008-01-12'))                                     
       ,CASE QUARTER(DATE('2008-01-12'))                                 
          WHEN 1 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE) - 1),4)       
                       || '-12-31')                                                   
          WHEN 2 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-03-31')                                                   
          WHEN 3 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-06-30')                                                   
          WHEN 4 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-09-30')                                                   
        END                                                             
  FROM  SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
PostPosted: Mon Sep 29, 2008 11:41 am    Post subject: Reply with quote
jsharon1248 wrote:
The mm-dd values never change for quarter end; only the year. If you're willing to ease up on the
Quote:
No hard coding
requirement, DB2 has a QUARTER function that returns an integer that represents the current quarter. Using a CASE, you should be able to string the current year and the appropriate hard-coded mm-dd based on the value passed back from the QUARTER function. Something like this should work:

Code:
SELECT  QUARTER(DATE('2008-01-12'))                                     
       ,CASE QUARTER(DATE('2008-01-12'))                                 
          WHEN 1 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE) - 1),4)       
                       || '-12-31')                                                   
          WHEN 2 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-03-31')                                                   
          WHEN 3 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-06-30')                                                   
          WHEN 4 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-09-30')                                                   
        END                                                             
  FROM  SYSIBM.SYSDUMMY1;




Thanks jsharon1248 for the reply/query. I made a slight change and the date is Not hard coded. I tested it and it seems to work.
Code:

SELECT  QUARTER(DATE(current date))                                     
       ,CASE QUARTER(DATE(current date))                                 
          WHEN 1 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE) - 1),4)       
                       || '-12-31')                                                   
          WHEN 2 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-03-31')                                                   
          WHEN 3 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-06-30')                                                   
          WHEN 4 THEN DATE(RIGHT(DIGITS(YEAR(CURRENT DATE)),4)           
                       || '-09-30')                                                   
        END                                                             
  FROM  SYSIBM.SYSDUMMY1;

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
Dibakar
Advanced


Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
PostPosted: Mon Sep 29, 2008 1:57 pm    Post subject: Reply with quote
not tested but think this should also work-

Code:

Select
  date (YEAR(CURRENT DATE) ||'-' ||(((QUARTER(DATE(CURRENT DATE)) -1) *3) +1) ||'-01') -1)   
FROM  SYSIBM.SYSDUMMY1;   


Edited to add logic -

logic: last_day_of_previous_quarter = first_day_of_current_quarter - 1

Diba
Back to top
View user's profile Send private message Send e-mail
jsharon1248
Intermediate


Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
PostPosted: Mon Sep 29, 2008 3:01 pm    Post subject: Reply with quote
There were a couple type mismatches. This will work:

Code:
SELECT DATE(                                                   
   RIGHT(DIGITS(YEAR(CURRENT DATE)),4)                         
   || '-'                                                       
   ||  RIGHT(DIGITS(((QUARTER(CURRENT DATE) - 1) * 3) + 1),2)   
   || '-01') - 1 DAY                                           
FROM  SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Sep 29, 2008 4:15 pm    Post subject: Reply with quote
NASCAR9,

Untested SQL but should work. Just subtract the dayoftheyear from the current date which would give the last day of the prev year and add 3,6,9,12 months and get the last day of the month

Code:

SELECT LAST_DAY(CURRENT DATE - DAYOFYEAR(CURRENT DATE) DAYS + 03 MONTHS)
      ,LAST_DAY(CURRENT DATE - DAYOFYEAR(CURRENT DATE) DAYS + 06 MONTHS)
      ,LAST_DAY(CURRENT DATE - DAYOFYEAR(CURRENT DATE) DAYS + 09 MONTHS)
      ,LAST_DAY(CURRENT DATE - DAYOFYEAR(CURRENT DATE) DAYS + 12 MONTHS)
  FROM SYSIBM.SYSDUMMY1;                                               
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
PostPosted: Mon Sep 29, 2008 9:14 pm    Post subject: Reply with quote
jsharon1248, Diba, and kolusu, Thank you all very much! This is one of the reasons I visit this board many times a day. The DB2 knowlege is OUTSTANDING! This topic surley will be of value to someone else in the future. Very Happy
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
Dibakar
Advanced


Joined: 02 Dec 2002
Posts: 700
Topics: 63
Location: USA
PostPosted: Tue Sep 30, 2008 9:46 am    Post subject: Reply with quote
Thanks jsharon1248 for correcting my code.

I am refreshing DB2 (without access to DB2) for interview preparation. I took clue from your solution but forgot the types.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group