Поворот поля на основе диапазона дат в таблице B, который >= диапазон дат в таблице A

Question 1

Я пытаюсь преобразовать строку в столбцы, основанные на отформатированных датах "гггг-МММ", и хотя это работает, как и ожидалось, в течение текущего месяца, это не работает в течение будущих месяцев, и я не могу понять, как это исправить, и был бы очень признателен за любые отзывы по этому поводу.

Вот немного предыстории: Я приобрел количество, поступающее из сводной таблицы поставщика, которая включает информацию о контракте, такую как тип контракта, количество, номер партии, дата создания, отформатированная в "гггг-МММ", поскольку фактическая дата для этой цели не имеет значения.

Оставшееся количество-это еще одно поле, взятое из таблицы операций книги товаров, которая, по сути, суммирует все виды операций на основе номера партии.

Потребленное количество-это то, что я пытаюсь свернуть на основе месяца, в котором оно было израсходовано, а также из записей книги товаров с фильтром типа записи

SELECT * 
FROM 
(
SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No', VS.[Date] AS 'Date', 
SUM(VS.Quantity) AS 'Contracted_Quantity',
SUM(CQ.Consumed_Qty*-1) AS 'Consumed_Qty',
SUM(RQ.Remaining_Qty) AS Remaining_Qty,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END 
FROM
(SELECT [Document No_],[Vendor No_],[Lot No_],FORMAT([Date Created], 'yyyy-MMM') AS 'Date'
      ,[Purch_ Contract No_],[Contract Type],[Quantity] FROM [A].[dbo].[Company$Volume Summary]) VS

/*to identify remaining quantity by lot (Lot no. is included in my VS alias statement)*/
LEFT JOIN (SELECT [Lot No_] ,SUM([Remaining Quantity]) AS Remaining_Qty FROM [A].[dbo].[Company$Item Ledger Entry]
  GROUP BY [Lot No_]) RQ on RQ.[Lot No_] = VS.[Lot No_]

/*to identify consumed volume, if consumption is in future month compared to purchase month, it doesn't populate, this is where I believe the problem is*/
LEFT JOIN (SELECT [Lot No_],FORMAT([Posting Date], 'yyyy-MMM') AS 'Date',SUM([Quantity]) AS Consumed_Qty
FROM [A].[dbo].[Company$Item Ledger Entry]
WHERE [Entry Type] = '5' 
GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')) CQ on CQ.[Lot No_] = VS.[Lot No_] and CQ.Date >= VS.[Date]

GROUP BY VS.[Vendor Name], FORMAT(VS.[Date Created],'yyyy-MMM'), PT.[Contract Type], VS.[Vendor No_]
) cs 
PIVOT
(
 SUM(Consumed_Qty)
  for Date in ([2021-Sep], [2021-Oct], [2021-Nov])
  ) pvt

РЕЗУЛЬТАТ (отображается только частично):

Как вы можете видеть, вторая запись технологии ABC показывает потребление в 2021-октябре, что является точным, однако, исходя из этой контрактной суммы, купленной также в 2021-октябре, и того, что осталось в этой записи, потребление должно составлять 9 373 и меньше (383) по сравнению с 8 990, которые он точно отображает в 2021-октябре. В этом случае фактическое потребление 383 падает в 2021-ноябре, однако мой код, похоже, этого не улавливает.

Это также относится к обоим записям Zebra technologies, где оставшееся значение равно 0, что означает, что оно было полностью израсходовано, однако, поскольку даты контрактов на покупку для них обоих указаны в 2021-сентябре, а потребление-в будущем месяце 2021-октябрь, сценарий SQL этого не учитывает.

Я был бы признателен за любые рекомендации, которые помогут это исправить, и заранее благодарю.

Question 2

Вы поворачиваетесь на основе VS.[Дата], то есть когда Лот был куплен, а не когда он был израсходован. В последних трех столбцах будут отображаться только цифры, когда товары были куплены И потреблены в ОДНОМ и том же месяце.

Вместо поворота всего набора данных переместите поворот в третий подзапрос. Это упрощает запрос, получая сначала сведения о лоте, включая контракт_qty и оставшийся_qty, а затем присоединяясь к третьему подзапросу, который представляет собой сводный набор потребляемых_qty по лотам за три месяца.

SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No',
VS.[Contracted_Quantity],
RQ.Remaining_Qty AS Remaining_Quantity,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END,
CQ.[2021-Sep], CQ.[2021-Oct], CQ.[2021-Nov]

FROM
    (
    /* List of Lots with details */
    SELECT 
        [Document No_],[Vendor No_],[Lot No_], [Purch_ Contract No_],[Contract Type],[Quantity] AS [Contracted_Quantity]
    FROM [A].[dbo].[Company$Volume Summary]
    ) VS

    /* Remaining Quantity for each Lot */
    LEFT JOIN (
        SELECT [Lot No_] , SUM([Remaining Quantity]) AS Remaining_Quantity 
        FROM [A].[dbo].[Company$Item Ledger Entry]
        GROUP BY [Lot No_]
        ) RQ on RQ.[Lot No_] = VS.[Lot No_]

    /* Consumed Quantity per Lot for three months*/
    LEFT JOIN (

        SELECT [Lot No_], [2021-Sep], [2021-Oct], [2021-Nov]
        FROM 
        (
        SELECT [Lot No_], FORMAT([Posting Date], 'yyyy-MMM') AS 'Date', SUM([Quantity]) AS Consumed_Qty
        FROM [A].[dbo].[Company$Item Ledger Entry]
        WHERE [Entry Type] = '5' 
        GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')
        ) AS src
        PIVOT
        (
         SUM(Consumed_Qty)
          for [Date] in ([2021-Sep], [2021-Oct], [2021-Nov])
          ) pvt

        ) CQ on CQ.[Lot No_] = VS.[Lot No_]
;

Я не могу проверить этот запрос без данных, но он должен быть очень близким.

RobertT · Answer 1 · 2021-11-23T20:47:23

Вы поворачиваетесь на основе VS.[Дата], то есть когда Лот был куплен, а не когда он был израсходован. В последних трех столбцах будут отображаться только цифры, когда товары были куплены И потреблены в ОДНОМ и том же месяце.

Вместо поворота всего набора данных переместите поворот в третий подзапрос. Это упрощает запрос, получая сначала сведения о лоте, включая контракт_qty и оставшийся_qty, а затем присоединяясь к третьему подзапросу, который представляет собой сводный набор потребляемых_qty по лотам за три месяца.

SELECT VS.[Vendor Name], VS.[Vendor No_] AS 'Vendor_No',
VS.[Contracted_Quantity],
RQ.Remaining_Qty AS Remaining_Quantity,
'Contract Type' = 
CASE 
  WHEN VS.[Contract Type] = 1 THEN 'CONTRACT A'
  WHEN VS.[Contract Type] = 2 THEN 'CONTRACT B'
  ELSE 'OTHERS'
  END,
CQ.[2021-Sep], CQ.[2021-Oct], CQ.[2021-Nov]

FROM
    (
    /* List of Lots with details */
    SELECT 
        [Document No_],[Vendor No_],[Lot No_], [Purch_ Contract No_],[Contract Type],[Quantity] AS [Contracted_Quantity]
    FROM [A].[dbo].[Company$Volume Summary]
    ) VS

    /* Remaining Quantity for each Lot */
    LEFT JOIN (
        SELECT [Lot No_] , SUM([Remaining Quantity]) AS Remaining_Quantity 
        FROM [A].[dbo].[Company$Item Ledger Entry]
        GROUP BY [Lot No_]
        ) RQ on RQ.[Lot No_] = VS.[Lot No_]

    /* Consumed Quantity per Lot for three months*/
    LEFT JOIN (

        SELECT [Lot No_], [2021-Sep], [2021-Oct], [2021-Nov]
        FROM 
        (
        SELECT [Lot No_], FORMAT([Posting Date], 'yyyy-MMM') AS 'Date', SUM([Quantity]) AS Consumed_Qty
        FROM [A].[dbo].[Company$Item Ledger Entry]
        WHERE [Entry Type] = '5' 
        GROUP BY [Lot No_], Format([Posting Date], 'yyyy-MMM')
        ) AS src
        PIVOT
        (
         SUM(Consumed_Qty)
          for [Date] in ([2021-Sep], [2021-Oct], [2021-Nov])
          ) pvt

        ) CQ on CQ.[Lot No_] = VS.[Lot No_]
;

Я не могу проверить этот запрос без данных, но он должен быть очень близким.

Спасибо за ваш ответ. Причина, по которой я использую СУММУ(оставшуюся), заключается в том, что таблица операций книги товаров содержит несколько типов операций: покупка, корректировки, потребление. Сумма всех типов записей для каждого номера лота-это то, что позволяет мне получить оставшееся количество, и это действительно связано с моей книгой учета запасов партии. однако, когда я теперь использую CQ.Дата в сводке и удалите соединение >= дата в CQ, теперь оно дублирует запись о покупке за каждый месяц потребления, что делает общую сумму покупок по лотам неправильной. какие еще данные я мог бы предоставить, чтобы лучше объяснить свою проблему? очень ценю ваше время и помощь в этом
Следующая корректировка будет заключаться в перемещении оси в третий подзапрос. Вместо того, чтобы поворачивать весь набор данных, получите сведения о лоте, включая Contracted_Qty и Remaining_Qty, а затем присоединитесь к третьему подзапросу, который представляет собой свернутый набор Consumed_Qty по лотам за три месяца.
Мне все еще любопытно, как вы можете получить оставшееся количество, суммируя какое-то число. Можете ли вы предоставить некоторые примеры данных для демонстрации?
Не обращайте внимания на оставшееся количество. Я только что понял, что в вашей бухгалтерской таблице каждое число должно быть записано в двух столбцах. Оставшееся количество является положительным или отрицательным числом в зависимости от типа записи. Суммирование этого даст оставшееся количество. Название колонки сбило меня с толку.
Да, это верно! Именно так в таблице главной книги записывается каждая запись.
это хорошая идея! Я попробую переместить сводную таблицу в подзапрос, хотя вы были правы насчет первой части, у меня была сводная таблица, основанная на дате покупки, а не на дате потребления, и затем я сгруппировал ее по номеру лота. что, казалось, решило проблему. но мне нравится идея переместить стержень в третий подзапрос, буду держать вас в курсе! Спасибо
Ответ обновлен на основе отзывов в комментариях

Поворот поля на основе диапазона дат в таблице B, который >= диапазон дат в таблице A

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории