| View previous topic :: View next topic |
| Author |
Message |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
 Posted: Wed May 26, 2004 7:43 am Post subject: SQL Challenge - I |
 |
|
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
www.linkedin.com/in/kolusu |
|
| Back to top |
|
 |
NutCracker
Beginner
Joined: 13 Dec 2002
Posts: 45
Topics: 3
Location: 3rd Block from the SUN
|
| Posted: Thu May 27, 2004 2:35 am Post subject: |
|
|
| 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 |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 5:46 am Post subject: |
|
|
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
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 5:51 am Post subject: |
|
|
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
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 8:08 am Post subject: |
|
|
Ravi,
The following is my rating for your queries.
| Code: |
1. 10
2. 4*
Total : 14
|
* You are making it way too complicated
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 10:09 am Post subject: |
|
|
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
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 27, 2004 3:05 pm Post subject: |
|
|
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
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri May 28, 2004 8:04 am Post subject: |
|
|
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
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Pknair
Beginner
Joined: 29 Nov 2005
Posts: 26
Topics: 9
|
| Posted: Fri Apr 28, 2006 5:43 pm Post subject: |
|
|
| 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 |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
