4 Replies Latest reply on Feb 8, 2013 12:00 PM by Frank Kulash

    Mixing Max and Min

      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.

      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
        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.

      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
        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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Mixing Max and Min
            Frank Kulash

            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
                            )          AS delivery_time
                 ,       MIN ( CASE 
                                     WHEN  keyname = 'str_TimestampStop'
                               THEN  value
                            )          AS delivery_stop
                 FROM      i_data_4
                 GROUP BY  rid
                 HAVING       COUNT ( CASE
                                   WHEN  keyname = 'str_A_ID'
                                 THEN  1
                           )     > 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.,
            See the forum FAQ {message:id=9360002}
            • 3. Re: Mixing Max and Min
              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
              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

                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.