Get running inventory

ramos_leomar · June 13, 2016, 1:20pm

Hi Sir,

Great DAy!

May I ask your help on SQL please.

I have tables like
trn_Beginning_Inventory
trn_Beginning_Inventory_Adjustment
trn_PurchaseOrder_Detail
trn_SalesOrder_Detail
trn_SalesReturn_Detail
trn_StockReturn_Detail
trn_StockTransfer_Detail
trn_StockTransfer_Receiving_Detail

I want to get the running inventory using SQL statement. Right now, here is my sql codes:

SELECT
'All State' AS Stock_State ,*
FROM
(
SELECT
f.ItemGroup,
f.ItemType,
f.ItemBrand,
f.ItemCode,
f.[Description],
f.Beginning_Inventory_Date,
f.Beginning_Inventory_Qty,
f.Purchased_Qty,
f.Sales_Qty,
f.SalesReturn_Qty,
f.StockReturn_Qty,
f.STOutgoing_Qty,
f.STIncoming_Qty,
f.Inventory
FROM
(
SELECT
s.ItemGroup,
s.ItemType,
s.ItemBrand,
s.ItemCode,
s.[Description],
s.Beginning_Inventory_Date,
s.Beginning_Inventory_Qty,
s.Purchased_Qty,
isnull(SUM(sd.Sales_Qty),0) AS Sales_Qty,
isnull(SUM(sr.SalesReturn_Qty),0) AS SalesReturn_Qty,
isnull(SUM(r.StockReturn_Qty),0) AS StockReturn_Qty,
isnull(SUM(t.STOutgoing_Qty),0) AS STOutgoing_Qty,
isnull(SUM(d.STIncoming_Qty),0) AS STIncoming_Qty,
(
s.Beginning_Inventory_Qty + s.Purchased_Qty
)- isnull(SUM(sd.Sales_Qty), 0)+isnull(SUM(sr.SalesReturn_Qty), 0)- isnull(SUM(r.StockReturn_Qty),0)-isnull(SUM(t.STOutgoing_Qty),0)+isnull(SUM(d.STIncoming_Qty),0)AS Inventory
FROM
(
SELECT
i.ItemGroup,
i.ItemType,
i.ItemBrand,
i.ItemCode,
i.[Description],
i.Beginning_Inventory_Date,
i.Beginning_Inventory_Qty,
SUM(Isnull(p.Purchased_Qty, 0))AS Purchased_Qty
FROM
(
SELECT
i.ItemGroup,
i.ItemType,
i.ItemBrand,
i.ItemCode,
i.[Description],
CASE
WHEN i.Adjustment_Qty <= 0 THEN
i.Beginning_Date
ELSE
i.Adjustment_Date
END AS Beginning_Inventory_Date,
CASE
WHEN i.Adjustment_Qty <= 0 THEN
i.Beginning_Qty
ELSE
i.Adjustment_Qty
END AS Beginning_Inventory_Qty
FROM
(
SELECT
im.*, Isnull(ad.Adjustment_Qty, 0)AS Adjustment_Qty
FROM
(
SELECT
ig.[Description] AS ItemGroup,
it.[Description] AS ItemType,
ib.[Description] AS ItemBrand,
im.ItemCode,
im.[Description],
(
SELECT
MAX(bi.[TransDate])AS [TransDate]
FROM
trn_Beginning_Inventory bi(nolock)
)AS Beginning_Date,
Isnull(
(
SELECT
MAX(bia.[TransDate])AS [TransDate]
FROM
trn_Beginning_Inventory_Adjustment bia(nolock)
WHERE
bia.ItemCode = im.ItemCode
),
(
SELECT
MAX(bi.[TransDate])AS [TransDate]
FROM
trn_Beginning_Inventory bi(nolock)
)
)AS Adjustment_Date,
Isnull(bi.Beginning_Inventory, 0)AS Beginning_Qty
FROM
tblMst_Item im(nolock)
LEFT OUTER JOIN tblMst_ItemGroup ig ON im.ItemGroupCode = ig.ItemGroupCode
LEFT OUTER JOIN tblMst_ItemType it ON im.ItemTypeCode = it.ItemTypeCode
LEFT OUTER JOIN tblMst_ItemBrand ib ON im.BrandCode = ib.BrandCode
LEFT OUTER JOIN(
SELECT
bi.ItemCode,
bi.Qty AS Beginning_Inventory
FROM
trn_Beginning_Inventory bi(nolock)
)bi ON im.ItemCode = bi.ItemCode
WHERE
im.Discontinued = 'N'
)im
LEFT OUTER JOIN(
SELECT
bia.ItemCode,
bia.[TransDate] AS Adjustment_Date,
bia.Qty AS Adjustment_Qty
FROM
trn_Beginning_Inventory_Adjustment bia(nolock)
)ad ON im.ItemCode = ad.ItemCode
AND im.Adjustment_Date = ad.Adjustment_Date
)i
)i
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS Purchased_Date,
pd.ItemCode,
SUM(pd.Qty)AS Purchased_Qty
FROM
trn_PurchaseOrder_Detail pd(nolock)
JOIN trn_PurchaseOrder_Header pm(nolock)ON pd.PurchaseCode = pm.PurchaseCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode
)p ON i.ItemCode = p.ItemCode
AND p.[Purchased_Date] > i.Beginning_Inventory_Date
GROUP BY
i.ItemGroup,
i.ItemType,
i.ItemBrand,
i.ItemCode,
i.[Description],
i.Beginning_Inventory_Date,
Beginning_Inventory_Qty
)s
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS Sales_Qty
FROM
trn_SalesOrder_Detail pd(nolock)
JOIN trn_SalesOrder_Header pm(nolock)ON pd.SalesOrderCode = pm.SalesOrderCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)sd ON s.ItemCode=sd.Itemcode
AND sd.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS SalesReturn_Qty
FROM
trn_SalesReturn_Detail pd(nolock)
JOIN trn_SalesReturn_Header pm(nolock)ON pd.SalesReturnCode = pm.SalesReturnCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)sr ON s.ItemCode=sr.Itemcode
AND sr.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS StockReturn_Qty
FROM
trn_StockReturn_Detail pd(nolock)
JOIN trn_StockReturn_Header pm(nolock)ON pd.StockReturnCode = pm.StockReturnCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)r ON s.ItemCode=r.Itemcode
AND r.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS STOutgoing_Qty
FROM
trn_StockTransfer_Detail pd(nolock)
JOIN trn_StockTransfer_Header pm(nolock)ON pd.TransferCode = pm.TransferCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)t ON s.ItemCode=t.Itemcode
AND t.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS STIncoming_Qty
FROM
trn_StockTransfer_Receiving_Detail pd(nolock)
JOIN trn_StockTransfer_Receiving_Header pm(nolock)ON pd.ReceiptCode = pm.ReceiptCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)d ON s.ItemCode=d.Itemcode
AND d.[TransDate] > s.Beginning_Inventory_Date
GROUP BY
s.ItemGroup,
s.ItemType,
s.ItemBrand,
s.ItemCode,
s.[Description],
s.Beginning_Inventory_Date,
s.Beginning_Inventory_Qty,
s.Purchased_Qty
)f
)f
WHERE
f.ItemCode IN(
160500084,
160500106,
160500107
)

But am not getting the correct output. I want that when i add inventory adjustment which has greater date than the other tables, the result will be the inventory adjustment qty.

Please help me achieve that sir. thank you

regards
leo

JamesK · June 13, 2016, 4:15pm

Your code is rather long and hard to understand, especially without having access to the tables that you are querying against.

If you are on SQL 2012 or later, you can use windowing functions to calculate running totals. You will find several examples and tutorials if you search online. For example here

devAdvcte · June 16, 2016, 10:56am

Surely this code can be simplified/optimized to generate a simple current inventory on hand report. I believe we would need to see the tables you're working with to provide sufficient guidance. If you could provide a simple layout of the tables you are working with I would be more than happy to assist.

When you, or others, log inventory, or process sales, do you modify a single column such as inventoryOnHand (and add an inventory change log) or is it that an inventory adjustment (product, qtyChange, date) is added and from there you plan to sum the inventory adjustments, as well as all sales for that product to receive your current total? I would think the latter would require far more resources/time to generate than the former on a daily basis.

ramos_leomar · June 16, 2016, 1:45pm

Hi Sirs,

Great day!

I deeply appreciate your immediate responses to my query. To give you more details, hereunder are my tables structure.

-- Table structure for [dbo].[trn_Beginning_Inventory]

DROP TABLE [dbo].[trn_Beginning_Inventory]
GO
CREATE TABLE [dbo].[trn_Beginning_Inventory] (
[ItemCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[TransDate] datetime NULL ,
[UnitCode] bigint NULL ,
[EncodedBy] nvarchar(50) NULL ,
[LotNumber] nvarchar(20) NULL
)

GO

-- Records of trn_Beginning_Inventory

-- Table structure for [dbo].[trn_Beginning_Inventory_Adjustment]

DROP TABLE [dbo].[trn_Beginning_Inventory_Adjustment]
GO
CREATE TABLE [dbo].[trn_Beginning_Inventory_Adjustment] (
[TransDate] datetime NULL ,
[ItemCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[UnitCode] bigint NULL ,
[AdjustmentNo] int NULL ,
[Justification] nvarchar(500) NULL
)

GO

-- Records of trn_Beginning_Inventory_Adjustment

-- Table structure for [dbo].[trn_PurchaseOrder_Detail]

DROP TABLE [dbo].[trn_PurchaseOrder_Detail]
GO
CREATE TABLE [dbo].[trn_PurchaseOrder_Detail] (
[PurchaseCode] nvarchar(10) NOT NULL ,
[TransTypeCode] bigint NULL ,
[ItemCode] bigint NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[UnitPrice] money NULL ,
[Amount] money NULL ,
[LotNumber] nvarchar(20) NULL
)

GO

-- Records of trn_PurchaseOrder_Detail

-- Table structure for [dbo].[trn_PurchaseOrder_Header]

DROP TABLE [dbo].[trn_PurchaseOrder_Header]
GO
CREATE TABLE [dbo].[trn_PurchaseOrder_Header] (
[PurchaseCode] nvarchar(10) NOT NULL DEFAULT '' ,
[DocTypeCode] bigint NULL ,
[CompanyCode] nvarchar(9) NULL ,
[DeliveryDate] datetime NULL ,
[ReferenceNo] nvarchar(50) NULL ,
[StatusCode] nvarchar(2) NULL ,
[Remarks] nvarchar(255) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[TransDate] datetime NULL ,
[DueDate] datetime NULL ,
[CreditTermCode] bigint NULL ,
[EmployeeCode] bigint NULL ,
[TruckCode] bigint NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_PurchaseOrder_Header

-- Table structure for [dbo].[trn_SalesOrder_Detail]

DROP TABLE [dbo].[trn_SalesOrder_Detail]
GO
CREATE TABLE [dbo].[trn_SalesOrder_Detail] (
[SalesOrderCode] nvarchar(10) NOT NULL ,
[TransTypeCode] bigint NULL ,
[ItemCode] bigint NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[UnitPrice] money NULL ,
[Amount] money NULL ,
[LotNumber] nvarchar(20) NULL
)

GO

-- Records of trn_SalesOrder_Detail

-- Table structure for [dbo].[trn_SalesOrder_Header]

DROP TABLE [dbo].[trn_SalesOrder_Header]
GO
CREATE TABLE [dbo].[trn_SalesOrder_Header] (
[SalesOrderCode] nvarchar(10) NOT NULL ,
[DocTypeCode] bigint NULL ,
[CustomerCode] nvarchar(12) NULL ,
[PaymentTypeCode] bigint NULL ,
[CreditTermCode] bigint NULL ,
[TransDate] datetime NULL ,
[DeliveryDate] datetime NULL ,
[DueDate] datetime NULL ,
[CustRef] nvarchar(20) NULL ,
[Remarks] nvarchar(100) NULL ,
[StatusCode] nvarchar(2) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[LoadingCode] nvarchar(10) NULL ,
[PaymentStatusCode] nvarchar(2) NULL ,
[ShippingCode] nvarchar(5) NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_SalesOrder_Header

-- Table structure for [dbo].[trn_SalesReturn_Detail]

DROP TABLE [dbo].[trn_SalesReturn_Detail]
GO
CREATE TABLE [dbo].[trn_SalesReturn_Detail] (
[SalesReturnCode] nvarchar(10) NULL ,
[ItemCode] bigint NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[TransTypeCode] bigint NULL ,
[UnitPrice] money NULL ,
[Amount] money NULL
)

GO

-- Records of trn_SalesReturn_Detail

-- Table structure for [dbo].[trn_SalesReturn_Header]

DROP TABLE [dbo].[trn_SalesReturn_Header]
GO
CREATE TABLE [dbo].[trn_SalesReturn_Header] (
[SalesReturnCode] nvarchar(10) NOT NULL ,
[DocTypeCode] bigint NULL ,
[CustomerCode] nvarchar(12) NULL ,
[TransDate] datetime NULL ,
[DeliveryDate] datetime NULL ,
[ReferenceNo] nvarchar(20) NULL ,
[Remarks] nvarchar(100) NULL ,
[StatusCode] nvarchar(2) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[Reason] nvarchar(255) NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_SalesReturn_Header

-- Table structure for [dbo].[trn_StockReturn_Detail]

DROP TABLE [dbo].[trn_StockReturn_Detail]
GO
CREATE TABLE [dbo].[trn_StockReturn_Detail] (
[StockReturnCode] nvarchar(10) NULL ,
[ItemCode] bigint NULL ,
[UnitCode] money NULL ,
[Qty] decimal(18) NULL ,
[TransTypeCode] bigint NULL ,
[UnitPrice] money NULL ,
[Amount] money NULL
)

GO

-- Records of trn_StockReturn_Detail

-- Table structure for [dbo].[trn_StockReturn_Header]

DROP TABLE [dbo].[trn_StockReturn_Header]
GO
CREATE TABLE [dbo].[trn_StockReturn_Header] (
[StockReturnCode] nvarchar(10) NOT NULL ,
[DocTypeCode] bigint NULL ,
[CompanyCode] nvarchar(9) NULL ,
[TransDate] datetime NULL ,
[DeliveryDate] datetime NULL ,
[ReferenceNo] nvarchar(20) NULL ,
[Remarks] nvarchar(100) NULL ,
[StatusCode] nvarchar(2) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_StockReturn_Header

-- Table structure for [dbo].[trn_StockTransfer_Detail]

DROP TABLE [dbo].[trn_StockTransfer_Detail]
GO
CREATE TABLE [dbo].[trn_StockTransfer_Detail] (
[TransferCode] nvarchar(8) NULL ,
[ItemCode] bigint NOT NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[UnitPrice] money NULL ,
[Amount] money NULL
)

GO

-- Records of trn_StockTransfer_Detail

-- Table structure for [dbo].[trn_StockTransfer_Header]

DROP TABLE [dbo].[trn_StockTransfer_Header]
GO
CREATE TABLE [dbo].[trn_StockTransfer_Header] (
[TransferCode] nvarchar(8) NOT NULL ,
[TruckCode] bigint NULL ,
[DriverCode] nvarchar(12) NULL ,
[DestinationWareHouse] nvarchar(5) NULL ,
[TransDate] datetime NULL ,
[Remarks] nvarchar(100) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[DocTypeCode] bigint NULL ,
[StatusCode] nvarchar(2) NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_StockTransfer_Header

-- Table structure for [dbo].[trn_StockTransfer_Receiving_Detail]

DROP TABLE [dbo].[trn_StockTransfer_Receiving_Detail]
GO
CREATE TABLE [dbo].[trn_StockTransfer_Receiving_Detail] (
[ReceiptCode] nvarchar(8) NULL ,
[ItemCode] bigint NOT NULL ,
[UnitCode] bigint NULL ,
[Qty] decimal(18) NULL ,
[UnitPrice] money NULL ,
[Amount] money NULL
)

GO

-- Records of trn_StockTransfer_Receiving_Detail

-- Table structure for [dbo].[trn_StockTransfer_Receiving_Header]

DROP TABLE [dbo].[trn_StockTransfer_Receiving_Header]
GO
CREATE TABLE [dbo].[trn_StockTransfer_Receiving_Header] (
[ReceiptCode] nvarchar(8) NOT NULL ,
[TruckCode] bigint NULL ,
[DriverCode] nvarchar(12) NULL ,
[SourceWarehouse] nvarchar(5) NULL ,
[TransDate] datetime NULL ,
[Remarks] nvarchar(100) NULL ,
[EncodedBy] nvarchar(50) NULL ,
[DateEncoded] datetime NULL ,
[ModifiedBy] nvarchar(50) NULL ,
[LastDateModified] datetime NULL ,
[DocTypeCode] bigint NULL ,
[StatusCode] nvarchar(2) NULL ,
[ReferenceNo] nvarchar(15) NULL ,
[AutoCode] int NOT NULL
)

GO

-- Records of trn_StockTransfer_Receiving_Header

My goal is to achieve the running inventory of my current products using this formula
Running Inventory=Beginning Inventory + Purchases - Sales + Sales Return - Stock Return + Stock Transfer Incoming - Stock Transfer Outgoing.

But, when I add record/s in the beginning inventory adjustment table, the running inventory to be captured is the adjustment quantity, if adjustment date is greater than the tables - Beginning Inventory , Purchases , Sales , Sales Return , Stock Return , Stock Transfer Incoming, Stock Transfer Outgoing.

Thank you very much for your valuable response dear developers.

Regards,
Leo

ramos_leomar · June 16, 2016, 2:37pm

Here is my current SQL Query which im not getting the correct running inventory

SELECT
'All State' AS Stock_State ,*
FROM
(
SELECT
f.ItemGroup,
f.ItemType,
f.ItemBrand,
f.ItemCode,
f.[Description],
f.Beginning_Inventory_Date,
f.Beginning_Inventory_Qty,
f.Purchased_Qty,
f.Sales_Qty,
f.SalesReturn_Qty,
f.StockReturn_Qty,
f.STOutgoing_Qty,
f.STIncoming_Qty,
f.Inventory
FROM
(
SELECT
s.ItemGroup,
s.ItemType,
s.ItemBrand,
s.ItemCode,
s.[Description],
s.Beginning_Inventory_Date,
s.Beginning_Inventory_Qty,
s.Purchased_Qty,
isnull(SUM(sd.Sales_Qty),0) AS Sales_Qty,
isnull(SUM(sr.SalesReturn_Qty),0) AS SalesReturn_Qty,
isnull(SUM(r.StockReturn_Qty),0) AS StockReturn_Qty,
isnull(SUM(t.STOutgoing_Qty),0) AS STOutgoing_Qty,
isnull(SUM(d.STIncoming_Qty),0) AS STIncoming_Qty,
(
s.Beginning_Inventory_Qty + s.Purchased_Qty
)- isnull(SUM(sd.Sales_Qty), 0)+isnull(SUM(sr.SalesReturn_Qty), 0)- isnull(SUM(r.StockReturn_Qty),0)-isnull(SUM(t.STOutgoing_Qty),0)+isnull(SUM(d.STIncoming_Qty),0)AS Inventory
FROM
(
SELECT
i.ItemGroup,
i.ItemType,
i.ItemBrand,
i.ItemCode,
i.[Description],
i.Beginning_Inventory_Date,
i.Beginning_Inventory_Qty,
SUM(Isnull(p.Purchased_Qty, 0))AS Purchased_Qty
FROM
(
SELECT
i.ItemGroup,
i.ItemType,
i.ItemBrand,
i.ItemCode,
i.[Description],
CASE
WHEN i.Adjustment_Qty <= 0 THEN
i.Beginning_Date
ELSE
i.Adjustment_Date
END AS Beginning_Inventory_Date,
CASE
WHEN i.Adjustment_Qty <= 0 THEN
i.Beginning_Qty
ELSE
i.Adjustment_Qty
END AS Beginning_Inventory_Qty
FROM
(
SELECT
im.*, Isnull(ad.Adjustment_Qty, 0)AS Adjustment_Qty
FROM
(
SELECT
ig.[Description] AS ItemGroup,
it.[Description] AS ItemType,
ib.[Description] AS ItemBrand,
im.ItemCode,
im.[Description],
(
SELECT
MAX(bi.[TransDate])AS [TransDate]
FROM
trn_Beginning_Inventory bi(nolock)
)AS Beginning_Date,
Isnull(
(
SELECT
MAX(bia.[TransDate])AS [TransDate]
FROM
trn_Beginning_Inventory_Adjustment bia(nolock)
WHERE
bia.ItemCode = im.ItemCode
),
(
SELECT
MAX(bi.[TransDate])AS [TransDate]
FROM
trn_Beginning_Inventory bi(nolock)
)
)AS Adjustment_Date,
Isnull(bi.Beginning_Inventory, 0)AS Beginning_Qty
FROM
tblMst_Item im(nolock)
LEFT OUTER JOIN tblMst_ItemGroup ig ON im.ItemGroupCode = ig.ItemGroupCode
LEFT OUTER JOIN tblMst_ItemType it ON im.ItemTypeCode = it.ItemTypeCode
LEFT OUTER JOIN tblMst_ItemBrand ib ON im.BrandCode = ib.BrandCode
LEFT OUTER JOIN(
SELECT
bi.ItemCode,
bi.Qty AS Beginning_Inventory
FROM
trn_Beginning_Inventory bi(nolock)
)bi ON im.ItemCode = bi.ItemCode
WHERE
im.Discontinued = 'N'
)im
LEFT OUTER JOIN(
SELECT
bia.ItemCode,
bia.[TransDate] AS Adjustment_Date,
bia.Qty AS Adjustment_Qty
FROM
trn_Beginning_Inventory_Adjustment bia(nolock)
)ad ON im.ItemCode = ad.ItemCode
AND im.Adjustment_Date = ad.Adjustment_Date
)i
)i
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS Purchased_Date,
pd.ItemCode,
SUM(pd.Qty)AS Purchased_Qty
FROM
trn_PurchaseOrder_Detail pd(nolock)
JOIN trn_PurchaseOrder_Header pm(nolock)ON pd.PurchaseCode = pm.PurchaseCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode
)p ON i.ItemCode = p.ItemCode
AND p.[Purchased_Date] > i.Beginning_Inventory_Date
GROUP BY
i.ItemGroup,
i.ItemType,
i.ItemBrand,
i.ItemCode,
i.[Description],
i.Beginning_Inventory_Date,
Beginning_Inventory_Qty
)s
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS Sales_Qty
FROM
trn_SalesOrder_Detail pd(nolock)
JOIN trn_SalesOrder_Header pm(nolock)ON pd.SalesOrderCode = pm.SalesOrderCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)sd ON s.ItemCode=sd.Itemcode
AND sd.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS SalesReturn_Qty
FROM
trn_SalesReturn_Detail pd(nolock)
JOIN trn_SalesReturn_Header pm(nolock)ON pd.SalesReturnCode = pm.SalesReturnCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)sr ON s.ItemCode=sr.Itemcode
AND sr.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS StockReturn_Qty
FROM
trn_StockReturn_Detail pd(nolock)
JOIN trn_StockReturn_Header pm(nolock)ON pd.StockReturnCode = pm.StockReturnCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)r ON s.ItemCode=r.Itemcode
AND r.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS STOutgoing_Qty
FROM
trn_StockTransfer_Detail pd(nolock)
JOIN trn_StockTransfer_Header pm(nolock)ON pd.TransferCode = pm.TransferCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)t ON s.ItemCode=t.Itemcode
AND t.[TransDate] > s.Beginning_Inventory_Date
LEFT OUTER JOIN(
SELECT
pm.[TransDate] AS TransDate,
pd.ItemCode,
SUM(pd.Qty)AS STIncoming_Qty
FROM
trn_StockTransfer_Receiving_Detail pd(nolock)
JOIN trn_StockTransfer_Receiving_Header pm(nolock)ON pd.ReceiptCode = pm.ReceiptCode
AND pm.StatusCode = 'PS'
GROUP BY
pm.[TransDate],
pd.ItemCode)d ON s.ItemCode=d.Itemcode
AND d.[TransDate] > s.Beginning_Inventory_Date
GROUP BY
s.ItemGroup,
s.ItemType,
s.ItemBrand,
s.ItemCode,
s.[Description],
s.Beginning_Inventory_Date,
s.Beginning_Inventory_Qty,
s.Purchased_Qty
)f
)f
WHERE
f.ItemCode IN(
160500084,
160500106,
160500107
)

JeffModen · June 17, 2016, 12:16pm

Running Totals are actually a part of what some call a "Presentation Layer". It's important to keep the Data Layer separate from the Presentation Layer for two reasons.... ease of coding and performance. And, speaking of performance, it's always good to write "set based" code. Unfortunately, a lot of people misinterpret that and think that "set based" means "all in one query", and nothing could be further from the truth.

My recommendation would be to write the code to simply (as possible) gather the data that you want to do the running totals for and store it in a Temp Table. From there, you have several choices as to how to do the running total.

If you have a GUI, the best way would be to pass the summary information from the Temp Table to the GUI and let the GUI figure it out.

If the running totals are actually going to be used within SQL Server, the same holds true. Summarize the regular totals in a Temp Table and then do the running total.

If you could present such a Temp Table result and some sample data contained in that table in a readily consumable INSERT/SELECT UNION ALL format, we could show you how to easily do the running total. Make sure that you also tell use what version of SQL Server you're using because there's another choice for doing running totals from 2012 and up.