запрос B1 | Ежемесячные продажи консолидированы за разные периоды времени (например, 1 / 3 / 6 / 12 месяцев)

действительно борюсь с этим. Ищете способ запросить объемы продаж за разные промежутки времени, чтобы все это было помещено в одну таблицу.

Предположим, что это 13 августа. Затем я хочу суммировать объемы продаж для:

  • 1 июля 00:00:00 - 31 июля 11:59:59 (последний 1 месяц)
  • 00:00:00 1 мая - 11:59:59 31 июля (последние 3 месяца)
  • 00:00:00 1 февраля - 11:59:59 31 июля (последние 6 месяцев)
  • 00:00:00 1 августа - 11:59:59 31 июля (последние 12 месяцев)

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

Я подумал, что мне нужно получить 4 отдельных запроса, которые затем нужно объединить, чтобы получить все столбцы в одну таблицу:

ItemName ItemCode Sal.vol 1 mth Sal.vol 3 mths Sal.vol 6 mths Sal.vol 12 mths
Item 1 10102251 10 30 60 120
Item 2 10120101 14 35 78 181

Мой прогресс на данный момент:

SELECT
  T1.itemcode,  month(T1.[docDate]) as month, T3.ItemName,
  SUM(t1.quantity )
FROM INV1 T1 INNER JOIN OINV T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T1.[docDate] between DATEADD(month, -4, GETDATE()) and DATEADD(month, -1, GETDATE()) AND T1.ItemCode LIKE '1%' AND T3.ItmsGrpCod = 100
Group By  T1.itemcode, T3.ItemName, month(T1.[docDate])

Для моих дат я хотел построить их следующим образом:

DECLARE @date date = month(DATEADD(month, -1, GETDATE())) + '-01-' + year(DATEADD(month, -1, GETDATE()));
DECLARE @datetime datetime = @date;     
SELECT @datetime AS '@datetime'

Однако мне это еще не удалось, так как у меня проблемы с преобразованием int в дату, и я думаю, что месяц () для января-сентября не будет работать, поскольку он возвращает только 1 цифру...

Может ли кто-нибудь помочь мне указать правильное направление?

Заранее спасибо!

Обновление 1:

@imord указал мне правильное направление. Я смог построить свой запрос по желанию. Делюсь своим промежуточным результатом, пока это работает. Запрос нуждается в некоторой очистке и оптимизации, а повторяющаяся основная часть передается во вторую функцию.

create function MonthFirst (@period integer ) returns date
as begin
declare @returnperiod date 
set @returnperiod = cast(cast(year(DATEADD(month, @period, GETDATE())) as varchar(4)) + '-' +
    cast(month(DATEADD(month, @period, GETDATE())) as varchar(2)) + '-01' as date)
 return @returnperiod
end
-- drop function dbo.MonthFirst

