#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