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 - I

 
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: 12358
Topics: 75
Location: San Jose

PostPosted: Wed May 26, 2004 7:43 am    Post subject: SQL Challenge - I Reply with quote

Look at the following test data
Code:

key      mon      Qty
ABC      JAN       100
ABC      FEB       200
ABC      MAR       300
ABC      APR       400
XYZ      FEB       500
XYZ      MAR       600
XYZ      APR       700



I need an sql to get the results in the following Format. The qty is defined as integer in the table

Code:

KEY    JAN     FEB   MAR   APR

ABC     100    200   300   400
XYZ     0      500   600   700


The data should be display horizontally. If the data for a particular month is missing then it should be populated with zero

2. How do you display all the days in week. The week begins on a monday and ends
on sunday.

3. we use the hex function to convert a number into hex format. But I want the reverse using sql .i.e take in a number in hex and convert that back to the orginal number. The value of 100 in hex is 00000064. Now using this 00000064 I want to get back to 100.

I will post the solutions on Friday

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NutCracker
Beginner


Joined: 13 Dec 2002
Posts: 45
Topics: 3
Location: 3rd Block from the SUN

PostPosted: Thu May 27, 2004 2:35 am    Post subject: Reply with quote

Quote:

2. How do you display all the days in week. The week begins on a monday and ends on sunday.


User-Defined function DAYNAME gives the Name of the WeekDay.
The example below is untested, but I think this may be a lead.

Quote:

SELECT ' The Day Name is '
|| DSN8.DAYNAME( <Date> ) || ', '
|| CHAR( <Date> )
FROM SYSIBM.SYSDUMMY1;


PS: Admin, Please delete my earlier post/reply on the same subject; I am not able to edit it.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu May 27, 2004 5:46 am    Post subject: Reply with quote

ravi,

The expected output is all days in a given week. For example today is thursday(May 27th) , so If you take current date , the output should have dates from monday thru sunday

Code:

2004-04-24
2004-04-25
2004-04-26
2004-04-27
2004-04-28
2004-04-29
2004-04-30


Here 24th is a monday and 30th is a sunday

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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: 12358
Topics: 75
Location: San Jose

PostPosted: Thu May 27, 2004 5:51 am    Post subject: Reply with quote

nutcracker,

The challenge here is to get ALL days in a week but not the dayname . You don't need an user defined function to get the dayname.

With the latest version of DB2
Code:

SELECT DAYOFWEEK(CURRENT DATE)     
  FROM TABLE
;


Older version of DB2

Code:

SELECT (CASE DAYOFWEEK('2004-04-29')
        WHEN 1 THEN  'SUNDAY'       
        WHEN 2 THEN  'MONDAY'       
        WHEN 3 THEN  'TUESDAY'       
        WHEN 4 THEN  'WEDNESDAY'     
        WHEN 5 THEN  'THURSDAY'     
        WHEN 6 THEN  'FRIDAY'       
        WHEN 7 THEN  'SATURDAY'     
       END) AS WEEKDAY               
  FROM SYSIBM.SYSDUMMY1; 


Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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: 12358
Topics: 75
Location: San Jose

PostPosted: Thu May 27, 2004 8:08 am    Post subject: Reply with quote

Ravi,

The following is my rating for your queries.

Code:

1. 10
2. 4*
Total : 14



* You are making it way too complicated
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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: 12358
Topics: 75
Location: San Jose

PostPosted: Thu May 27, 2004 10:09 am    Post subject: Reply with quote

Question 2 Update:

I sincerely apolozise for framing the question incorrectly. The actual challenge is

Let us say there is a table which contains only dates for all the years starting from 1950 to 2050 (CCYY-MM-DD format). ie. for every year this table will have all the dates starting from January 1st to December 31.

Now I will provide you with a date which is between 1950 and 2050. You need to get all the dates for the week in which this date falls in.

Let us say I provide you the date as 2004-03-12 (2004 March 12th friday). The output should be

Code:

2004-03-08
2004-03-09
2004-03-10
2004-03-11
2004-03-12
2004-03-13
2004-03-14


Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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: 12358
Topics: 75
Location: San Jose

PostPosted: Thu May 27, 2004 3:05 pm    Post subject: Reply with quote

Ravi,

Your solution will not work for all cases. Especially the beginning of the years. try with given date as 2000-01-01

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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: 12358
Topics: 75
Location: San Jose

PostPosted: Fri May 28, 2004 8:04 am    Post subject: Reply with quote

Here are the answers for the questions:

