Results 1 to 5 of 5

Thread: Help wit query

  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Unanswered: Help wit query

    Hello,

    I struggle to join the two tables below.

    T1
    TaskID Task_create_date Contact_ID
    XX XX XX

    and

    T2
    Contact_ID IssueID Issue_Create_date
    XX XX XX
    XX XY XY
    XX XZ XZ

    What I need to do is to link Task to a specific Issue. There is no direct connection between Issue and Task. The only common key is the Contact_id. but one contact can have multiple Issues as you can see. The only way how to connect Task to a specific Issue is based on dates and the join on Contact_ID.

    - If task_create_date is between Issue_create_date of IssueID = XX and IssueID = XY then the TASKID = XX would be attached to IssueID = XX.
    - If between IssueID_create_date XY and XZ, then to IssueID XY.
    - If task_create_date is higher then Issue_create_date XZ then it would be attached to IssueID XZ.

    Any ideas how the query should look like?

    Thanks a lot
    Last edited by Ivanuska132; 05-24-13 at 02:59.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What datatypes for each column of T1 and T2?

    T1
    TaskID Task_create_date Contact_ID

    T2
    Contact_ID IssueID Issue_Create_date

    - If task_create_date is between Issue_create_date of IssueID = XX and IssueID = XY then the TASKID = XX would be attached to SetMetID = XX.
    - If between SetMet_create_date XY and XZ, then to SetmetId XY.
    - If task_create_date is higher then Issue_create_date XZ then it would be attached to SetMetID XZ.
    I didn't see SetMetID nor SetMet_create_date in your tables.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If task_create_date is lower than Issue_create_date of IssueID = XX,
    what result do you want?

  4. #4
    Join Date
    Oct 2011
    Posts
    6
    sorry tonkuma. SetmetID = IssueID, SetMet_create_date = Issue_Create_date (I`ve mixed up the terminology). I updated the original post so it makes more sense now.

    IssueID varchar(10)
    TaskID varchar(10)
    Contact_ID varchar(10)
    Task_create_date timestamp
    Issue_Create_date timestamp

    If task_create_date is lower than Issue_create_date of IssueID - this should not happen so something like 'Error' should be displayed as IssueID.

    thanks

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like...

    Example 1:
    Code:
    SELECT t1.TaskID , t1.Task_create_date
         , t1.Contact_ID
         , COALESCE(t2.IssueID , 'Error') AS IssueID
         , t2.Issue_Create_date
     FROM  t1
     LEFT  OUTER JOIN
           (SELECT t2.*
                 , LEAD( Issue_Create_date , 1 , TIMESTAMP('9999-12-31-23.59.59') )
                      OVER( PARTITION BY Contact_ID
                                ORDER BY Issue_Create_date
                          ) AS lead_Issue_Create_date
             FROM  t2
           ) AS t2
      ON   t2.Contact_ID = t1.Contact_ID
       AND t1.Task_create_date
           BETWEEN t2.Issue_Create_date
               AND t2.lead_Issue_Create_date
    ;

Posting Permissions

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