SQL inner sub query calculation help needed

59 Views Asked by At

I want to calculate balance runtime from totalammount - AmmountReceived = Balance next row ..

Totalammont(actually will be previous runtime calculated balance should be here) - AmmountReceived = Balance and so on ....

Here is my query which doesnt decrement totalAmmount

select ID,
       RecvDate,
       BillNo,
       ModeofPayment,
       (Select Item_name from Product where ID = CampusRecovery.ItemID) as Item,
       SUM(AmountReceived) as AmountReceived,
       (Select CampusName from Campus where CampusID = CampusRecovery.CampusID) as Campus,
       IsNull((select SUM(Inventory_Out.TotalAmount)
               from Inventory_Out
               where Inventory_Out.BillNo = CampusRecovery.BillNo),0) as TotalAmmount,
       IsNull((select SUM(Inventory_Out.TotalAmount)
               from Inventory_Out
               where Inventory_Out.BillNo = CampusRecovery.BillNo),0) - SUM(AmountReceived) as Balance
from CampusRecovery 
where RecvDate Between @startdt and @enddt
  and CampusID = 2
Group By CampusRecovery.CampusID,
         CampusRecovery.ItemID,
         CampusRecovery.RecvDate,
         CampusRecovery.BillNo,
         CampusRecovery.ModeofPayment,
         CampusRecovery.ID

enter image description here

1

There are 1 best solutions below

0
On
select cr.BillNum, cr.DelievryDate,cr.AmmountReceived,(select TotalAmmount from InventoryOut where InventoryOut.BillNum = cr.BillNum) as TotalBill,
(select TotalAmmount -
(select Sum(AmmountReceived) from CampusRecovery ch where ch.BillNum = io.BillNum )
 from InventoryOut io where io.BillNum = cr.BillNum )--- sum(cr.AmmountReceived)
as Balance
from CampusRecovery  cr where CampusID = 1
group by 
cr.BillNum,
cr.DelievryDate,
cr.BillNum,
cr.AmmountReceived