Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2013
    Posts
    11

    Unanswered: Duplicates in join

    I am trying to calculate the time difference in 2 set of values; below is the original data.
    id date/time
    ----------------------------
    1004 2014-04-07 09:01:28.0
    1004 2014-04-07 09:01:48.0
    1004 2014-04-07 14:03:08.0
    1004 2014-04-07 14:07:08.0
    1004 2014-04-08 04:31:32.0
    1004 2014-04-08 04:31:52.0

    i want this data to be displayed as below so i can calculate the time difference
    id date/time for A date/time for B
    ----------------------------------------------------
    1004 2014-04-07 09:01:28.0 2014-04-07 09:01:48.0
    1004 2014-04-07 14:03:08.0 2014-04-07 14:07:08.0
    1004 2014-04-08 04:31:32.0 2014-04-08 04:31:52.0

    instead my query returns the below;its joining date/time for A 3 times for 3 entries for date/time in B.so the query displays 9 combinations
    id date/time for A date/time for B
    ---------------------------------------------------
    1004 2014-04-07 09:01:28.0 2014-04-07 09:01:48.0
    1004 2014-04-07 09:01:28.0 2014-04-07 14:07:08.0
    1004 2014-04-07 09:01:28.0 2014-04-08 04:31:52.0
    1004 2014-04-07 14:03:08.0 2014-04-07 09:01:48.0
    1004 2014-04-07 14:03:08.0 2014-04-07 14:07:08.0
    1004 2014-04-07 14:03:08.0 2014-04-08 04:31:52.0
    1004 2014-04-08 04:31:32.0 2014-04-07 09:01:48.0
    1004 2014-04-08 04:31:32.0 2014-04-07 14:07:08.0
    1004 2014-04-08 04:31:32.0 2014-04-08 04:31:52.0

    any help/suggestions is highly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    How do you know which one is A and which is B?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's at least one important piece missing from your question. For the set:
    Code:
     1004 2014-04-07 00:15:01.0
     1004 2014-04-07 00:18:01.0
     1004 2014-04-07 00:21:01.0
     1004 2014-04-08 00:00:01.0
     1004 2014-04-08 00:03:01.0
    What result would you expect to see?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2013
    Posts
    11
    This is the query im using;the issue arises when i join them.

    Select id1,date/time for A,date/time for B from
    (
    (
    select m_id as id1,time as date/time for A ,code_id from table A
    where code_id=5
    and time between '2014-04-07 07:00:00' and '2014-04-08 07:00:00'
    and id=1004
    )
    left join
    (
    select m_id as id2,time as date/time for B ,code_id from table B
    where code_id=6
    and time between '2014-04-07 07:00:00' and '2014-04-08 07:00:00'
    and id=1004
    )as first
    on id1=id2
    )group by id1, date/time for A,date/time for B

  5. #5
    Join Date
    Oct 2013
    Posts
    11
    the result should be this;
    id date/time for a date/time for b
    ----------------------------------------------------
    1004 2014-04-07 09:01:28.0 2014-04-07 09:01:48.0
    1004 2014-04-07 14:03:08.0 2014-04-07 14:07:08.0
    1004 2014-04-08 04:31:32.0 2014-04-08 04:31:52.0

    but instead of 3 rows i get 9 rows.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by spetritia View Post
    This is the query im using;the issue arises when i join them.

    Select id1,date/time for A,date/time for B from
    (
    (
    select m_id as id1,time as date/time for A ,code_id from table A
    where code_id=5
    and time between '2014-04-07 07:00:00' and '2014-04-08 07:00:00'
    and id=1004
    )
    left join
    (
    select m_id as id2,time as date/time for B ,code_id from table B
    where code_id=6
    and time between '2014-04-07 07:00:00' and '2014-04-08 07:00:00'
    and id=1004
    )as first
    on id1=id2
    )group by id1, date/time for A,date/time for B
    Looking into your code,
    answer to n_i's question
    How do you know which one is A and which is B?
    might be code_id=5 for A and code_id=6 for B.

    Note: If my guess was right, you should include code_id in your sample data.
    I am trying to calculate the time difference in 2 set of values; below is the original data.
    id date/time
    ----------------------------
    1004 2014-04-07 09:01:28.0
    1004 2014-04-07 09:01:48.0
    1004 2014-04-07 14:03:08.0
    1004 2014-04-07 14:07:08.0
    1004 2014-04-08 04:31:32.0
    1004 2014-04-08 04:31:52.0
    like...
    Code:
    id   date_time             code_id
    ---- --------------------- -------
    1004 2014-04-07 09:01:28.0 5
    1004 2014-04-07 09:01:48.0 6
    1004 2014-04-07 14:03:08.0 5
    1004 2014-04-07 14:07:08.0 6
    1004 2014-04-08 04:31:32.0 5
    1004 2014-04-08 04:31:52.0 6

    Anyway,
    your join condition was "on id1=id2", and all id1/id2 were 1004(same value).
    So, combination of (any row of A and any row of B) satisfy the join condition. Then all combination of A and B were in your results.

    You should add some more conditions by which a row of A joied by a row of B.
    Last edited by tonkuma; 04-09-14 at 20:15.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You should add some more conditions by which a row of A joied by a row of B.
    In other words,
    why 2014-04-07 09:01:28.0 joined only 2014-04-07 09:01:48.0(and not 2014-04-07 14:07:08.0 nor 2014-04-08 04:31:52.0)?
    why 2014-04-07 14:03:08.0 joined only 2014-04-07 14:07:08.0(and not 2014-04-07 09:01:28.0 nor 2014-04-08 04:31:52.0)?
    so on...

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    with a (id, dt) as (values
      (1004, timestamp('2014-04-07 09:01:28'))
    , (1004, timestamp('2014-04-07 09:01:48'))
    , (1004, timestamp('2014-04-07 14:03:08'))
    , (1004, timestamp('2014-04-07 14:07:08'))
    , (1004, timestamp('2014-04-08 04:31:32'))
    , (1004, timestamp('2014-04-08 04:31:52'))
    )
    , b (id, dt, grp, num_in_grp) as (
    select 
      id, dt
    , (rownumber() over (partition by id order by dt) - 1)/2
    , rownumber() over (partition by id, (rownumber() over (partition by id order by dt) - 1)/2 order by dt)
    from a
    )
    select b1.id, b1.dt as dt_a, coalesce(b2.dt, b1.dt) as dt_b
    from b b1
    left join b b2 on b2.id=b1.id and b2.grp=b1.grp and b2.num_in_grp=2
    where b1.num_in_grp=1
    Regards,
    Mark.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by mark.b View Post
    Hi,

    try this:
    Code:
    with a (id, dt) as (values
      (1004, timestamp('2014-04-07 09:01:28'))
    , (1004, timestamp('2014-04-07 09:01:48'))
    , (1004, timestamp('2014-04-07 14:03:08'))
    , (1004, timestamp('2014-04-07 14:07:08'))
    , (1004, timestamp('2014-04-08 04:31:32'))
    , (1004, timestamp('2014-04-08 04:31:52'))
    )
    , b (id, dt, grp, num_in_grp) as (
    select 
      id, dt
    , (rownumber() over (partition by id order by dt) - 1)/2
    , rownumber() over (partition by id, (rownumber() over (partition by id order by dt) - 1)/2 order by dt)
    from a
    )
    select b1.id, b1.dt as dt_a, coalesce(b2.dt, b1.dt) as dt_b
    from b b1
    left join b b2 on b2.id=b1.id and b2.grp=b1.grp and b2.num_in_grp=2
    where b1.num_in_grp=1
    The query might be equivalent to
    Code:
    ------------------------------ Commands Entered ------------------------------
    with a (id, dt) as (values
      (1004, timestamp('2014-04-07 09:01:28'))
    , (1004, timestamp('2014-04-07 09:01:48'))
    , (1004, timestamp('2014-04-07 14:03:08'))
    , (1004, timestamp('2014-04-07 14:07:08'))
    , (1004, timestamp('2014-04-08 04:31:32'))
    , (1004, timestamp('2014-04-08 04:31:52'))
    )
    , b (id , dt , rn) AS (
    SELECT id , dt
         , ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt) AS rn
     FROM  a
    )
    SELECT b1.id
         , b1.dt AS dt_a
         , b2.dt AS dt_b
     FROM  b AS b1
     LEFT  OUTER JOIN
           b AS b2
      ON   b2.id = b1.id
      AND  b2.rn = b1.rn + 1
     WHERE MOD(b1.rn , 2) = 1
    ;
    ------------------------------------------------------------------------------
    
    ID          DT_A                       DT_B                      
    ----------- -------------------------- --------------------------
           1004 2014-04-07-09.01.28.000000 2014-04-07-09.01.48.000000
           1004 2014-04-07-14.03.08.000000 2014-04-07-14.07.08.000000
           1004 2014-04-08-04.31.32.000000 2014-04-08-04.31.52.000000
    
      3 record(s) selected.

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    hi, spetritia
    if just combine two rows ,we can use the min,max function to avoid join:
    Code:
    with a (id, dt) as (values
      (1004, timestamp('2014-04-07 09:01:28'))
    , (1004, timestamp('2014-04-07 09:01:48'))
    , (1004, timestamp('2014-04-07 14:03:08'))
    , (1004, timestamp('2014-04-07 14:07:08'))
    , (1004, timestamp('2014-04-08 04:31:32'))
    , (1004, timestamp('2014-04-08 04:31:52'))
    )
    select b.id,min(b.dt),max(b.dt)
    from (
    select 
      a.*
      ,(rownumber() over (partition by id order by dt) - 1)/2 as rn
        from a ) b
    group by b.id,b.rn

  11. #11
    Join Date
    Oct 2013
    Posts
    11
    I used "rownumber() over (order by id) as rn" in my select and it gives the desired result,except that there are some exceptions in the data.I figured some id's have just code id 5 or 6,some start with a 6,so i am trying to eliminate the 6.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I figured some id's have just code id 5 or 6,some start with a 6,so i am trying to eliminate the 6.
    The statement was not clear for me.

    I want you to give some concrete examples, like I wrote in here
    Quote Originally Posted by tonkuma View Post
    Looking into your code,
    answer to n_i's question

    might be code_id=5 for A and code_id=6 for B.

    Note: If my guess was right, you should include code_id in your sample data.

    like...
    Code:
    id   date_time             code_id
    ---- --------------------- -------
    1004 2014-04-07 09:01:28.0 5
    1004 2014-04-07 09:01:48.0 6
    1004 2014-04-07 14:03:08.0 5
    1004 2014-04-07 14:07:08.0 6
    1004 2014-04-08 04:31:32.0 5
    1004 2014-04-08 04:31:52.0 6
    ...
    Please publish some sample data(which include code_id values) and the expeced results from the data.

  13. #13
    Join Date
    Oct 2013
    Posts
    11
    The exception is as below, almost all id's start with code 5 .The time difference is calculated with (code6 time-code5time).So the code6 time is always higher than code5time. But in certain id's code 6 occurrence is higher than code5 ,like the example below,results in negative values.The data has to be collected for a 24 hrs period. so the corresponding incident (code 5) for the additional code 6 ( 2503 2014-04-07 13:30:28.0 6 ) happened before the 24 hrs in my query. The incident before the 24 hrs is not required. i hope this makes sense.

    id date/time code
    --------------------------------------
    2503 2014-04-07 13:32:28.0 5
    2503 2014-04-07 13:36:48.0 5
    2503 2014-04-07 17:03:29.0 5
    2503 2014-04-07 17:08:09.0 5
    2503 2014-04-07 18:00:49.0 5
    2503 2014-04-07 22:17:11.0 5



    2503 2014-04-07 13:30:28.0 6
    2503 2014-04-07 13:33:08.0 6
    2503 2014-04-07 13:44:31.0 6
    2503 2014-04-07 17:07:29.0 6
    2503 2014-04-07 17:09:09.0 6
    2503 2014-04-07 18:36:49.0 6
    2503 2014-04-07 22:22:31.0 6

Posting Permissions

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