Struggling with Logic in T-SQL

Question

I need some help with T-SQL.

I can't use loops, cursors, etc. This is because I need high performance.

I would be very grateful if you may help me.

Here is my problem:

I have a table called __tt_Freight_Product which has the following columns:

dt_reference_date, id_contract, qtt_terminal_loaded

And a table called Product which has the following columns:

dt_reference_date, id_contract, id_fixing, qtt_fixing, qtt_terminal

There are NO foreign key between them, yet id_contract and dt_reference_date are supposed to be the same.

Example data in __tt_Freight_Product:

('2015-02-25', '0000006-t12', 200000)
('2015-02-26', '0000006-t12', 200000)
('2015-02-28', '0000006-t12', 100000)

That data means that 200,000 TONS was loaded on contract '0000006-t12' into terminal on '2015-02-25', and so on

Example data in Product:

('2015-02-24', '0000006-t12', 1, 300000, 0)
('2015-02-25', '0000006-t12', 1, 300000, 0)
('2015-02-26', '0000006-t12', 1, 300000, 0)
('2015-02-27', '0000006-t12', 1, 300000, 0)
('2015-02-28', '0000006-t12', 1, 300000, 0)
('2015-02-29', '0000006-t12', 1, 300000, 0)
('2015-02-24', '0000006-t12', 2, 200000, 0)
('2015-02-25', '0000006-t12', 2, 200000, 0)
('2015-02-26', '0000006-t12', 2, 200000, 0)
('2015-02-27', '0000006-t12', 2, 200000, 0)
('2015-02-28', '0000006-t12', 2, 200000, 0)
('2015-02-29', '0000006-t12', 2, 200000, 0)

What I need to accomplish is to split the loaded volumes that are on __tt_Freight_Product into the Product table, per dt_reference_date/id_contract/id_fixing. That split volume will be updated on the Product table.

It's kinda we had to divide the volume per "id_fixing".

So, considering the example data on __tt_Freight_Product we will update:

dt_reference_date: '2015-02-24'

Nothing will be updated as the cargo loaded just con 25th

dt_reference_date: '2015-02-25'

qtt_terminal of id_fixing "1" will be updated to 200,000

qtt_terminal of id_fixing "2" won't change

dt_reference_date: '2015-02-26'

qtt_terminal of id_fixing "1" will be updated to 300,000

qtt_terminal of id_fixing "2" will be updated to 100,000

dt_reference_date: '2015-02-27'

qtt_terminal of id_fixing "1" will be updated to 300,000, as we had cargo on the previous day and no loaded cargo on 27th OR considering we already have id_fixing '1' "full allocated"

qtt_terminal of id_fixing "2" will be updated to 100,000, as we had cargo on the previous day and no loaded cargo on 27th

dt_reference_date: '2015-02-28'

qtt_terminal of id_fixing "1" will be updated to 300,000, as we had cargo on the previous day and no loaded cargo on 27th OR considering we already have id_fixing '1' "full allocated"

qtt_terminal of id_fixing "2" will be updated to 200,000

dt_reference_date: '2015-02-29'

qtt_terminal of id_fixing "1" will be updated to 300,000, as we had cargo on the previous day and no loaded cargo on 29th OR considering we already have id_fixing '1' "full allocated"

qtt_terminal of id_fixing "2" will be updated to 200,000, as we had cargo on the previous day and no loaded cargo on 29th OR considering we already have id_fixing '2' "full allocated"

EDIT

I have to get the value [qtt_terminal_loaded] that is in __tt_Freight_Product and "allocate" it on table Product, column qtt_terminal. But, I have to distribute __tt_Freight_Product.qtt_terminal_loaded into one or more "id_fixing", in the order that they appear (id_fixing = 1 and 2).

