# How to subtract output products from input products base on receipts respectively?

• ### Question

• Hi

in my inventory application, i have a table which hold input and output receipts.

for better understanding, here is my example :

Input Receipts :
ReceiptID  StoreID  ProductID  Quantity
------------------------------------------
1            1         4201      50
2            1         4201      30
3            1         4201      80
------------------------------------------
Sum=160

Output Receipts :
ReceiptID  StoreID  ProductID  Quantity
------------------------------------------
4            1         4201      20
5            1         4201      25
6            1         4201      40
------------------------------------------
Sum=85

My Expected Result from Input-Output Receipts :
ReceiptID  StoreID  ProductID  Available
--------------------------------------------------
4            1         4201      0
5            1         4201      0
3            1         4201      75

As you can see in final result, the available product=4201 is 75 from receiptID=3(output products subtracts from input products respectively!)

How can i do that?

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

Friday, September 30, 2016 3:28 PM

• Hi Hamed_1983,

Script below applies to my previous reply, you may change your code accordingly.

```CTE_Input AS
(
SELECT a.*
, (SELECT SUM(b.[Quantity])
FROM @T b
WHERE b.[ReceiptTypeID] = 1
AND b.[StoreID] = a.[StoreID]
AND b.[ProductID] = a.[ProductID]
AND b.[ReceiptID] <= a.[ReceiptID]) AS [Quantity_Input]
FROM @T a
WHERE a.[ReceiptTypeID] = 1
)```

Sam Zha
TechNet Community Support

• Marked as answer by Thursday, October 6, 2016 5:28 PM
Thursday, October 6, 2016 6:07 AM

### All replies

• What you are referring too is a "running total".

http://blog.sqlauthority.com/2014/10/04/sql-server-how-to-find-running-total-in-sql-server/

Friday, September 30, 2016 5:41 PM
• Thanks Tom

i want to find out the remaining products in the specific store is owned by which input receiptID?

in my above example the receiptID=3 have remaining product 4201.

Thanks again

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

Friday, September 30, 2016 10:19 PM
• >> in my inventory application, I have a table which hold input and output receipts.<< Where is the DDL? Did you read the form posting rules? Now we have to do the basic work that you were too lazy to do for us. CREATE TABLE Inventory (receipt_nbr CHAR(12) NOT NULL PRIMARY KEY, store_id CHAR(5) NOT NULL, product_gtin CHAR(15) NOT NULL receipt_qty INTEGER NOT NULL CHECK (receipt_qty <> 0)); If you were to design a personnel schema, would you have a "male_personnel" and a "female_personnel" in it? No! Of course not! But this is what you have done with inputs and outputs to the inventory. They should be in one table. The quantity going in and out can be plus or minus, and that will tell you whether the transaction is a debit or credit. Remember that 85 to 95% of the work in SQL is done in the DDL and not kludges and repaired with the DML after the fact.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Saturday, October 1, 2016 1:35 AM
• Sorry Celko!

Here is my tables structure :

```CREATE TABLE [dbo].[ReceiptDetails](
[ReceiptDetailID] [uniqueidentifier] NOT NULL,
[ReceiptID] [int] NULL,
[Barcode] [nvarchar](50) NULL,
[ProductID] [int] NULL,
[ProductionDate] [char](10) NULL,
[ExpirationDate] [char](10) NULL,
[UnitPrice] [money] NULL,
[Quantity] [money] NULL,
[MeasurmentID] [int] NULL,
[Subtotal] [money] NULL,
[Discount] [money] NULL,
[SaleUnitPrice] [money] NULL,
[MasterReceiptDetailID] [uniqueidentifier] NULL,
CONSTRAINT [PK_ReceiptDetails] PRIMARY KEY CLUSTERED
(
[ReceiptDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
GO
/****** Object:  Table [dbo].[Receipts]    Script Date: 10/1/2016 6:27:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
GO
CREATE TABLE [dbo].[Receipts](
[ReceiptID] [int] NOT NULL,
[CreatedBy] [int] NULL,
[TimeCreated] [datetime] NULL,
[DateCreated] [char](10) NULL,
[LastModifiedBy] [int] NULL,
[LastModifiedTime] [datetime] NULL,
[LastModifiedDate] [char](10) NULL,
[ReceiptDate] [char](10) NULL,
[ReceiptTypeID] [int] NULL,
[Description] [nvarchar](max) NULL,
[ReceiptCode] [int] NULL,
[ReceiptTransferCode] [int] NULL,
[ReceiptStatusID] [int] NULL,
[StoreID] [int] NULL,
[OwnerTableName] [nvarchar](50) NULL,
[OwnerID] [nvarchar](50) NULL,
CONSTRAINT [PK_Receipts] PRIMARY KEY CLUSTERED
(
[ReceiptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
GO
/****** Object:  Table [dbo].[ReceiptTypes]    Script Date: 10/1/2016 6:27:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReceiptTypes](
[ReceiptTypeID] [int] NOT NULL,
[ReceiptTypeName] [nvarchar](50) NULL,
CONSTRAINT [PK_ReceiptTypes] PRIMARY KEY CLUSTERED
(
[ReceiptTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ReceiptDetails] CHECK CONSTRAINT [FK_ReceiptDetails_Measurment]
GO
ALTER TABLE [dbo].[ReceiptDetails]  WITH CHECK ADD  CONSTRAINT [FK_ReceiptDetails_Receipts] FOREIGN KEY([ReceiptID])
REFERENCES [dbo].[Receipts] ([ReceiptID])
GO
ALTER TABLE [dbo].[ReceiptDetails] CHECK CONSTRAINT [FK_ReceiptDetails_Receipts]
GO
ALTER TABLE [dbo].[Receipts]  WITH CHECK ADD FOREIGN KEY([ReceiptTypeID])
REFERENCES [dbo].[ReceiptTypes] ([ReceiptTypeID])
GO```

