Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: Problem regarding DB2 query

    Hi,
    I have 2 tables using which i have to populate a third table

    OffID ID Type DATE
    1 1 StartDate 12/10/2009
    1 2 EndDate 18/10/2009



    Period STARTDATE ENDDATE
    a 10/10/2009 20/10/2009
    b 21/10/2009 20/10/2009


    I need to populate a column CycID in the third table with the help of these two tables in which I have to mark the ID as a or b for OffID depending on the Startdate and enddate.
    Also if the diffrence of the Start Date and end date in the first table spans more than 20 days I have to mark it as C.
    Can somebody please suggest me the query i should frame?

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You'll have to try and restate the problem, showing us both the source tables and the desired result. Best of all, provide table DDL statements and sample data as INSERT statements, so that those willing to help can easily reproduce the data.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    Here is the sample data from the 1 st table
    START_DATE END_DATE PERIOD
    ---------- ---------- -----------------
    27/03/2008 22/04/2008 1
    23/05/2008 05/06/2008 3
    10/05/2008 01/06/2008 2
    10/04/2008 03/05/2008 1

    Here is the data for the second table

    PERIOD CODE START_DATE END_DATE
    --------------- ----------------- --------------------- -------------------
    1 a 26/03/2008 22/04/2008
    1 b 09/04/2008 06/05/2008

    2 a 23/04/2008 20/05/2008
    2 b 07/05/2008 03/06/2008

    3 a 21/05/2008 17/06/2008
    3 b 04/06/2008 01/07/2008

    Now i have to compare the start and end dates of the first able to the second table to get the code.The conditions are
    1)If start date and end date fall within a perid code say 1a then the code will be a.
    2)If it falls in between two periods then whichver code started first will get the code
    3)If it starts within a period and exceeds its end date mark it as c
    Note.A period would be a single row in the second table,eg 1a and 2b are diffrent periods
    The result should be like this
    START_DATE END_DATE PERIOD CODE
    ---------- ---------- ----------------- ----------------
    27/03/2008 22/04/2008 1 a
    10/05/2008 01/06/2008 2 L
    23/05/2008 05/06/2008 3 L
    10/04/2008 03/05/2008 1 b

    Thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand your requirements, even if I saw your examples.
    For example:
    1) Code in your result include 'L'. But, it was not explained in your description of the conditions.
    (Yours include a description of 'c'.)
    2) I thought that period 2 satisfied condition 2). But, your example showed code 'L'.
    3) I thought that period 3 satisfied condition 1). But, your example showed code 'L'.
    4) Condition 3) for code 'c' was inconsistent with the description of code 'c' in your original post.

    Here is my understandings about relationships of periods:
    Code:
    Month |3(March)     4(April)                       5(May)                          6(June)                        7(Jury)
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
    Date  |2         33 0        1         2         3 0        1         2         33 0        1         2         3 0        1
          |012345678901 123456789012345678901234567890 1234567890123456789012345678901 123456789012345678901234567890 1234567890
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
       1  |       3/27--------first----------4/22   code=a
       1a |      3/26-----------a------------4/22
       1b |                     4/09-----------b------------5/06
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
       2  |                                                     5/10------first--------6/01   code=L (b?)
       2a |                                   4/23------------a-----------5/20
       2b |                                                  5/07------------b-----------6/03
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
       3  |                                                                  5/23--first---6/05   code=L (a?)
       3a |                                                                5/21------------a-----------6/17
       3b |                                                                               6/04------------b-----------7/01
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
       1  |                      4/10-------first--------5/03   code=b
       1a |      3/26-----------a------------4/22
       1b |                     4/09-----------b------------5/06
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
    Month |3(March)     4(April)                       5(May)                          6(June)                        7(Jury)
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
    Date  |2         33 0        1         2         3 0        1         2         33 0        1         2         3 0        1
          |012345678901 123456789012345678901234567890 1234567890123456789012345678901 123456789012345678901234567890 1234567890
    ------+------------+------------------------------+-------------------------------+------------------------------+----------
    I thought that the query would be like:
    Code:
    SELECT ft.*
         , CASE
           WHEN ft.start_date  >= sta.start_date
            AND ft.end_date    <= sta.end_date   THEN
                'a'
           WHEN .....                            THEN
                'b'
           WHEN .....                            THEN
                'c'
           END  AS code
      FROM first_table  ft
      JOIN second_table sta
       ON  sta.period = ft.period
       AND sta.code   = 'a'
      JOIN second_table stb
       ON  stb.period = ft.period
       AND stb.code   = 'b'
    ;
    Last edited by tonkuma; 10-30-09 at 09:57.

Posting Permissions

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