Блог

Главная - Вопросы по программированию - Как связать один и тот же код при выполнении SUM()?

Как связать один и тот же код при выполнении SUM()?

#window-functions #cumulative-sum #sql-server-2019 #inventory-management

Вопрос:

Я выполняю SUM() НАД(РАЗДЕЛЕНИЕМ ПО ПОРЯДКУ СТРОК МЕЖДУ НЕОГРАНИЧЕННОЙ ПРЕДЫДУЩЕЙ И ТЕКУЩЕЙ СТРОКАМИ)

Использование SQL Server 2019

У меня есть 2 таблицы: Продажи и запасы.

Я пытаюсь связать номера запасов (DWF_INVENTORY_NUMBER_DESC), которые представляют партии количеств товаров в запасах (DWF_INVENTORY_QUANTITY) с товарами и их счетами-фактурами. Каждый товар (DWF_SALES_ITEM_CODE), на который выставляется счет в продажах с определенным количеством продаж (DWF_SALES_SALES_QTY), должен представлять покупку (DWF_SALES_INVOICE_NUM), а затем должен быть извлечен из запасов (DWF_INVENTORY_QUANTITY уменьшен). У меня есть несколько счетов-фактур на один и тот же товар. У меня также есть несколько партий инвентаря с разными номерами партий инвентаря с их собственными соответствующими количествами и их товарами в инвентаре. Мне нужно иметь возможность использовать стратегию «первый вход-первый выход» (FIFO), когда я извлекаю товар из инвентаря. Другими словами, мне нужно иметь возможность извлекать только один инвентарный номер (партию) за один раз с самой ранней датой инвентаризации (что означает 1-е место для DWF_INVENTORY_DELIVERY_DATE). Однако в моем сценарии это выглядит так, как будто я извлекаю из нескольких инвентарных номеров (партий) один и тот же товар, чего я не хочу, чтобы это произошло.

Мои текущие и ожидаемые результаты следующие (обратите внимание, что моя сокращающаяся текущая сумма DWF_INVENTORY_RUNNING_BALANCE_QTY работает хорошо).:

введите описание изображения здесь

Я чувствую, что я очень близок, но мне просто нужен этот последний совет, чтобы привести свои результаты в порядок. Условие должно заключаться в том, что самая старая дата поставки (которая также старше даты выставления счета) с соответствующим номером партии должна быть связана с соответствующей номенклатурой и связанной с ней накладной. Как только начальная партия запасов из 5000 единиц кол-во достигнет нуля, мы сможем связать следующий номер партии запасов (например, DWF_INVENTORY_NUMBER_DESC равен 13763002028961), когда в будущем появятся новые проводки по накладным.

Ниже приведены сценарии, позволяющие воссоздать весь сценарий.

Создайте таблицы продаж и запасов с данными:

 CREATE TABLE [dbo].[DWF_INVENTORY](
[DWF_INVENTORY_ITEM_CD] [varchar](50) NULL,
[DWF_INVENTORY_DELIVERY_DATE] [varchar](50) NULL,
[DWF_INVENTORY_NUMBER_DESC] [varchar](50) NULL,
[DWF_INVENTORY_QUANTITY] [int] NULL,
[DWF_INVENTORY_ACCUMULATED_QTY] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DWF_SALES]    Script Date: 8/25/2021 12:13:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DWF_SALES](
    [DWF_SALES_INVOICE_NUM] [int] NOT NULL,
    [DWF_SALES_INVOICE_DATE] [datetime2](7) NOT NULL,
    [DWF_SALES_ITEM_CODE] [nvarchar](50) NOT NULL,
    [DWF_SALES_SALES_QTY] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[DWF_INVENTORY] ([DWF_INVENTORY_ITEM_CD], [DWF_INVENTORY_DELIVERY_DATE], [DWF_INVENTORY_NUMBER_DESC], [DWF_INVENTORY_QUANTITY], [DWF_INVENTORY_ACCUMULATED_QTY]) VALUES (N'TU-1055', N'2019-10-31', N'13763001924657', 5000, 5000)
GO
INSERT [dbo].[DWF_INVENTORY] ([DWF_INVENTORY_ITEM_CD], [DWF_INVENTORY_DELIVERY_DATE], [DWF_INVENTORY_NUMBER_DESC], [DWF_INVENTORY_QUANTITY], [DWF_INVENTORY_ACCUMULATED_QTY]) VALUES (N'TU-1055', N'2020-11-20', N'13763002028961', 5000, 10000)
GO
INSERT [dbo].[DWF_INVENTORY] ([DWF_INVENTORY_ITEM_CD], [DWF_INVENTORY_DELIVERY_DATE], [DWF_INVENTORY_NUMBER_DESC], [DWF_INVENTORY_QUANTITY], [DWF_INVENTORY_ACCUMULATED_QTY]) VALUES (N'TU-1055', N'2021-01-08', N'13763002038565', 5000, 15000)
GO
INSERT [dbo].[DWF_SALES] ([DWF_SALES_INVOICE_NUM], [DWF_SALES_INVOICE_DATE], [DWF_SALES_ITEM_CODE], [DWF_SALES_SALES_QTY]) VALUES (395395, CAST(N'2021-05-13T00:00:00.0000000' AS DateTime2), N'TU-1055', 4)
GO
INSERT [dbo].[DWF_SALES] ([DWF_SALES_INVOICE_NUM], [DWF_SALES_INVOICE_DATE], [DWF_SALES_ITEM_CODE], [DWF_SALES_SALES_QTY]) VALUES (411239, CAST(N'2021-07-26T00:00:00.0000000' AS DateTime2), N'TU-1055', 100)
GO
INSERT [dbo].[DWF_SALES] ([DWF_SALES_INVOICE_NUM], [DWF_SALES_INVOICE_DATE], [DWF_SALES_ITEM_CODE], [DWF_SALES_SALES_QTY]) VALUES (378789, CAST(N'2021-02-23T00:00:00.0000000' AS DateTime2), N'TU-1055', 100)
GO
INSERT [dbo].[DWF_SALES] ([DWF_SALES_INVOICE_NUM], [DWF_SALES_INVOICE_DATE], [DWF_SALES_ITEM_CODE], [DWF_SALES_SALES_QTY]) VALUES (313564, CAST(N'2020-02-05T00:00:00.0000000' AS DateTime2), N'TU-1055', 30)
GO
INSERT [dbo].[DWF_SALES] ([DWF_SALES_INVOICE_NUM], [DWF_SALES_INVOICE_DATE], [DWF_SALES_ITEM_CODE], [DWF_SALES_SALES_QTY]) VALUES (327469, CAST(N'2020-05-04T00:00:00.0000000' AS DateTime2), N'TU-1055', 350)
GO

Вот мой сценарий с текущими результатами:

 SELECT
DWF_SALES_INVOICE_NUM,DWF_SALES_INVOICE_DATE
,DWF_INVENTORY_NUMBER_DESC
,DWF_INVENTORY_DELIVERY_DATE
,INVENTORY_NUM_STATUS
,DWF_INVENTORY_ITEM_CD
,CASE
    WHEN DWF_INVENTORY_RUNNING_BALANCE_QTY < 0 THEN 0
    ELSE DWF_INVENTORY_RUNNING_BALANCE_QTY
END AS DWF_INVENTORY_RUNNING_BALANCE_QTY
,DWF_INVENTORY_QUANTITY
,CASE
    WHEN DWF_SALES_RUNNING_BALANCE_QTY < 0 THEN 0
    ELSE DWF_SALES_RUNNING_BALANCE_QTY
END AS DWF_SALES_RUNNING_BALANCE_QTY
,DWF_SALES_SALES_QTY
FROM (
SELECT
    DWF_SALES_INVOICE_NUM,DWF_SALES_INVOICE_DATE
    ,DWF_INVENTORY_NUMBER_DESC
    ,DWF_INVENTORY_DELIVERY_DATE
    ,INVENTORY_NUM_STATUS
    ,DWF_INVENTORY_ITEM_CD
    ,CASE
        WHEN INVENTORY_NUM_STATUS='NOT ALLOCATED' THEN 0
        ELSE (DWF_INVENTORY_QUANTITY-
                    SUM(DWF_SALES_SALES_QTY_forInvRunnBlnCalculate) OVER(PARTITION BY DWF_INVENTORY_ITEM_CD ORDER BY DWF_INVENTORY_DELIVERY_DATE,DWF_SALES_INVOICE_DATE
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
    END AS DWF_INVENTORY_RUNNING_BALANCE_QTY
    ,DWF_INVENTORY_QUANTITY
    ,CASE
        WHEN INVENTORY_NUM_STATUS='NOT ALLOCATED' THEN (DWF_INVENTORY_QUANTITY-DWF_SALES_SALES_QTY)
        ELSE 0
    END AS DWF_SALES_RUNNING_BALANCE_QTY
    ,DWF_SALES_SALES_QTY
FROM (
    SELECT b.DWF_SALES_INVOICE_NUM,b.DWF_SALES_INVOICE_DATE
    ,a.DWF_INVENTORY_NUMBER_DESC
    ,a.DWF_INVENTORY_DELIVERY_DATE
    ,CASE
        WHEN (a.DWF_INVENTORY_DELIVERY_DATE > b.DWF_SALES_INVOICE_DATE) 
            AND (a.DWF_INVENTORY_QUANTITY - SUM(b.DWF_SALES_SALES_QTY) OVER(PARTITION BY a.DWF_INVENTORY_ITEM_CD ORDER BY a.DWF_INVENTORY_DELIVERY_DATE,b.DWF_SALES_INVOICE_DATE 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) < 1
            THEN 'NOT ALLOCATED'
        ELSE ''
    END AS INVENTORY_NUM_STATUS
    ,CASE
        WHEN (a.DWF_INVENTORY_DELIVERY_DATE > b.DWF_SALES_INVOICE_DATE) 
            AND (a.DWF_INVENTORY_QUANTITY - SUM(b.DWF_SALES_SALES_QTY) OVER(PARTITION BY a.DWF_INVENTORY_ITEM_CD ORDER BY a.DWF_INVENTORY_DELIVERY_DATE,b.DWF_SALES_INVOICE_DATE 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) < 0
            THEN 0
        ELSE DWF_SALES_SALES_QTY
    END AS DWF_SALES_SALES_QTY_forInvRunnBlnCalculate
    ,a.DWF_INVENTORY_ITEM_CD
    ,a.DWF_INVENTORY_QUANTITY - SUM(b.DWF_SALES_SALES_QTY) OVER(PARTITION BY a.DWF_INVENTORY_ITEM_CD ORDER BY a.DWF_INVENTORY_DELIVERY_DATE,b.DWF_SALES_INVOICE_DATE 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DWF_INVENTORY_RUNNING_BALANCE_QTY
    ,a.DWF_INVENTORY_QUANTITY
    ,b.DWF_SALES_SALES_QTY - SUM(a.DWF_INVENTORY_QUANTITY) OVER(PARTITION BY a.DWF_INVENTORY_ITEM_CD ORDER BY a.DWF_INVENTORY_DELIVERY_DATE,b.DWF_SALES_INVOICE_DATE 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DWF_SALES_RUNNING_BALANCE_QTY
    ,b.DWF_SALES_SALES_QTY
    ,ROW_NUMBER() OVER (PARTITION BY a.DWF_INVENTORY_ITEM_CD ORDER BY a.DWF_INVENTORY_DELIVERY_DATE,b.DWF_SALES_INVOICE_DATE) AS rowNum
    FROM [dbo].[DWF_INVENTORY] AS a
    INNER JOIN [dbo].[DWF_SALES] AS b ON a.DWF_INVENTORY_ITEM_CD=b.DWF_SALES_ITEM_CODE
    WHERE b.DWF_SALES_SALES_QTY >= 0
) AS tblA) AS tblB;

Я знаю, что мне нужно создать какую-то условную логику для моего столбца DWF_INVENTORY_NUMBER_DESC или, возможно, создать подзапрос, CTE или временную таблицу для хранения MIN(DWF_INVENTORY_DELIVERY_DATE), но я точно не знаю, как действовать.

Краткое изложение условий для сопоставления одного и того же инвентарного номера в моих результирующих записях, когда …

 ... the DWF_INVENTORY_NUMBER_DESC is associated to the good item (DWF_INVENTORY_ITEM_CODE = DWF_SALES_ITEM_CODE)
... the DWF_INVENTORY_DELIVERY_DATE is the oldest/earliest
... the DWF_INVENTORY_DELIVERY_DATE < DWF_SALES_INVOICE_DATE
... the DWF_INVENTORY_RUNNING_BALANCE_QTY > 0

move on to next DWF_INVENTORY_NUMBER_DESC, when ...
... DWF_INVENTORY_RUNNING_BALANCE_QTY reaches zero

затем снова выполните описанные выше условия.

Надеюсь, теперь это имеет больше смысла, чтобы иметь возможность повторно использовать тот же DWF_INVENTORY_NUMBER_DESC для записей в результирующем сценарии?

Буду признателен за любую помощь в завершении моего сценария с уменьшенной суммой выполнения.

Надеюсь, все мои объяснения выше были достаточно ясны, так как это не очень простая проблема!

Ответ №1:

Итак, я решил свою собственную проблему, записав во временную таблицу и сохранив инвентарный номер, где MIN(Дата доставки), который обновляется соответствующим образом.

Метки: