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 

SQL Challenge - II

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Mainframe Challenge
View previous topic :: View next topic  
Author Message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11794
Topics: 74
Location: San Jose

PostPosted: Mon Jan 03, 2005 11:17 am    Post subject: SQL Challenge - II Reply with quote

1.Find the 3rd saturday of any given month. The input to you is a date in CCYY-MM-DD format. You need to find the 3rd saturday of that month.


For ex: take current date 2005-01-03 , now the third saturday of this month is 2005-01-15.

2. Find the difference between any 2 dates in terms of months.

Date 1 : 2004-05-01 (CCYY-MM-DD)

Date 2 : 2005-01-03 (CCYY-MM-DD)


The difference between these 2 dates is 8 months.

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11794
Topics: 74
Location: San Jose

PostPosted: Wed Jan 05, 2005 11:22 am    Post subject: Reply with quote

Well No one has attempted to answer this. so let me go ahead and post the solutions. The clue to the first question was posted in this topic

http://www.mvsforums.com/helpboards/viewtopic.php?t=3461

1.Find the 3rd saturday of any given month. The input to you is a date in CCYY-MM-DD format. You need to find the 3rd saturday of that month.

Here is the actual solution.

Code:

SELECT A.SAT3 FROM (                                                 
SELECT DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT')) 
       SAT1                                                         
      ,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
             7 DAYS ) SAT2                                           
      ,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
            14 DAYS ) SAT3                                           
      ,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
            21 DAYS ) SAT4                                           
      ,DATE(NEXT_DAY(LAST_DAY(DATE('2005-01-03') - 1 MONTH),'SAT') +
            28 DAYS ) SAT5                                           
FROM SYSIBM.SYSDUMMY1) A                                             


Check this link for a detailed explanation of Last_Day scalar function

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.40?SHELF=&DT=20010718164132&CASE=

Check this link for a detailed explanation of NEXT_Day scalar function

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2.56?SHELF=&DT=20010718164132&CASE=

2. Find the difference between any 2 dates in terms of months.

Date 1 : 2004-05-01 (CCYY-MM-DD)

Date 2 : 2005-01-03 (CCYY-MM-DD)

Solution:

Code:

SELECT INT(SUBSTR(DIGITS(                                       
           DATE('2005-01-03') - DATE('2004-05-01')),1,4)) * 12 +
       INT(SUBSTR(DIGITS(                                       
           DATE('2005-01-03') - DATE('2004-05-01')),5,2))       
  FROM SYSIBM.SYSDUMMY1;                                       


The difference between any 2 dates is given decimal number which specifies the number of years, months, and days between the two dates.

We convert the number into character form using DIGITS function. By doing so the the difference between the dates is given as Character form of length 8(4 bytes for the year , 2 bytes for the months and 2 bytes for the days)

So we multiply the year portion with 12 ( 1 year= 12 months) and add it to the bytes at 5 which holds the months.

Thanks

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mummanen
Beginner


Joined: 02 Feb 2004
Posts: 4
Topics: 1
Location: Sacramento/USA

PostPosted: Tue Aug 23, 2005 4:09 pm    Post subject: Reply with quote

The below SQL will work for differance between two dates:

select round(months_between(to_date('2005-01-03', 'YYYY-MM-DD'),to_date('2004-05-01', 'YYYY-MM-DD')))
from dual;
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11794
Topics: 74
Location: San Jose

PostPosted: Tue Aug 23, 2005 4:56 pm    Post subject: Reply with quote

mummanen,

I beleive that your query works only with Oracle . Db2 does not have the function Months_between

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mummanen
Beginner


Joined: 02 Feb 2004
Posts: 4
Topics: 1
Location: Sacramento/USA

PostPosted: Wed Aug 24, 2005 12:09 pm    Post subject: Reply with quote

I didn't realize that you are talking about only DB2 SQL's.
Back to top
View user's profile Send private message
imone
Beginner


Joined: 21 Sep 2005
Posts: 1
Topics: 0

PostPosted: Wed Sep 21, 2005 4:46 pm    Post subject: Reply with quote

This can be used to get Third saturday of any month.
Code:

SELECT (DATE('2005-02-28') - DAY('2005-02-28') DAY + 1 DAY) +         
((7 - DAYOFWEEK(DATE('2005-02-28') - DAY('2005-02-28') DAY + 1 DAY))+
14) DAY AS "THIRD SATURDAY"                                           
FROM SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11794
Topics: 74
Location: San Jose

PostPosted: Wed Sep 21, 2005 5:37 pm    Post subject: Reply with quote

Good One Imone !

kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Pknair
Beginner


Joined: 29 Nov 2005
Posts: 26
Topics: 9

PostPosted: Fri Apr 28, 2006 10:05 am    Post subject: Reply with quote

Ths also gives you the 3rd saturday
Code:

 SELECT 14+8-DAYOFWEEK(DATE(SUBSTR('2006-01-01',6,2)||'/01/'||   
              SUBSTR('2006-01-01',1,4)))                         
  FROM SYSIBM.SYSDUMMY1                                           

pknair
Back to top
View user's profile Send private message
Pknair
Beginner


Joined: 29 Nov 2005
Posts: 26
Topics: 9

PostPosted: Fri Apr 28, 2006 10:11 am    Post subject: Reply with quote

I am sorry it loks similar to Imone's posting

pknair
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Mainframe Challenge 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