您的位置: 首页  >  技术问答  >  产品数量扣除条件

产品数量扣除条件

分类: 技术问答 • 2022-08-26 19:38:33
问题描述：

我有要求扣除条件上的产品数量。这似乎有点复杂，不知道如何使用SQL查询来做到这一点。这里是它的概念：产品在这里意味着原材料。为了生产目的，我们必须从库存中扣除原材料。很少有规律可循：产品数量扣除条件

表 - ProductEntry：

I）产品与PO（采购订单），并从供应商发票号购买。在这种情况下有一个条件。假设，100种产品的产品编号1001已被购买，并进入了两个部分，如下：

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

这里开始游戏。在许多情况下，原材料或产品可能分为多个部分或完全一次，我的意思是假设共有100个部分。

ii）现在购买后，它必须进入商店，并有另一个程序。每个购买的产品应该用IP（进口许可证）数分别被接收如下：

表 - 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

ⅲ）接收产品之后，它应被用于生产目的，这意味着，会有消费。在消费时，应使用首次进入的产品或原材料。这意味着，如果必须扣除产品ID 1001，那么首次输入的数据应该根据'输入日期'扣除，因为它已经输入了最小值。时间在同一天的10:00:00。所以扣除或消费，下面应该发生：

表 - 消费：

标识 - 消费无 - AnotherProductId - 数量

1 - Con-122 - 2 - 10 

3 - Con-122 - 4 - 10

所以最终的输出将被以下内容：

Id - AnotherProductId - Stock - Quantity Used - Remaining Balance 
1 - 2 - 10 - 10 - 100 
2 - 4 - 10 - 10 - 200

我不在这里分享sql查询，因为它应该是不准确和简单的usi NG INNER JOIN和MIN函数返回如下：

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

我不知道该怎么办处理上述情况特别同一产品的不同数量和有点困惑。

这里是为了更好地理解剧本：

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
+0

**注意：**产品的扣除应继续，直到数量变为零。 –

+0

在你的解释中，库存和使用的数量是相同的--10和10？ –

+0

因为两者的使用量和使用后的总量（扣除10），它变成了10和10. –

答

这应该给您预期的结果。请尝试。

;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
+0

我运行您的查询@Rajesh Bhat。它只适用于一个问题。数量仍然固定在我给定的样本上，并且当我再次给出从特定产品的**消费**表中扣除数量的输入时，它不会被扣除'AnotherProductId'。2.顺便说一下，我必须使用'MIN（DateTime）'用于产品消耗。 –

+0

是的，实际上它是从ProductEntry表的第二条记录中扣除的，如果你在消费表中增加了一条记录。将重新查看查询。 –

+0

对我的回答做了一个小修改。这看起来不错。请尝试。 –

答

预计这将涵盖所有情况。

SELECT DISTINCT P.ProductID,P.Quantity,-1 Flag,C.[Quantity] Balance 
INTO #TMP 
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 

DECLARE @Counter INT=1 
WHILE((SELECT TOP 1 1 FROM #TMP WHERE Flag=-1)=1) 
BEGIN 
    UPDATE T SET T.Balance = T.Balance-T.Quantity, 
       T.Quantity = CASE WHEN T.Quantity-T.Balance>=0 THEN T.Quantity-T.Balance ELSE 0 END, 
       T.Flag = CASE WHEN T.Quantity-T.Balance>=0 THEN 0 ELSE 1 END 

    FROM (SELECT ProductId,Quantity,row_number() over (partition by ProductId order by Quantity)RID FROM [ProductEntry])P 
      INNER JOIN [IpEntry] I ON I.ProductID=P.ProductId and [email protected] 
      INNER JOIN (SELECT ProductId,Quantity,Flag,Balance,row_number() over (partition by ProductId order by Quantity)RID FROM #TMP) T ON T.ProductID=P.ProductID and [email protected] 
      INNER JOIN (SELECT [AnotherProductId],SUM([Quantity]) [Quantity] FROM [Consumption] GROUP BY [AnotherProductId])C ON C.AnotherProductId=I.AnotherProductId 

    UPDATE T1 SET Balance=T2.Balance 
    FROM #TMP T1 INNER JOIN #TMP T2 ON T1.ProductId=T2.ProductId 
    WHERE T2.Flag IN (0,1) 
    UPDATE T1 SET Flag= (SELECT T2.Flag FROM #TMP T2 WHERE T1.ProductId=T2.ProductId AND T2.Flag=0) 
    FROM #TMP T1 
    WHERE Flag=0 

    SET @[email protected]+1 
    SELECT * FROM #TMP 
END  
SELECT ProductId,Quantity FROM #TMP --You can add more details by joining with other tables as per your requirement 
drop table #TMP

相关推荐