13 Replies Latest reply on Oct 3, 2013 7:11 AM by Chandru_80

    Logic Doubt !!

    Chandru_80

      Hi Guys,

      I am having bit trouble for getting output, I have tried all possiblities way like ranking, rownum etc. Kindly what logic need to used.

       

      Create table c_temp (l_cust_code varchar2(5), l_bill date,l_invoice varchar2(30));

       

      Insert into c_temp values ('xx','06-sep-2013','SA3IN0000004');

      Insert into c_temp values ('xx','06-sep-2013','SA3IN0000006');

      Insert into c_temp values ('xx','06-sep-2013','SA3IN0000011');

      Insert into c_temp values ('xx','07-sep-2013','SA3IN0000048');

      Insert into c_temp values ('xx','10-sep-2013','SA3IN0000504');

      Insert into c_temp values ('yy','06-sep-2013','SS4IN0000104');

       

      Select * from c_temp order by 1,2 ,3;

       

      Table Output:

       

      l_cust_codel_cust_codel_bill 
      xxSA3IN000000406-sep-2013
      xxSA3IN000000606-sep-2013
      xxSA3IN000001106-sep-2013
      xxSA3IN000004807-sep-2013
      xxSA3IN000050410-sep-2013
      yySS4IN0000109  06-sep-2013

       

      Need Output:

       

      Cust_

      code

      Total invTotal Inv Same dayFirst_inv_of same_date(A)Tot Inv Same day except First(A)Inv Same DayTot Inv Other Same dayInv Other Same day
      xx53SA3IN00000042SA3IN0000006,SA3IN00000112SA3IN0000048,SA3IN0000504
      xy11SS4IN00001090

       

      Thanks in Advance!!

       

      Regards

      Chandru.B

        • 1. Re: Logic Doubt, need to what query will work out?
          Hoek

          Why are you using strings instead of dates in your insert statements for l_bill?

          And also post the result of: select * from v$version;

          You should always do this whenever posing a question,

           

          Message was edited by: Hoek

          • 2. Re: Logic Doubt !!
            Cherif bh

            Hi ,

             

            You can use analytic functions :

             

            listagg function in 11g release 2

            Analytic functions by Example | Oracle FAQ

             

            Thanks,

            Cherif

            1 person found this helpful
            • 3. Re: Logic Doubt !!
              Hoek

              We don't know if OP can use LISTAGG, since it's only available from db versions 11.2 and onward...

              • 4. Re: Logic Doubt !!
                Cherif bh

                Hi Hoek, Yes, you are right.

                • 5. Re: Logic Doubt !!
                  BluShadow

                  But as he hasn't told us... he can have an answer that does use it...

                   

                  (bearing in mind we haven't been told the exact logic, so this is just a rough idea...)

                   

                   

                  SQL> ed
                  Wrote file afiedt.buf

                    1  select l_cust_code
                    2        --,l_bill
                    3        --,l_invoice
                    4        ,max(decode(l_bill,same_day,total_inv,null)) as total_inv
                    5        ,max(decode(l_bill,same_day,total_day,null)) as total_day
                    6        ,max(decode(l_bill,same_day,first_inv_same_day,null)) as first_inv_same_day
                    7        ,max(decode(l_bill,same_day,total_day-1,null)) as total_day_except_first
                    8        ,listagg(decode(rn,1,null,l_invoice),',') within group (order by rn) as inv_same_day
                    9        ,max(decode(l_bill,same_day,total_inv-total_day,null)) as total_other_same_day
                  10        ,listagg(decode(rn,1,decode(l_bill,same_day,null,l_invoice),null),',') within group (order by l_invoice) as inv_other_same_day
                  11  from (
                  12        select l_cust_code, l_bill, l_invoice
                  13              ,count(*) over (partition by l_cust_code) as total_inv
                  14              ,count(*) over (partition by l_cust_code, l_bill) as total_day
                  15              ,row_number() over (partition by l_cust_code, l_bill order by l_invoice) as rn
                  16              ,min(l_bill) over (partition by l_cust_code) as same_day
                  17              ,min(l_invoice) over (partition by l_cust_code order by l_bill, l_invoice) as first_inv_same_day
                  18        from c_temp
                  19       )
                  20* group by l_cust_code
                  SQL> /

                   

                  L_CUS  TOTAL_INV  TOTAL_DAY FIRST_INV_SAME_ TOTAL_DAY_EXCEPT_FIRST INV_SAME_DAY                   TOTAL_OTHER_SAME_DAY INV_OTHER_SAME_DAY
                  ----- ---------- ---------- --------------- ---------------------- ------------------------------ -------------------- -------------------------
                  xx             5          3 SA3IN0000004                         2 SA3IN0000006,SA3IN0000011                         2 SA3IN0000048,SA3IN0000504
                  yy             1          1 SS4IN0000104                         0                                                   0

                  • 6. Re: Logic Doubt !!
                    Frank Kulash

                    Hi, Chandru,

                     

                    Here's one way:

                     

                    WITH got_analytics  AS

                    (

                        SELECT  l_cust_code, l_bill, l_invoice

                        ,       DENSE_RANK () OVER ( PARTITION BY  l_cust_code

                                                     ORDER BY      l_bill

                                                   )    AS day_num

                        ,       ROW_NUMBER () OVER ( PARTITION BY  l_cust_code

                                                     ,             l_bill

                                                     ORDER BY      l_invoice

                                                   )    AS invoice_num

                        FROM    c_temp

                    )

                    SELECT    l_cust_code

                    ,         COUNT (*)                 AS total_inv

                    ,         COUNT ( CASE

                                          WHEN  day_num = 1

                                          THEN  1

                                      END

                                    )                   AS total_inv_same_day

                    ,         MIN ( CASE

                                        WHEN  day_num = 1

                                        AND   invoice_num = 1

                                        THEN  l_invoice

                                    END

                                  )                     AS first_inv_of_same_date_a

                    ,         COUNT ( CASE

                                          WHEN  day_num = 1

                                          THEN  1

                                      END

                                    ) - 1               AS total_inv_same_day_except_1st

                    ,         LISTAGG ( CASE

                                            WHEN  day_num = 1

                                            AND   invoice_num > 1

                                            THEN  l_invoice

                                        END

                                      , ','

                                      ) WITHIN GROUP (ORDER BY  l_invoice)

                                                        AS inv_same_day

                    ,         COUNT ( CASE

                                          WHEN  day_num > 1

                                          THEN  1

                                      END

                                    )                   AS total_inv_other_same_day

                    ,         LISTAGG ( CASE

                                            WHEN  day_num > 1

                                            THEN  l_invoice

                                        END

                                      , ','

                                      ) WITHIN GROUP (ORDER BY  l_invoice)

                                                        AS inv_other_same_day

                    FROM      got_analytics

                    GROUP BY  l_cust_code

                    ORDER BY  l_cust_code

                    ;

                    Thanks for posting the CREATE TABLE and INSERT statments; that's very helpful.

                    As others have said, don't forget to post your Oracle version (e.g. 11.2.0.3.0).

                    1 person found this helpful
                    • 7. Re: Logic Doubt !!
                      Mike Kutz

                      Your specifications are unclear.

                      'total inv' is easy:  count(*) over (partition by cust_code)

                      'total inv same day' -- you have 3 different days listed.  which day do we use?

                      'first inv of same day' -- "first" means you need to specify an order.  ORDER BY what?

                      'other same day' -- this shows 2 different invoices (for same cust_code) but they are not on 'the same day'.  please clarify why

                      The math for the rest seems to follow suit.

                      • 8. Re: Logic Doubt !!
                        BluShadow

                        From what I could gather "same day" means the earliest date for the cust_code.  So when it says "other same day" it means other than the same day i.e. all the other days.

                        At least that's what it looks like. 

                        • 9. Re: Logic Doubt !!
                          Chandru_80

                          Hi,

                            Your logic is perfect. Thanks a ton. Currently using oracle 10g other than listagg, what function need to be used.

                           

                          Regards,

                          Chandru.B

                          • 10. Re: Logic Doubt !!
                            Chandru_80

                            Hi Mike,

                            As you said

                            "Your specifications are unclear.

                            'total inv' is easy:  count(*) over (partition by cust_code)

                            'total inv same day' -- you have 3 different days listed.  which day do we use?

                            'first inv of same day' -- "first" means you need to specify an order.  ORDER BY what?

                            'other same day' -- this shows 2 different invoices (for same cust_code) but they are not on 'the same day'.  please clarify why"

                            total inv same day: means in cust code xx on 6th Sep 2013 having 3 invoice, so I need first bill of same day (coz other 2 invoice not be included and eventually not required 07th and 10th Sep).

                            'first inv of same day' : ascending order to be used.

                            'other same day' : Need other first bill of the same day, so output required as :SA3IN0000006 and SA3IN0000011

                             

                            I hope you got clear.

                             

                            Regards,

                            Chandru.B

                            • 11. Re: Logic Doubt !!
                              Chandru_80

                              Hi,

                                Your query is absolutely correct. Currently using oracle 10g other than listagg, what function need to be used.

                               

                              Regards,

                              Chandru.B

                              • 13. Re: Logic Doubt !!
                                Chandru_80

                                Hi,

                                Thanks a ton!!

                                 

                                Regards,

                                Chandru.B