Hi All,
Below is the scenario i am facing. I tried to explain it with sample data.
| 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 |
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.
| ID | Billing_type | CREATED_DATE |
| 1128 | In Billing | 01-Jan-01 |
| 2115 | In billing | 27-Mar-15 |
| 2141 | New order | 01-Mar-18 |
| 1786 | In Billing | 01-Jan-01 |
| 7654 | New 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