This discussion is archived
4 Replies Latest reply: Feb 8, 2013 4:00 AM by Frank Kulash RSS

Mixing Max and Min

976439 Newbie
Currently Being Moderated
Hi, i am struggling with code that I am trying to put together, but seem to be scratching my head, rather than coming up with something.

The code1 shows the rid, cid, call started, queue, call stopped and identity1 and identity2 as I require. Identity will mostly be null values but if any jobs were picked up by 2 people than this will identify that for me.

code1
select rid, cid, call_started, queue, call_stopped, 
       max(case when ord=1 then identity end) as identity1,
       max(case when ord=2 then identity end) as identity2
from
(
  select i3.rid, i3.cid, i3.call_started, i3.queue, i3.call_stopped, i4.value as identity,
         dense_rank() over(partition by i3.rid order by to_date(i4.created, 'DD/MM/YYYY HH24:MI:SS')) as ord
    from i_data_3 i3
      left join i_data_4 i4 
        on i3.rid=i4.rid
       and i4.keyname='str_A_ID'
  order by i3.rid
) r
group by rid, cid, call_started, queue, call_stopped;
What I am looking to do now, is within the i4 table i have other keynames that give me information such as the delivery time and delivery stop for a job and I need to somehow add these within the first query. I have managed to write a separate query (shown below) to give me this but now i dont no how i can combine this 2 queries to give me what i require.

code2
select i3.rid, i3.cid, i3.queue,
  min(case when keyname='str_TimestampDelivery' then value end) as Delivery_Time,
  i3.call_started, i3.call_stopped,
  min(case when keyname='str_TimestampStop' then value end) as Delivery_Stop,
  min (case when keyname='str_A_ID' then value end) as A_ID
from 
  i_data_3 i3 
  left join i_data_4 i4
  on i3.rid=i4.rid
  group by i3.rid, i3.cid, i3.queue, i3.call_started, i3.call_stopped;
So the query i am trying to write should show me:

rid, cid, delivery_time, call started, queue, call stopped, delivery_stop, identity1 and identity2.

Any help or advise would be really appreciated. If somebody could please help.

Thanks in advance.

Edited by: 973436 on 08-Feb-2013 01:14
  • 1. Re: Mixing Max and Min
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Mixing Max and Min
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Depending on your data and your requirements, you can create a result set like this:
    WITH     i_data_4_agg     AS
    (
         SELECT       rid     
         ,       MIN ( CASE 
                             WHEN  keyname = 'str_TimestampDelivery'
                       THEN  value
                   END
                    )          AS delivery_time
         ,       MIN ( CASE 
                             WHEN  keyname = 'str_TimestampStop'
                       THEN  value
                   END
                    )          AS delivery_stop
         FROM      i_data_4
         GROUP BY  rid
         HAVING       COUNT ( CASE
                           WHEN  keyname = 'str_A_ID'
                         THEN  1
                     END
                   )     > 0
    )
    Instead of joining to the actual i_data_4_agg table, join to this result set, and SELECT the delivery_time and delivery_stop columns.


     

    I hope this answers your question.
    If not, then, as the first reply suggested, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 3. Re: Mixing Max and Min
    976439 Newbie
    Currently Being Moderated
    sb92075 and Frank Kulash thank you very much for your replies. Apologies for the errors made. I have managed to outline the table information for i3 and i4, which are the 2 tables where the info lies.

    Table I3
    I3
    COLUMN NAME    DATA TYPE
    ------------- ----------
    RID              VARCHAR
    CID              VARCHAR
    CALL_STARTED         DATE
    QUEUE                  VARCHAR
    CALL_STOPPED          DATE
    the following is part of the data that is within table i3
    RID         CID          CALL_STARTED          QUEUE          CALL_STOPPED
    ------     ------      ------------------     ----------     ------------------
    134I3S     173391     01-DEC-12 00:05:43     QUEUE A          01-DEC-12 00:07:06
    134I42     173391     01-DEC-12 00:10:10     QUEUE B          01-DEC-12 00:13:43
    The next table is the i4 table and this is where the delivery and agent info lies but this is within the 1 column called values which is linked to to the keyname.
    COLUMN NAME    DATA TYPE
    ------------- ----------
    RID              VARCHAR
    CID              VARCHAR
    CREATED                   DATE
    KEYNAME                  VARCHAR
    VALUE                VARCHAR
    The data within this table is the following:
    RID         CID          CREATED                    KEYNAME                         VALUE
    ------     ------      ------------------     ---------------------     -------------------
    134I3S     173391     01-DEC-12 00:01:00     str_A_ID                    603          
    134I3S     173391     01-DEC-12 00:01:00     str_TimestampDelivery     01/12/2012 00:04:30     
    134I3S     173391     01-DEC-12 00:07:00     str_TimestampStop          01/12/2012 00:08:49     
    134I3S     119911     01-DEC-12 00:07:00     str_TimestampStop          01/12/2012 00:08:49     
    134I42     173391     01-DEC-12 00:08:00     str_AgentID                    700          
    134I42     173391     01-DEC-12 00:09:00     str_TimestampDelivery     01/12/2012 00:09:30     
    134I42     119911     01-DEC-12 00:14:00     str_TimestampStop          01/12/2012 00:15:10     
    134I42     173391     01-DEC-12 00:14:00     str_TimestampStop          01/12/2012 00:15:10
    The outcome I am looking from these two tables is the following:
    RID         CID          DELIVERY_TIME           CALL_STARTED           QUEUE           CALL_STOPPED           DELIVERY_STOP           IDENTITY1  IDENTITY2
    ------     ------      ------------------      ------------------      ----------      -----------------  -------------------  ---------  ---------      
    134I3S     173391     01/12/2012 00:04:30      01-DEC-12 00:05:43      QUEUE A     01-DEC-12 00:07:06     01/12/2012 00:08:49      603          NULL
    The queries stated in my original post allows me to get this information based on 2 queries but I would like to combine the 2 to give me just the 1 query with the info I required.

    Also for example if i was to have 2 identities shown for 1 item, than I would only like to pick up the information for the first id that picked up the job, but a value in identity2 would tell me it was handled by another agent which is enough info for what i require.

    Just wanted to say thanks again for taking your time to help me out, really appreciated.
  • 4. Re: Mixing Max and Min
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Sorry, without tables that I can actually use, I can't do much more than what I posted yesterday. Please post CREATE TABLE and INSERT statements for your sample data, and your best attempt using a sub-query like the one I posted earlier.

    I still think you should pivot table i4, as I showed yesterday, then join that result set to i3. The code I posted yesterday said <tt> GROUP BY rid </tt> ; that should be changed to include cid as well: <tt> GROUP BY rid, cid </tt> . Add a pivoted column for identity1. I don't know what identity2 is; maybe you need a pivoted column for that, too.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points