1. display the values in horizontal fashion.

Code:

SELECT KEY
      ,SUM(CASE WHEN MON = 'JAN' THEN QTY ELSE 0 END) AS JAN
      ,SUM(CASE WHEN MON = 'FEB' THEN QTY ELSE 0 END) AS FEB
      ,SUM(CASE WHEN MON = 'MAR' THEN QTY ELSE 0 END) AS MAR
      ,SUM(CASE WHEN MON = 'APR' THEN QTY ELSE 0 END) AS APR
  FROM TABLE
 GROUP BY KEY



2. To find all the dates in a week which starts from monday and ends on a sunday

Code:

SELECT *                                                           
  FROM TABLE                                                       
 WHERE TAB_DATE >= (SELECT DATE(DATE('2004-01-01') - A.WNUM DAYS)   
                          FROM (SELECT (CASE DAYOFWEEK('2004-01-01')
                                        WHEN 1 THEN  6             
                                        WHEN 2 THEN  0             
                                        WHEN 3 THEN  1             
                                        WHEN 4 THEN  2             
                                        WHEN 5 THEN  3             
                                        WHEN 6 THEN  4             
                                        WHEN 7 THEN  5             
                                        END) AS WNUM               
                                   FROM SYSIBM.SYSDUMMY1) A)       
   AND TAB_DATE <= (SELECT DATE(DATE('2004-01-01') + B.WNUM DAYS)   
                          FROM (SELECT (CASE DAYOFWEEK('2004-01-01')
                                        WHEN 1 THEN  0             
                                        WHEN 2 THEN  6             
                                        WHEN 3 THEN  5             
                                        WHEN 4 THEN  4             
                                        WHEN 5 THEN  3             
                                        WHEN 6 THEN  2             
                                        WHEN 7 THEN  1             
                                        END) AS WNUM               
                                   FROM SYSIBM.SYSDUMMY1) B);       



The first sub-query will fetch the monday date and the second sub-query will fetch the sunday's date.

I guess ravi's second solution is better than this, but I haven't fully tested it out.

Code:

SELECT *
  FROM TABLE
 WHERE ((DAYS(TAB_DATE)-1)/7) = ((DAYS('2004-01-01')-1)/7)
 ;



3.Get the number from hex format
Code:

