1 2 3 Previous Next 34 Replies Latest reply on Apr 25, 2019 5:54 PM by CarlosDLG

    Choose latest record with conditional scenerio

    bhaskar_subbiah

      Hi All,

       

      Below is the scenario i am facing. I tried to explain it with sample data.

       

         

      IDBilling_typeCREATED_DATE
      1128New order01-Jan-98
      1128In Billing01-Jan-01
      2115In billing25-Feb-10
      2115In billing27-Mar-15
      2141New order01-Mar-18
      2141Cancelled04-Feb-19
      1786New order01-Jan-98
      1786In Billing01-Jan-01
      7654New order01-Mar-18
      7654Cancelled04-Feb-19
      7654Cancelled

      09-Mar-19

       

      The scenerio is, a single ID appears multiple time but i have to choose latest record based on max(created_date) column. But if a ID has billing_type as 'Cancelled' then we have to pick previous line item for that ID value. For ex, here for ID 2141 the latest one has billing type as cancelled, so in this case i need to select the previous one with billing type as 'New order' .

       

      Suppose if the previous one is also 'Cancelled' ( for ID 7654) then i have to select the one before that with billing type other than 'Cancelled' .

       

      Below is how the output should appear.

       

         

      IDBilling_typeCREATED_DATE
      1128In Billing01-Jan-01
      2115In billing27-Mar-15
      2141New order01-Mar-18
      1786In Billing01-Jan-01
      7654New order

      01-Mar-18

       

      I googled for the logic but i did not get a proper one. I think it should be something like case - When statement but i am unable to frame a right logic.

      So please help me with this.

       

      Let me know for any queries.

       

      Thanks

      Bhaskar.S

        • 1. Re: Choose latest record with conditional scenerio
          CarlosDLG

          So, you want the last non-cancelled row.  Is that right?

           

          Since you want only one row per id, you need some kind of aggregation.

           

          Here is an idea, which I could not test because you didn't provide the necessary scripts to create your table and have your data:

           

          SELECT

              id

              , max(billing_type) keep (dense_rank last order by created_date) AS billing_type

              , max(created_date) as created_date

          FROM your_table

          WHERE billing_type != 'Cancelled'

          GROUP BY id;

           

          This assumes that there can only be one row for a given id and created_date, and that for every id there is always going to be at least one non-cancelled row.

          • 2. Re: Choose latest record with conditional scenerio
            bhaskar_subbiah

            Thanks Carlos for your response, but the problem is, in your query you have given where condition as != 'Cancelled',

            This will completely exclude the id's having Cancelled as billing type. But it should not happen in that way, it should not exclude that record.

             

            Hope you got my concern. Please give me some alternate solution.

             

            Thanks

            Bhaskar.S

            • 3. Re: Choose latest record with conditional scenerio
              CarlosDLG

              bhaskar_subbiah wrote:

               

              Thanks Carlos for your response, but the problem is, in your query you have given where condition as != 'Cancelled',

              This will completely exclude the id's having Cancelled as billing type. But it should not happen in that way, it should not exclude that record.

               

              Hope you got my concern. Please give me some alternate solution.

               

              Thanks

              Bhaskar.S

              Why?

               

              You said that if the latest row was cancelled you need to keep going backwards until you find a non-cancelled row for that id, so, in which cases do you need a cancelled row to be returned?

              • 4. Re: Choose latest record with conditional scenerio
                mathguy

                CarlosDLG wrote:

                 

                ............

                 

                This assumes that there can only be one row for a given id and created_date, and that for every id there is always going to be at least one non-cancelled row.

                 

                In fact, the solution doesn't assume either of those conditions (it doesn't need them to run without error and to produce possibly correct output). If an ID only has canceled bills, that ID will simply not appear in the output at all. And if there are two or more rows with billing type != 'Canceled' and with the same latest date for an ID, then one of those tied rows will be shown in the output. It is entirely possible that this handling of the two exceptional situations is acceptable to the OP and his organization.

                • 5. Re: Choose latest record with conditional scenerio
                  CarlosDLG

                  mathguy wrote:

                   

                  CarlosDLG wrote:

                   

                  ............

                   

                  This assumes that there can only be one row for a given id and created_date, and that for every id there is always going to be at least one non-cancelled row.

                   

                  In fact, the solution doesn't assume either of those conditions (it doesn't need them to run without error and to produce possibly correct output). If an ID only has canceled bills, that ID will simply not appear in the output at all. And if there are two or more rows with billing type != 'Canceled' and with the same latest date for an ID, then one of those tied rows will be shown in the output. It is entirely possible that this handling of the two exceptional situations is acceptable to the OP and his organization.

                  The key would be the "possibly" or "possible" words you used in your explanation.

                   

                  The first assumption is probably not necessary, as you suggested, if it is acceptable to not include all IDs in the results.

                   

                  As for the second one, the results wouldn't be deterministic without it, and I usually don't like that.

                  • 6. Re: Choose latest record with conditional scenerio
                    mathguy

                    The results ARE deterministic when there are ties. They may not be what the OP wants/needs, but that doesn't make them non-deterministic. From among all the rows tied for most recent, the MAX value of BILLING_TYPE will be selected.

                     

                    This is different from using ROW_NUMBER() with a partial ordering criterion and selecting the row with RN = 1. That is indeed non-deterministic. To make it deterministic (in this problem, where no OTHER columns are involved), you would use ROW_NUMBER with ORDER BY CREATED_DATE DESC, BILLING_TYPE DESC  -  then the result is equivalent to your aggregate query. (But the aggregate query will get the same answer faster.)

                    • 7. Re: Choose latest record with conditional scenerio
                      Frank Kulash

                      Hi, Bhaskar,

                      bhaskar_subbiah wrote:

                       

                      Thanks Carlos for your response, but the problem is, in your query you have given where condition as != 'Cancelled',

                      This will completely exclude the id's having Cancelled as billing type. But it should not happen in that way, it should not exclude that record.

                       

                      Hope you got my concern. Please give me some alternate solution.

                       

                      Thanks

                      Bhaskar.S

                      Sorry, I don't understand.  In your original message you said

                      ... But if a ID has billing_type as 'Cancelled' then we have to pick previous line item for that ID value.

                      ...

                      Suppose if the previous one is also 'Cancelled' ( for ID 7654) then i have to select the one before that with billing type other than 'Cancelled' . ...

                      That sure sounds like you want to exclude rows where billing_type='Cancelled'.

                       

                      As Carlos (and the Forum FAQ: Re: 2. How do I ask a question on the forums?  ) said, post some sample data in a form that the people who want to help you can run to re-create the problem and test their ideas.

                       

                      Point out where the query Carlos posted in reply #1 is not getting the results you want.  Show what the correct results are, and explain how you figure those are the correct results.

                      • 8. Re: Choose latest record with conditional scenerio
                        CarlosDLG

                        mathguy wrote:

                         

                        The results ARE deterministic when there are ties. They may not be what the OP wants/needs, but that doesn't make them non-deterministic. From among all the rows tied for most recent, the MAX value of BILLING_TYPE will be selected.

                         

                        This is different from using ROW_NUMBER() with a partial ordering criterion and selecting the row with RN = 1. That is indeed non-deterministic. To make it deterministic (in this problem, where no OTHER columns are involved), you would use ROW_NUMBER with ORDER BY CREATED_DATE DESC, BILLING_TYPE DESC - then the result is equivalent to your aggregate query. (But the aggregate query will get the same answer faster.)

                        Hmmm.  I see your point.  Maybe 'deterministic' wasn't the best word in this case.

                         

                        What I was trying to say is that if there are more than one row, you wouldn't always get the 'New Order', for example, because it depends on what other rows there are for the same date.  Also, selecting the max of a string doesn't make a lot of sense from the user perspective, in most cases.

                         

                        I believe It might or might not be considered deterministic, depending on what you consider the inputs to be.  You might not agree, and I'm okay with that.

                        • 9. Re: Choose latest record with conditional scenerio
                          bhaskar_subbiah

                          Ok i may be wrong in my understanding. I thought if we give the condition !='Cancelled' then all the corresponding ID's will be rejected. I mean other if Id 123 has multiple billing type with 'Cancelled' as latest one then it will reject all the records. I think thats wrong understanding.

                           

                          So the query you have provided will neglect 'Cancelled' one and check for other billing types. right? i ll try this.

                           

                          Thanks

                          Bhaskar.S

                          • 10. Re: Choose latest record with conditional scenerio
                            bhaskar_subbiah

                            Hi Frank,

                             

                            For the same query i replied to CarlosDLG.

                            • 11. Re: Choose latest record with conditional scenerio
                              bhaskar_subbiah

                              Hi Carlos,

                               

                              I m seeing data where a single id has only one line item  with billing type as 'Cancelled' . So such records are getting rejected due to !='Cancelled' condition.

                              If a id has only one record with 'cancelled' as billing type then in this case it should come to target. Only if it has multiple line items with values other than 'Cancelled'  then the other values should be selected.

                               

                              In short, for single id select values other than 'cancelled'  , if the value is only one with 'cancelled' then no other way select that..

                               

                              Hope my point is clear.

                               

                              Thanks

                              Bhaskar.S

                              • 12. Re: Choose latest record with conditional scenerio
                                Frank Kulash

                                Hi,

                                bhaskar_subbiah wrote:

                                 

                                Hi Carlos,

                                 

                                I m seeing data …

                                I'm not seeing any data.  Post some, and point out where the query Carlos posted way back in reply #1 is not producing the results you want.

                                 

                                Hope my point is clear.

                                Me too.

                                • 13. Re: Choose latest record with conditional scenerio
                                  mathguy

                                  I created a small table for testing, with a few more rows - two additional ID's, where the only billing type is 'Cancelled'. I show it below, with the desired output rows in red.

                                   

                                  select * from billing;

                                   

                                    ID BILLING_TYPE                   CREATED_DATE

                                  ---- ------------------------------ ------------

                                  1128 New order                      01-Jan-98

                                  1128 In Billing                     01-Jan-01

                                  2115 In billing                     25-Feb-10

                                  2115 In billing                     27-Mar-15

                                  2141 New order                      01-Mar-18

                                  2141 Cancelled                      04-Feb-19

                                  1786 New order                      01-Jan-98

                                  1786 In Billing                     01-Jan-01

                                  7654 New order                      01-Mar-18

                                  7654 Cancelled                      04-Feb-19

                                  7654 Cancelled                      09-Mar-19

                                  8000 Cancelled                      01-Apr-14

                                  8000 Cancelled                      22-Feb-13

                                  9999 Cancelled                      18-Nov-18

                                   

                                  If this is what you need, then the following query will do the job:

                                   

                                  select id,

                                         max(billing_type) keep (dense_rank last order by case when billing_type != 'Cancelled'

                                                                                               then created_date end

                                                                                 nulls first, created_date) as billing_type,

                                         max(created_date) keep (dense_rank last order by case when billing_type != 'Cancelled'

                                                                                               then created_date end

                                                                                 nulls first, created_date) as created_date

                                  from   billing

                                  group  by id

                                  ;

                                   

                                    ID BILLING_TYPE                   CREATED_DATE

                                  ---- ------------------------------ ------------

                                  1128 In Billing                     01-Jan-01

                                  1786 In Billing                     01-Jan-01

                                  2115 In billing                     27-Mar-15

                                  2141 New order                      01-Mar-18

                                  7654 New order                      01-Mar-18

                                  8000 Cancelled                      01-Apr-14

                                  9999 Cancelled                      18-Nov-18

                                  • 14. Re: Choose latest record with conditional scenerio
                                    bhaskar_subbiah

                                    Hi Mathguy,

                                     

                                    Your query worked as expected .But there is one new finding.. For few records, a single ID comes with two different billing_type with same createddate.

                                    In that case we need to select one which has max(order_number). For single id, even createdate can be same for two or more, but order number will not be same. The order number with max value is the latest one. This understanding i got only after trying your above query. So i modified i query and below is the one.

                                     

                                    max(billing_type) keep (dense_rank last order by case when billing_type != 'Cancelled'then order_number end nulls first, order_number) as billing_type,

                                     

                                    Along with this there are few more columns.So i added them in the select statment but its asking me to put it in group by clause. When i put in group by i am getting incorrect data.

                                     

                                    select id,

                                    max(billing_type) keep (dense_rank last order by case when billing_type != 'Cancelled'then order_number end nulls first, order_number) as billing_type, opportunity_id,BANDWIDTH,CUSTOMER_NAME,ASSOCIATE_ID from billing group by id

                                     

                                    So now please help me how to add other columns also in the select statement to get complete data set.

                                     

                                    Thanks

                                    Bhaskar.S

                                    1 2 3 Previous Next