There is strange multiplication in the script.
SUM(CAST(PurchLine.[Amount Including VAT] AS decimal(10,2)))
OVER(PARTITION BY PurchLine.[Document No_])AS "TotalAmount"
Finding the Total Amount from above.
TotalAmount * TotalAmount * Discount AS PaymentAmount
Finding the discount and final Payment Amount from the above.
For example I have 24.80 * (24.80*0.015) = 24.42
Instead of that I receive 24.30.
Also for the discount 24.80*0.015 = 49.60 This is not correct again. The correct result would be 0.372
But the strange part this is not working only when TotalAmount is lower than 3 digits.
When the Total amount is below 100, always the discount is not correct.
I'm not sure what do I have to make so it would work all the time.
The problem was that the this table with column Discount was imported into the sql server from excel file.
For some reason the column Discount was not Decimal.
All the column contain numbers like 0.0015, 0.010 and so on...
When I change the column datatype to decimal(10.4) for Discount everything start working fine.
But here is still the question, why this work for number bigger than 100 and for less than 100 don't work?