SELECT (CASE SUBSTR('00000064',1,1)     
        WHEN 'A' THEN  10               
        WHEN 'B' THEN  11               
        WHEN 'C' THEN  12               
        WHEN 'D' THEN  13               
        WHEN 'E' THEN  14               
        WHEN 'F' THEN  15               
        WHEN '0' THEN  0                 
        WHEN '1' THEN  1                 
        WHEN '2' THEN  2                 
        WHEN '3' THEN  3                 
        WHEN '4' THEN  4                 
        WHEN '5' THEN  5                 
        WHEN '6' THEN  6                 
        WHEN '7' THEN  7                 
        WHEN '8' THEN  8                 
        WHEN '9' THEN  9                 
        END) * POWER(16,7) +             
          (CASE SUBSTR('00000064',2,1)   
        WHEN 'A' THEN  10             
        WHEN 'B' THEN  11             
        WHEN 'C' THEN  12             
        WHEN 'D' THEN  13             
        WHEN 'E' THEN  14             
        WHEN 'F' THEN  15             
        WHEN '0' THEN  0             
        WHEN '1' THEN  1             
        WHEN '2' THEN  2             
        WHEN '3' THEN  3             
        WHEN '4' THEN  4             
        WHEN '5' THEN  5             
        WHEN '6' THEN  6             
        WHEN '7' THEN  7             
        WHEN '8' THEN  8             
        WHEN '9' THEN  9             
        END) * POWER(16,6) +         
       (CASE SUBSTR('00000064',3,1)   
        WHEN 'A' THEN  10             
        WHEN 'B' THEN  11             
        WHEN 'C' THEN  12             
        WHEN 'D' THEN  13             
        WHEN 'E' THEN  14             
        WHEN 'F' THEN  15             
        WHEN '0' THEN  0             
        WHEN '1' THEN  1             
        WHEN '2' THEN  2             
        WHEN '3' THEN  3             
        WHEN '4' THEN  4             
        WHEN '5' THEN  5             
        WHEN '6' THEN  6             
        WHEN '7' THEN  7             
        WHEN '8' THEN  8             
        WHEN '9' THEN  9             
        END) * POWER(16,5) +         
          (CASE SUBSTR('00000064',4,1)     
        WHEN 'A' THEN  10               
        WHEN 'B' THEN  11               
        WHEN 'C' THEN  12               
        WHEN 'D' THEN  13               
        WHEN 'E' THEN  14               
        WHEN 'F' THEN  15               
        WHEN '0' THEN  0               
        WHEN '1' THEN  1               
        WHEN '2' THEN  2               
        WHEN '3' THEN  3               
        WHEN '4' THEN  4               
        WHEN '5' THEN  5               
        WHEN '6' THEN  6               
        WHEN '7' THEN  7               
        WHEN '8' THEN  8               
        WHEN '9' THEN  9               
        END) * POWER(16,4) +           
       (CASE SUBSTR('00000064',5,1)     
        WHEN 'A' THEN  10               
        WHEN 'B' THEN  11               
        WHEN 'C' THEN  12               
        WHEN 'D' THEN  13               
        WHEN 'E' THEN  14               
        WHEN 'F' THEN  15               
        WHEN '0' THEN  0               
        WHEN '1' THEN  1               
        WHEN '2' THEN  2               
        WHEN '3' THEN  3               
        WHEN '4' THEN  4               
        WHEN '5' THEN  5               
        WHEN '6' THEN  6               
        WHEN '7' THEN  7               
        WHEN '8' THEN  8               
        WHEN '9' THEN  9               
        END) * POWER(16,3) +           
          (CASE SUBSTR('00000064',6,1) 
        WHEN 'A' THEN  10           
        WHEN 'B' THEN  11           
        WHEN 'C' THEN  12           
        WHEN 'D' THEN  13           
        WHEN 'E' THEN  14           
        WHEN 'F' THEN  15           
        WHEN '0' THEN  0             
        WHEN '1' THEN  1             
        WHEN '2' THEN  2             
        WHEN '3' THEN  3             
        WHEN '4' THEN  4             
        WHEN '5' THEN  5             
        WHEN '6' THEN  6             
        WHEN '7' THEN  7             
        WHEN '8' THEN  8             
        WHEN '9' THEN  9             
        END) * POWER(16,2) +         
       (CASE SUBSTR('00000064',7,1) 
        WHEN 'A' THEN  10           
        WHEN 'B' THEN  11           
        WHEN 'C' THEN  12           
        WHEN 'D' THEN  13           
        WHEN 'E' THEN  14           
        WHEN 'F' THEN  15           
        WHEN '0' THEN  0             
        WHEN '1' THEN  1             
        WHEN '2' THEN  2             
        WHEN '3' THEN  3             
        WHEN '4' THEN  4             
        WHEN '5' THEN  5             
        WHEN '6' THEN  6             
        WHEN '7' THEN  7             
        WHEN '8' THEN  8             
        WHEN '9' THEN  9             
        END) * POWER(16,1) +         
          (CASE SUBSTR('00000064',8,1) 
        WHEN 'A' THEN  10           
        WHEN 'B' THEN  11           
        WHEN 'C' THEN  12           
        WHEN 'D' THEN  13           
        WHEN 'E' THEN  14           
        WHEN 'F' THEN  15           
        WHEN '0' THEN  0             
        WHEN '1' THEN  1             
        WHEN '2' THEN  2             
        WHEN '3' THEN  3             
        WHEN '4' THEN  4             
        WHEN '5' THEN  5             
        WHEN '6' THEN  6             
        WHEN '7' THEN  7             
        WHEN '8' THEN  8             
        WHEN '9' THEN  9             
        END) * POWER(16,0)           
        FROM SYSIBM.SYSDUMMY1;       



Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
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 5:43 pm    Post subject: Reply with quote

Code:

SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-8) DAYS               
 FROM SYSIBM.SYSDUMMY1                                                   
 UNION                                                                   
SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-7) DAYS               
 FROM SYSIBM.SYSDUMMY1                                                   
 UNION                                                                   
SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-6) DAYS               
 FROM SYSIBM.SYSDUMMY1                                                   
 UNION                                                                   
SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-5) DAYS               
 FROM SYSIBM.SYSDUMMY1                                                   
 UNION                                                                   
SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-4) DAYS               
 FROM SYSIBM.SYSDUMMY1                                                   
 UNION                                                                   
SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-3) DAYS               
 FROM SYSIBM.SYSDUMMY1                                                   
 UNION                                                                   
SELECT DATE('03/12/2004')-(DAYOFWEEK('03/12/2004')-2) DAYS       
 FROM SYSIBM.SYSDUMMY1                                           
 ORDER BY 1
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