Results 1 to 3 of 3

Thread: Query In Db2

  1. #1
    Join Date
    Nov 2008
    Posts
    9

    Unanswered: Query In Db2

    Below qry can be executed in sql server .. how to write in DB2 ?? does it need dummy tables??? without that anyway to write ????

    SELECT COUNT(*)
    FROM
    (
    SELECT 1 AS d
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    ) weekdays
    =======================

    Acutal qry need to be converted in db2 ... for business days
    ------------------------------------------------------------


    select (DATEDIFF(DAY, '2008/11/01', '2009/12/30')+1) / 7 * 5

    +

    ( (DATEDIFF(DAY, '2008/11/01', '2008/12/30')+1) % 7 )

    -

    (
    SELECT COUNT(*)
    FROM
    (
    SELECT 1 AS d
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    ) weekdays
    WHERE d <= ( (DATEDIFF(DAY, '2008/11/01', '2008/12/30')+1) % 7 ) % 7
    AND DATENAME(WEEKDAY, day('2008/12/30') - d + 1)
    IN
    (
    'Saturday',
    'Sunday'
    )
    )

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    SELECT needs a FROM clause, always. You can either use the VALUES statement instead or select from a 1-row table SYSIBM.SYSDUMMY1.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    Did I understand you right ?

    You have a start date (eg. 01.11.2008) and an end date (eg. 31.12.2009) and you want to know, how many mondays+tuesdays+wednesdays+thursdays+fridays are within that date range ?

    This query will do it:

    WITH ABCD ( DATUM ) AS
    ( SELECT DATE('01.11.2008') AS DATUM FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT ABCD.DATUM + 1 DAYS FROM ABCD X
    WHERE ABCD.DATUM < DATE('31.12.2009')
    )
    SELECT COUNT(*) FROM ABCD
    WHERE DAYOFWEEK(DATUM) IN (2,3,4,5,6)


    the dates may be specified in one of these formats:
    dd.mm.yyyy
    mm/dd/yyyy
    yyyy-mm-dd
    Last edited by umayer; 12-08-08 at 09:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •