locked
How to subtract output products from input products base on receipts respectively? RRS feed

  • 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?

    Thanks in advance


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

    Friday, September 30, 2016 3:28 PM

Answers

  • 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 Hamed_1983 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".

    Please see:

    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

    your link is very useful for me, but i think it's not my exact answer.

    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,
    	[ProductionDateMiladi] [datetime] NULL,
    	[ExpirationDateMiladi] [datetime] 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
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[Receipts]    Script Date: 10/1/2016 6:27:58 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    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
    SET ANSI_PADDING OFF
    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])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    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])
    ON UPDATE CASCADE
    GO

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

    insert ReceiptTypes select 1, N'Input'

    insert ReceiptTypes select 2, N'Output'

    Thanks in advcance.


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


    • Edited by Hamed_1983 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)

    thanks in advance


    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 Hamed_1983 Thursday, October 6, 2016 5:28 PM
    Thursday, October 6, 2016 6:07 AM
  • Thanks Sam Zha for your reply.

    it works!!


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

    Thursday, October 6, 2016 5:29 PM