That way, as in the example data, in 25th, we have qtt_terminal_loaded equals to 200,000. So, this 200,00, is the amount that I am able to distribute into multiple id_fixing. But, id_fixing = 1, has a "balance" of 300,00 "to receive" from qtt_terminal_loaded (which is determined by the column "qtt_fixing". qtt_terminal, the column that I am trying to update, can't be more than qtt_fixing. Never. That way, on 26th, if we sum previous qtt_terminal_loaded, we would get 400,00. But first id_fixing is just 300,000. So, from that day on, I start to allocate into id_fixing = 2. In this case, 100,000 on that day.

Was I clear? I'm trying to do my best to explain.

END EDIT

Image containing sample output:

Scripts containing table creations and example data:

-- CREATE sample "__tt_Freight_Product"
IF object_id('tempdb..#__tt_Freight_Product') IS NOT NULL
BEGIN
    DROP TABLE #__tt_Freight_Product;
END;

SELECT  a.dt_reference_date,
        a.id_contract,
        a.qtt_terminal_loaded
INTO    #__tt_Freight_Product
FROM    ( VALUES ( '2015-02-25', '0000006-t12', 200000),
        ( '2015-02-26', '0000006-t12', 200000),
        ( '2015-02-28', '0000006-t12', 100000) ) a ( dt_reference_date, id_contract, qtt_terminal_loaded );

-- CREATE sample "Product"
IF object_id('tempdb..#Product') IS NOT NULL
BEGIN
    DROP TABLE #Product;
END;

SELECT  a.dt_reference_date,
        a.id_contract,
        a.id_fixing,
        a.qtt_fixing,
        a.qtt_terminal
INTO    #Product
FROM    ( VALUES ( '2015-02-24', '0000006-t12', 1, 300000, 0),
        ( '2015-02-25', '0000006-t12', 1, 300000, 0),
        ( '2015-02-26', '0000006-t12', 1, 300000, 0),
        ( '2015-02-27', '0000006-t12', 1, 300000, 0),
        ( '2015-02-28', '0000006-t12', 1, 300000, 0),
        ( '2015-02-29', '0000006-t12', 1, 300000, 0),
        ( '2015-02-24', '0000006-t12', 2, 200000, 0),
        ( '2015-02-25', '0000006-t12', 2, 200000, 0),
        ( '2015-02-26', '0000006-t12', 2, 200000, 0),
        ( '2015-02-27', '0000006-t12', 2, 200000, 0),
        ( '2015-02-28', '0000006-t12', 2, 200000, 0),
        ( '2015-02-29', '0000006-t12', 2, 200000, 0) ) a ( dt_reference_date, id_contract, id_fixing, qtt_fixing, qtt_terminal );

Any ideas?

Answer 1

There seems to be some denormalization in your data model. The same qtt_fixing value seems to be repeated for all occurrences of the same id_contract and id_fixing, right? Assuming that's guaranteed, here's a possible solution:

with f as
(
  select *,
    sum(qtt_terminal_loaded) over(partition by id_contract
                                  order by dt_reference_date
                                  rows unbounded preceding) as runsum
  from #__tt_freight_product
),
p as
(
  select *,
    sum(qtt_fixing) over(partition by id_contract, dt_reference_date
                         order by id_fixing
                         rows unbounded preceding) as runsum
  from #product
)
update p
  set qtt_terminal =
    case
      when f.runsum >= p.runsum then p.qtt_fixing
      when p.runsum - p.qtt_fixing < f.runsum then f.runsum - (p.runsum - p.qtt_fixing)
      else 0
    end
from p
  outer apply (select top (1) *
               from f
               where f.id_contract = p.id_contract
                 and f.dt_reference_date <= p.dt_reference_date
               order by f.dt_reference_date desc) as f;

select *
from #product;

Note that the solution is not optimized at all. I provide it just so that you can study the logic. A few more steps would be required to optimize it, like storing intermediate results in temporary tables and indexing them. Also, this solution uses aggregate window functions with a frame--a feature that was introduced in SQL Server 2012. If you're using an older version, you would probably be better off using an iterative solution.

Cheers, Itzik