Ayman Ayman - 3 months ago 25
SQL Question

Getting running total in sql

How can i calculate the running total from below query

Query

SELECT
dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
Suppliers.SupplierName,
dbo.Restaurants.Name as [Restaurant Name],
CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
dbo.Restaurants.Name,
dbo.PurchaseInvoices.PurchaseInvoiceDate,
dbo.PurchaseInvoices.PurchaseInvoiceNo,
Suppliers.SupplierName
ORDER BY dbo.PurchaseInvoices.PurchaseInvoiceNo


Query Out Put

+------------+--------------+---------------+-----------------+--------+
| Invoice No | Invoice Date | Supplier Name | Restaurant Name | Debit |
+------------+--------------+---------------+-----------------+--------+
| 1 | 8/26/2016 | supplier1 | restaurant 1 | 92.00 |
| 2 | 8/27/2016 | supplier1 | restaurant 2 | 47.00 |
+------------+--------------+---------------+-----------------+--------+


The desired out put should be like below

+------------+--------------+---------------+-----------------+--------+---------+
| Invoice No | Invoice Date | Supplier Name | Restaurant Name | Debit | Balance |
+------------+--------------+---------------+-----------------+--------+---------+
| 1 | 8/26/2016 | supplier1 | restaurant 1 | 92.00 | 92.00 |
| 2 | 8/27/2016 | supplier1 | restaurant 2 | 47.00 | 139.00 |
+------------+--------------+---------------+-----------------+--------+---------+


How can i achieve this? to get running total.

Answer

You can use APPLY operator or Correlated sub-query to find running total

;with result as 
(
SELECT
  dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
  dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
  Suppliers.SupplierName,
  dbo.Restaurants.Name as [Restaurant Name],
  CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
  ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
  ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
  ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
  ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
  ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
  ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
         dbo.Restaurants.Name,
         dbo.PurchaseInvoices.PurchaseInvoiceDate,
         dbo.PurchaseInvoices.PurchaseInvoiceNo,
         Suppliers.SupplierName
)
SELECT * 
FROM   result a 
       OUTER apply (SELECT Sum([debit]) 
                    FROM   result b 
                    WHERE  a.[invoice no] >= b.[invoice no]) cs (balance) 

Unfortunately you are still in Sql Server 2008, in Sql Server 2012+ we have Sum() Over(Order by) aggregate window function to calculate running which is way faster than above method

Update: For SQL SERVER 2012+ use this

;with result as
(
SELECT
  dbo.PurchaseInvoices.PurchaseInvoiceNo as [Invoice No] ,
  dbo.PurchaseInvoices.PurchaseInvoiceDate as [Invoice Date],
  Suppliers.SupplierName,
  dbo.Restaurants.Name as [Restaurant Name],
  CONVERT(decimal(9, 2), SUM(RequisitionDetails.ReceivedQuantity * RequisitionDetails.UnitPrice)) AS Debit
FROM dbo.PurchaseOrders
INNER JOIN dbo.Requisitions
  ON dbo.PurchaseOrders.RequisitionID = dbo.Requisitions.RequisitionID
INNER JOIN dbo.Restaurants
  ON dbo.Requisitions.RestaurantID = dbo.Restaurants.RestaurantID
INNER JOIN dbo.Suppliers
  ON dbo.PurchaseOrders.SupplierID = dbo.Suppliers.SupplierID
INNER JOIN dbo.Categories
  ON dbo.Requisitions.CategoryID = dbo.Categories.CategoryID
INNER JOIN dbo.PurchaseInvoices
  ON dbo.PurchaseOrders.PurchaseOrderID = dbo.PurchaseInvoices.PurchaseInvoiceID
INNER JOIN dbo.RequisitionDetails
  ON dbo.RequisitionDetails.RequisitionID = dbo.Requisitions.RequisitionID
GROUP BY dbo.PurchaseOrders.PurchaseOrderID,
         dbo.Restaurants.Name,
         dbo.PurchaseInvoices.PurchaseInvoiceDate,
         dbo.PurchaseInvoices.PurchaseInvoiceNo,
         Suppliers.SupplierName
)
SELECT *, sum(Debit) over(Order by [invoice no]) as balance
FROM   result a 
Comments