SQL Challenge - I
Select messages from
 # through # FAQ
[/[Print]\]

MVSFORUMS.com -> Mainframe Challenge
#1: SQL Challenge - I Author: kolusu, Location: San Jose PostPosted: Wed May 26, 2004 7:43 am
    —
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
#2:  Author: NutCracker, Location: 3rd Block from the SUN PostPosted: Thu May 27, 2004 2:35 am
    —
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.
#3:  Author: kolusu, Location: San Jose PostPosted: Thu May 27, 2004 5:46 am
    —
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
#4:  Author: kolusu, Location: San Jose PostPosted: Thu May 27, 2004 5:51 am
    —
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
#5:  Author: kolusu, Location: San Jose PostPosted: Thu May 27, 2004 8:08 am
    —
Ravi,

The following is my rating for your queries.

Code:

1. 10
2. 4*
Total : 14



* You are making it way too complicated
#6:  Author: kolusu, Location: San Jose PostPosted: Thu May 27, 2004 10:09 am
    —
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
#7:  Author: kolusu, Location: San Jose PostPosted: Thu May 27, 2004 3:05 pm
    —
Ravi,

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

Kolusu
#8:  Author: kolusu, Location: San Jose PostPosted: Fri May 28, 2004 8:04 am
    —
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
#9:  Author: Pknair,  PostPosted: Fri Apr 28, 2006 5:43 pm
    —
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
MVSFORUMS.com -> Mainframe Challenge


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group