Distribute value in sql 2000

Question

#tempR table

iPriority_num    GCAMOUNT
221143           2000.00
221255           1500.00

#tempV table

iPriority_num  sPlu_cd  iDetail_num siTem_cd   ITEMAMOUNT
221143          0660     1           ACN00660    6000.00
221255          0112     1           CON00112     500.00
221255          0134     2           HAI00134     750.00
221255          0851     3           FPR50074     249.00
221255          0008     4           FNU30200     300.00
221255          0856     5           MICO0001     297.00

This is my script

SELECT t.iPriority_num as [TransactionID]
,t.iDetail_num as [Order ID],t.sPlu_cd AS [ItemCode]
,t.ITEMAMOUNT
,d.GCAMOUNT as [GC Payment]
,CASE WHEN d.GCAMOUNT < t.ITEMAMOUNT 
THEN (d.GCAMOUNT / CAST((SELECT COUNT(*) as cnt FROM #tempV t2 where t.iPriority_num = t2.iPriority_num) AS decimal(10,2))) 
ELSE
d.GCAMOUNT − t.ITEMAMOUNT
END AS DistributedAmt
FROM #tempV t
INNER JOIN #tempR dON d.iPriority_num = t.iPriority_num

The result

TransactionID   OrderID  ItemCode   ITEMAMOUNT   GC Payment    DistributedAmt
221143          1      0660        6000.00      2000.00         2000.00
221255          1      0112        500.00       1500.00         1000.00
221255          2      0134        750.00       1500.00          750.00
221255          3      0851        249.00       1500.00         1251.00
221255          4      0008        300.00       1500.00         1200.00
221255          5      0856        297.00       1500.00         1203.00

Desired Ouput

TransactionID   OrderID  ItemCode   ITEMAMOUNT   GC Payment    DistributedAmt
221143          1      0660        6000.00      2000.00         2000.00      
221255          1      0112        500.00       1500.00          500.00
221255          2      0134        750.00       1500.00          750.00
221255          3      0851        249.00       1500.00          249.00 
221255          4      0008        300.00       1500.00            1.00
221255          5      0856        297.00       1500.00            0.00

All I want is to distribute amount of each item if Item Amount is less than the GC Payment

Answer 1

This stuff usually need a cursor or a triangular join in sql server 2000.

One potential solution:

select *
into #tempR
from (
    SELECT  221143, 2000.00
    UNION ALL   
    SELECT  221255, 1500.00
) t (iPriority_num,GCAMOUNT)

select *
into #tempV
from (
     SELECT 221143, N'0660', 1, N'ACN00660', 6000.00
    UNION ALL SELECT 221255, N'0112', 1, N'CON00112', 500.00
    UNION ALL SELECT 221255, N'0134', 2, N'HAI00134', 750.00
    UNION ALL SELECT 221255, N'0851', 3, N'FPR50074', 249.00
    UNION ALL SELECT 221255, N'0008', 4, N'FNU30200', 300.00
    UNION ALL SELECT 221255, N'0856', 5, N'MICO0001', 297.00
) t (iPriority_num,sPlu_cd,iDetail_num,siTem_cd,ITEMAMOUNT)


select  *
,   CASE WHEN DistributedAmt < 0 THEN 0 ELSE DistributedAmt END AS FinalDistributedAmount
from    (
        select  t.*
        ,   case 
                when r.GCAMOUNT >= (SELECT SUM(itemamount) FROM #tempV v where v.iPriority_num = t.iPriority_num AND v.iDetail_num <= t.iDetail_num) THEN t.ItemAmount
                ELSE r.GCAmount - ISNULL((SELECT SUM(itemamount) FROM #tempV v where v.iPriority_num = t.iPriority_num AND v.iDetail_num < t.iDetail_num), 0)
            END AS DistributedAmt
        from #tempV t
        INNER JOIN #tempR r
            ON  r.iPriority_num = t.iPriority_num
    ) x

One can generate a running sum by doing a (SELECT SUM(itemamount) FROM #tempV v where v.iPriority_num = t.iPriority_num AND v.iDetail_num <= t.iDetail_num) subquery and then comparing to what's left of the original amount to distribute.

Unfortunately, it requires a bit of repetitions, you should consider upgrading to a bit modern version