I've a requirement to deduct product quantity on a condition. It seems little bit complicated and not sure how to do it using sql query. Here is the concept on it: Product means here raw material. For production purpose, we have to deduct raw materials from stock. There are few rules to follow:
Table - ProductEntry:
i) Products are purchased with PO (Purchase order) and invoice number from the supplier. There is a condition in this case. Suppose, 100 products for product id 1001 has been purchased and it came into two sections as follows:
Id - ProductId - PO - Invoice no - Quantity - Price - EntryDate
1st section: 1 - 1001 - PO-102 - Inv-122 - 20 - 200 - 2017-07-10 10:00:00
2nd section: 2 - 1001 - PO-102 - Inv-122 - 80 - 800 - 2017-07-10 11:00:00
3 - 1002 - PO-102 - Inv-122 - 20 - 400 - 2017-07-10 10:00:00
Here starts the game. In many cases, the raw material or product may come into multiple sections or fully at a time I mean suppose 100 pieces total.
ii) Now after it has been purchased, it has to get into the store and there is another procedure. Each purchased product should be received with a IP (import permit) number separately as follows:
Table - IpEntry:
Id - ProductId - Invoice no - IP - AnotherProductId
1 - 1001 - Inv-122 - IP2244 - 2
2 - 1001 - Inv-122 - IP2244 - 2
3 - 1002 - Inv-122 - IP2244 - 4
iii) After receiving the products, it should be used for production purpose, that means, there will be consumption. In consumption, the first entered product or raw material should be used. That means, if product id 1001 has to be deducted, then the first entered should be deducted based on 'EntryDate' as it has entered at the min. time 10:00:00 on the same date. So for deduction or consumption, following should take place:
Table - Consumption:
Id - Consumption no - AnotherProductId - Quantity
1 - Con-122 - 2 - 10
3 - Con-122 - 4 - 10
So the final output will be the following:
Id - AnotherProductId - Stock - Quantity Used - Remaining Balance
1 - 2 - 10 - 10 - 100
2 - 4 - 10 - 10 - 200
I am not sharing the sql query here as it is supposed to be not accurate and simple enough using INNER JOIN and MIN functions that returns the following:
Id - AnotherProductId - Stock - Quantity Used - Remaining Balance
1 - 2 - 10 - 10 - 100
2 - 2 - 10 - 10 - 100 //It returns **AnotherProductId or ProductId - 1001 or 2** twice as it should only return once
3 - 4 - 10 - 10 - 200
I am not sure how to do deal with the above scenario specifically same product with different quantity and little bit confused.
Here is the script for better understanding:
USE [Demo]
GO
/****** Object: Table [dbo].[ProductEntry] Script Date: 07/19/2017 20:37:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductEntry](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [int] NULL,
[PO] [nvarchar](60) NULL,
[Invoice No] [nvarchar](60) NULL,
[Quantity] [float] NULL,
[Price] [float] NULL,
[EntryDate] [datetime] NULL,
CONSTRAINT [PK_ProductEntry] PRIMARY KEY CLUSTERED
(
[Id] 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 IDENTITY_INSERT [dbo].[ProductEntry] ON
INSERT [dbo].[ProductEntry] ([Id], [ProductId], [PO], [Invoice No], [Quantity], [Price], [EntryDate]) VALUES (1, 1001, N'PO-102', N'Inv-122', 20, 200, CAST(0x0000A7AC00A4CB80 AS DateTime))
INSERT [dbo].[ProductEntry] ([Id], [ProductId], [PO], [Invoice No], [Quantity], [Price], [EntryDate]) VALUES (2, 1001, N'PO-102', N'Inv-122', 80, 800, CAST(0x0000A7AC00B54640 AS DateTime))
INSERT [dbo].[ProductEntry] ([Id], [ProductId], [PO], [Invoice No], [Quantity], [Price], [EntryDate]) VALUES (3, 1002, N'PO-102', N'Inv-122', 20, 400, CAST(0x0000A7AC00A4CB80 AS DateTime))
SET IDENTITY_INSERT [dbo].[ProductEntry] OFF
/****** Object: Table [dbo].[IpEntry] Script Date: 07/19/2017 20:37:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IpEntry](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [int] NULL,
[Invoice No] [nvarchar](60) NULL,
[IP] [nvarchar](60) NULL,
[AnotherProductId] [int] NULL,
CONSTRAINT [PK_IpEntry] PRIMARY KEY CLUSTERED
(
[Id] 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 IDENTITY_INSERT [dbo].[IpEntry] ON
INSERT [dbo].[IpEntry] ([Id], [ProductId], [Invoice No], [IP], [AnotherProductId]) VALUES (1, 1001, N'Inv-122', N'IP2244', 2)
INSERT [dbo].[IpEntry] ([Id], [ProductId], [Invoice No], [IP], [AnotherProductId]) VALUES (2, 1001, N'Inv-122', N'IP2244', 2)
INSERT [dbo].[IpEntry] ([Id], [ProductId], [Invoice No], [IP], [AnotherProductId]) VALUES (3, 1002, N'Inv-122', N'IP2244', 4)
SET IDENTITY_INSERT [dbo].[IpEntry] OFF
/****** Object: Table [dbo].[Consumption] Script Date: 07/19/2017 20:37:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Consumption](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Consumption no] [nvarchar](40) NULL,
[AnotherProductId] [int] NULL,
[Quantity] [float] NULL,
CONSTRAINT [PK_Consumption] PRIMARY KEY CLUSTERED
(
[Id] 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 IDENTITY_INSERT [dbo].[Consumption] ON
INSERT [dbo].[Consumption] ([Id], [Consumption no], [AnotherProductId], [Quantity]) VALUES (1, N'Con-122 ', 2, 10)
INSERT [dbo].[Consumption] ([Id], [Consumption no], [AnotherProductId], [Quantity]) VALUES (2, N'Con-122 ', 4, 10)
SET IDENTITY_INSERT [dbo].[Consumption] OFF
This should give you expected result. Please try.
;WITH CTE AS (
select DISTINCT ProductID,AnotherProductId,Balance,
CASE WHEN Balance>=0 THEN 'P' ELSE 'N' END Flag, row_number() over(partition by AnotherProductId order by Balance) RID
FROM (SELECT DISTINCT P.ProductID,I.AnotherProductId,(P.Quantity-C.Quantity) 'Balance'
FROM [ProductEntry] P INNER JOIN [IpEntry] I ON I.ProductID=P.ProductId
INNER JOIN (SELECT [AnotherProductId],SUM([Quantity]) [Quantity] FROM [Consumption] GROUP BY [AnotherProductId]) C ON C.AnotherProductId=I.AnotherProductId
)A
)
select T.AnotherProductId,Balance as Stock, C.Quantity as 'Quantity Used',MIN((P.Price *(P.Quantity-C.Quantity)/P.Quantity)) 'Remaining Balance'
FROM [ProductEntry] P INNER JOIN CTE T ON T.ProductID=P.ProductId AND (RID=1 OR Flag='N')
INNER JOIN (SELECT DISTINCT ProductId,AnotherProductId FROM [IpEntry]) I ON I.ProductID=P.ProductId
INNER JOIN (SELECT [AnotherProductId],SUM([Quantity]) [Quantity] FROM [Consumption] GROUP BY [AnotherProductId]) C ON C.AnotherProductId=I.AnotherProductId
GROUP BY T.AnotherProductId,Balance, C.Quantity