SELECT S12.ItemCode, o1.ItemName, S12.[last 12 months], S6.[last 6 months], S3.[last 3 months], S1.[last month] FROM 
(SELECT
   T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last 12 months'
FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T1.[docDate] between CAST(dbo.MonthFirst(-12) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
      AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
      AND T2.CANCELED = 'N'
Group By T1.itemcode) S12

FULL JOIN 

(SELECT
   T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last 6 months'
FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T1.[docDate] between CAST(dbo.MonthFirst(-6) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
      AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
      AND T2.CANCELED = 'N'
Group By T1.itemcode) S6 on S12.ItemCode = S6.ItemCode

FULL JOIN 

(SELECT
   T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last 3 months'
FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T1.[docDate] between CAST(dbo.MonthFirst(-3) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
      AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
      AND T2.CANCELED = 'N'
Group By T1.itemcode) S3 on S12.ItemCode = S3.ItemCode

FULL JOIN 

(SELECT
   T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last month'
FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T1.[docDate] between CAST(dbo.MonthFirst(-1) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
      AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
      AND T2.CANCELED = 'N'
Group By T1.itemcode) S1 on S12.ItemCode = S1.ItemCode

INNER JOIN OITM o1 on S12.ItemCode = o1.ItemCode

ORDER BY o1.ItemName

Обновление 2:

Теперь я объединил оба предложения / подсказки от @imrd и @Gordon Linoff и получил этот небольшой изящный запрос, который в значительной степени соответствует тому, что я хотел.

Большое спасибо!

Вот мое решение:

DECLARE @period integer = -12
DECLARE @startperiod date
DECLARE @endperiod date 
SET @startperiod = CAST(cast(year(DATEADD(month, @period, GETDATE())) AS varchar(4)) + '-' +
    cast(month(DATEADD(month, @period, GETDATE())) AS varchar(2)) + '-01' AS date)
SET @endperiod = CAST(cast(year(DATEADD(month, 0, GETDATE())) AS varchar(4)) + '-' +
    cast(month(DATEADD(month, 0, GETDATE())) AS varchar(2)) + '-01' AS datetime)

SELECT T1.ItemCode, T3.ItemName, 
       sum(case when datediff(month, T1.[docDate], GETDATE()) <= 12 then t1.quantity else 0 end) as month_12,
       sum(case when datediff(month, T1.[docDate], GETDATE()) <= 6 then t1.quantity else 0 end) as month_6,
       sum(case when datediff(month, T1.[docDate], GETDATE()) <= 3 then t1.quantity else 0 end) as month_3,
       sum(case when datediff(month, T1.[docDate], GETDATE()) = 1 then t1.quantity else 0 end) as month_1
FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T1.[docDate] between CAST(@startperiod AS datetime) AND  DATEADD(second, -1, CAST(@endperiod AS datetime)) 
      AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
      AND T2.CANCELED = 'N'
GROUP BY T1.ItemCode, T3.ItemName
ORDER BY T3.ItemName ASC

@Gordon Linoff: «=» должно быть «‹=», потому что я хочу, чтобы объем продаж, например, все последние 12 месяцев, а не объем только за один месяц год назад.

Еще раз спасибо за очень быструю помощь!


sql sapb1
person little_endian    schedule 29.03.2021    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Это выглядит как простой пример условной агрегации:

select itemname, itemcode
       sum(case when datediff(month, date, 1) = 1 then quantity else 0 end) as month_1,
       sum(case when datediff(month, date, 1) <= 3 then quantity else 0 end) as month_3,
       sum(case when datediff(month, date, 1) <= 6 then quantity else 0 end) as month_6,
       sum(case when datediff(month, date, 1) <= 12 then quantity else 0 end) as month_12
from t
group by itemname, itemcode;

Ваш запрос сложнее, чем предполагает ваше описание, но это должно показать вам, как выполнять вычисления, лежащие в основе вопроса.

Обратите внимание, что datediff() подсчитывает количество границ между двумя датами, поэтому идеально подходит для календарных месяцев.

person Gordon Linoff    schedule 29.03.2021
comment
Я только что обновил свой вопрос прямо сейчас с моим промежуточным решением. - person little_endian; 29.03.2021
comment
@little_endian . . . Вам не нужно определять свою собственную функцию для этого. - person Gordon Linoff; 29.03.2021
comment
Пришлось настроить сравнение ('=' / '‹=') - кроме этого очень хорошего предложения - спасибо! - person little_endian; 29.03.2021

arrow_upward
1
arrow_downward

Преобразование требует настройки, чтобы преобразовать все входные данные в один тип данных, а затем преобразовать в тип данных даты -

Для формата даты в американском стиле

declare @date date = cast( cast(month(DATEADD(month, @period, GETDATE())) as varchar(2)) + '-01-' 
        + cast(year(DATEADD(month, @period, GETDATE())) as varchar(4)) as date)

Для формата даты ISO

declare @date date = cast(cast(year(DATEADD(month, -1, GETDATE())) as varchar(4)) + '-' +
    cast(month(DATEADD(month, -1, GETDATE())) as varchar(2))
    + '-01' as date)

Предполагая формат ISO, поскольку мой слабый мозг никогда не понимал, почему Mon DD YYYY удобен для обработки данных, кроме представления

Создайте функцию, которой вы можете передать количество месяцев для отчета, чтобы уменьшить код в вашем основном запросе отчета.

create function MonthFirst (@period integer ) returns date
as begin
declare @returnperiod date 
set @returnperiod = cast(cast(year(DATEADD(month, @period, GETDATE())) as varchar(4)) + '-' +
    cast(month(DATEADD(month, @period, GETDATE())) as varchar(2))
    + '-01' as date)
 return @returnperiod
end

Затем используйте функцию в запросе по мере необходимости (например,)

select [3 Months Ago] = dbo.MonthFirst(-2)  -- 2020-12-01
select [Last Month] = dbo.MonthFirst(-1)  -- 2021-02-01
select [This Month] = dbo.MonthFirst(0)  -- 2021-03-01
select [Next Month] = dbo.MonthFirst(1)  -- 2021-04-01
person irnerd    schedule 29.03.2021
comment
Привет @imord - спасибо за твои мысли; пока выглядит очень многообещающе. Мне просто нужно выяснить, можно ли временно создавать функции в запросах SAP B1. - person little_endian; 29.03.2021