Running total with multiple item
Hello Everyone,
below i create string to get opening balance and closing balance for my daily stock opening and closing balance but when same item is transfer on same day its give me same balance for both entry i need a help for this below is my string
SELECT
A.ProductCode,
D.Store,
A.IssueQty,
A.TotalKG AS TotalKG,
B.ob_bl_kgs,
ISNULL(A.ToStore,0) ToStore
FROM (
SELECT
ProductCode,
SUM(IssueQty) AS IssueQty,
SUM(TotalKG) AS TotalKG,
ToStore
FROM tblStockIssue
WHERE IssueDate = '20220318'
AND StoreLocation IN (1)
AND ToStore IS NOT NULL
GROUP BY
ProductCode,
ToStore
) A
INNER JOIN (SELECT AutoNumbers, StoreName +' [ ' + StoreCode +' ]' AS Store FROM tblStores) AS D
ON A.ToStore = D.AutoNumbers
INNER JOIN
(
SELECT A.ProductCode, SUM(A.ob_bl_kgs) ob_bl_kgs, 1 Stype
FROM (
SELECT ProductCode, SUM(TotalKG) ob_bl_kgs
FROM tblStocksReceived SS
WHERE (StoreLocation = 1 AND DateReceived <= '20220318')
GROUP BY ProductCode
UNION ALL
SELECT ProductCode, -SUM(TotalKG) TotalKG
FROM tblStockIssue S
WHERE
(StoreLocation = 1 AND IssueDate < '20220318')
OR
(StoreLocation = 1 AND IssueDate = '20220318' AND ToStore IS NULL)
GROUP BY ProductCode
) A
GROUP BY A.ProductCode) B
ON A.ProductCode = B.ProductCode
ORDER BY A.ProductCode
----Result below