Problem regarding DB2 query

lifzgud · #1 (**permalink**) 10-28-09, 04:30

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

n_i · #2 (**permalink**) 10-28-09, 07:09

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.

lifzgud · #3 (**permalink**) 10-29-09, 23:52

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

tonkuma · #4 (**permalink**) 10-30-09, 08:39

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'
;

Internet Services

Web Development

Digital Marketing

Consumer Tech