Running total with multiple item

Hiteshkumar Patel 21 Reputation points
2022-03-19T09:12:19.207+00:00

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
184774-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,807 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,813 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
{count} votes