After creating tables, plz insert 2 records in ReceiptTypes table :

insert ReceiptTypes select 1, N'Input'

insert ReceiptTypes select 2, N'Output'

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

• Edited by Saturday, October 1, 2016 3:04 PM
Saturday, October 1, 2016 3:02 PM
• Hi Hamed_1983,

According to your description above, you may refer to the following script.

```DECLARE @T TABLE
([ReceiptID] int, [StoreID] int, [ProductID] int, [Quantity] int, [ReceiptTypeID] int)
;

INSERT INTO @T
VALUES
(1, 1, 4201, 50, 1),
(2, 1, 4201, 30, 1),
(3, 1, 4201, 80, 1),
(4, 1, 4201, 20, 2),
(5, 1, 4201, 25, 2),
(6, 1, 4201, 40, 2)
;

;WITH CTE_Output AS
(
SELECT [StoreID], [ProductID], SUM([Quantity]) AS [Quantity_Output]
FROM @T
WHERE [ReceiptTypeID] = 2
GROUP BY [StoreID], [ProductID]
),
CTE_Input AS
(
SELECT *, SUM([Quantity]) OVER(PARTITION BY [StoreID], [ProductID] ORDER BY [ReceiptID]) AS [Quantity_Input] -- require SQL Server 2012 or later version
FROM @T
WHERE [ReceiptTypeID] = 1
)
SELECT i.[ReceiptID]
, i.[StoreID]
, i.[ProductID]
, CASE
WHEN o.[Quantity_Output] IS NULL
OR i.[Quantity_Input] - i.[Quantity] >= o.[Quantity_Output] THEN i.[Quantity]
WHEN i.[Quantity_Input] <= o.[Quantity_Output] THEN 0
ELSE i.[Quantity_Input] - o.[Quantity_Output]
END AS [Available]
FROM CTE_Input i
LEFT JOIN CTE_Output o ON i.[StoreID] = o.[StoreID]
AND i.[ProductID] = o.[ProductID]```

Sam Zha
TechNet Community Support

Monday, October 3, 2016 5:09 AM
• Thanks Sam Zha

base on your sample query, i've changed it according to my situation and the result is :

```WITH CTE_Output AS
(
SELECT r.StoreID, rd.ProductID, SUM(rd.Quantity) AS [Quantity_Output]
FROM Receipts r
JOIN ReceiptDetails rd on r.ReceiptID=rd.ReceiptID
WHERE r.ReceiptTypeID=2 and r.ReceiptStatusID=2
GROUP BY r.StoreID, rd.ProductID
),
CTE_Input AS
(
SELECT r.ReceiptID, r.StoreID, rd.ProductID, rd.Quantity, SUM(rd.Quantity) OVER(PARTITION BY r.StoreID, rd.ProductID ORDER BY r.ReceiptID) AS [Quantity_Input] -- require SQL Server 2012 or later version
FROM Receipts r
join ReceiptDetails rd on r.ReceiptID=rd.ReceiptID
WHERE r.ReceiptTypeID=1 and r.ReceiptStatusID=2
),
CTE_Total AS
(
--select * from CTE_Input
SELECT i.ReceiptID, i.StoreID, i.ProductID,
CASE
WHEN o.Quantity_Output IS NULL OR i.Quantity_Input - i.Quantity >= o.Quantity_Output THEN i.Quantity
WHEN i.Quantity_Input <= o.Quantity_Output THEN 0
ELSE i.Quantity_Input - o.Quantity_Output
END AS [Available]
FROM CTE_Input i
LEFT JOIN CTE_Output o ON i.StoreID = o.StoreID AND i.ProductID = o.ProductID
)
select *
from CTE_Total
where Available<>0
ORDER BY ReceiptID```

Can u provide me how to change my query which run in previous version of sql server ? (sql server 2008 r2)

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

Wednesday, October 5, 2016 8:42 PM
• Hi Hamed_1983,

Script below applies to my previous reply, you may change your code accordingly.

```CTE_Input AS
(
SELECT a.*
, (SELECT SUM(b.[Quantity])
FROM @T b
WHERE b.[ReceiptTypeID] = 1
AND b.[StoreID] = a.[StoreID]
AND b.[ProductID] = a.[ProductID]
AND b.[ReceiptID] <= a.[ReceiptID]) AS [Quantity_Input]
FROM @T a
WHERE a.[ReceiptTypeID] = 1
)```

Sam Zha
TechNet Community Support

• Marked as answer by Thursday, October 6, 2016 5:28 PM
Thursday, October 6, 2016 6:07 AM