-1

The goal is to understand which manager provides the client's order without grouping by the managers. It's important, that during the time, clients' managers are changing, and that may happen several times, including returnee to the previous manager.

We have an Orders table:

Client Order Date Order Amount
Cl.1 01/01/22 120
Cl.2 01/01/22 450
Cl.3 02/01/22 340
Cl.4 10/01/22 210
Cl.1 05/02/22 119
Cl.2 07/02/22 454
Cl.3 08/02/22 342
Cl.4 11/02/22 225

We have a Managers changing table:

Client Manager Change Date Manager
Cl.3 01/01/22 Mng.2
Cl.4 08/01/22 Mng.3
Cl.2 07/02/22 Mng.2
Cl.3 06/02/22 Mng.1
Cl.1 15/02/22 Mng.2

The default manager at the start is Mng.1.

The result table is Orders table + Manager column:

Client Order Date Order Amount Manager
Cl.1 01/01/22 120 Mng.1
Cl.2 01/01/22 450 Mng.1
Cl.3 02/01/22 340 Mng.2
Cl.4 10/01/22 210 Mng.3
Cl.1 05/02/22 119 Mng.1
Cl.2 07/02/22 454 Mng.2
Cl.3 08/02/22 342 Mng.1
Cl.4 11/02/22 225 Mng.3
Christ
  • 3
  • 2
  • 1
    It's typically best to show what you have tried so far, and why it didn't work. Otherwise it looks like you are just trying to get other people to do your homework for you. – mc110 Mar 27 '23 at 08:24
  • 1
    I used **left outer join** function, but the we result was not as expected. Thank you, next time will provide my code too. – Christ Mar 28 '23 at 06:16

1 Answers1

1
with data as (
    select *,
        row_number() over (partition by m.Client, o.OrderDate order by o.ClientDate desc) as rn,
        coalesce(m.Manager, 'Mng.1') as AssignedManager
    from Orders o left outer join Managers m
        on m.Client = o.Client and m.ClientDate <= o.OrderDate
)
select * from data where rn = 1;
shawnt00
  • 16,443
  • 3
  • 17
  • 22
  • That is not the demanded result. | Clinet | Order Date | Order Amount |m | | -------- | -------------- | -------------- |-------------- | | Cl.2 | 2022-02-07 |454 |1 | | Cl.3 | 2022-02-08 |342 |1 | | Cl.4 | 2022-02-11 |225 |1 | – Christ Mar 27 '23 at 14:44
  • I received only 4 rows. That is not correct. row_number didn't do a benefit. I changed the code, expelled where `with data as ( select o.[Client] ,o.[Order Date] ,o.[Order Amount] ,row_number() over (partition by o.Client order by o.[Order Date] desc) as rn ,coalesce(m.Manager, 'Mng.1') as AssignedManager from [Orders] o left outer join [Managers] m on m.Client = o.Client and m.[Manager Change Date] <= o.[Order Date] ) select * from data --where rn = 1;` The result is 9 rows, one more, than the goal result. The 7th row, with Cl.3 / 2022.02.08 / Mng.2 is incorrect – Christ Mar 28 '23 at 06:36
  • Try my update. You don't have an order id so I'm using the date. – shawnt00 Mar 28 '23 at 06:45
  • With the new updated code we are missing the **Cl.2 / 2022-01-01 / 450 / 2 / Mng.1** order( – Christ Mar 28 '23 at 06:55
  • Try again. We need client and date. – shawnt00 Mar 28 '23 at 06:58
  • 1
    Brilliant! It worked and I caught the logic) @shawnt00 Thank you for your time and efforts! – Christ Mar 28 '23 at 10:38