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 |
Code: |
KEY JAN FEB MAR APR ABC 100 200 300 400 XYZ 0 500 600 700 |
Quote: |
2. How do you display all the days in week. The week begins on a monday and ends on sunday. |
Quote: |
SELECT ' The Day Name is ' || DSN8.DAYNAME( <Date> ) || ', ' || CHAR( <Date> ) FROM SYSIBM.SYSDUMMY1; |
Code: |
2004-04-24 2004-04-25 2004-04-26 2004-04-27 2004-04-28 2004-04-29 2004-04-30 |
Code: |
SELECT DAYOFWEEK(CURRENT DATE) FROM TABLE ; |
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; |
Code: |
1. 10 2. 4* Total : 14 |
Code: |
2004-03-08 2004-03-09 2004-03-10 2004-03-11 2004-03-12 2004-03-13 2004-03-14 |
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 |
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); |
Code: |
SELECT * FROM TABLE WHERE ((DAYS(TAB_DATE)-1)/7) = ((DAYS('2004-01-01')-1)/7) ; |
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; |
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 |
output generated using printer-friendly topic mod. All times are GMT - 5 Hours