SQL inner sub query calculation help needed

Question

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

could you add in your table definitions, some example data and an expected output? would make figuring this out a lot easier — Jeremy C., May 20 '15 at 11:46
include sample data and what your expected output is. Based on what you are describing you need window functions. — , May 20 '15 at 12:06
@JeremyC. please see my updated post i have inserted image explaining the issue — Mike, May 20 '15 at 12:16
@VladimirOselsky please see my updated post i have inserted image explaining the issue — Mike, May 20 '15 at 12:17
so Totalamount in the last row should have been 240 and balence 40? in which case you haven't given us the correct statement because we would need your insert statement — Jeremy C., May 20 '15 at 12:21
@JeremyC. yes you are right but there is no need of insert statement only have to fetch values and perform calculations on those tables — Mike, May 20 '15 at 12:49

score 0 · Answer 1 · answered May 20 '15 at 17:01

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

SQL inner sub query calculation help needed

1 Answers1