B1 query | Monthly sales consolidated for different time periods (i.e. 1 / 3 / 6 / 12 months)

Question

really struggling with this one. Looking for a way to query sale volumes for different time spans all put into one table.

Let's assume it is Aug 13th. I then want summed up sales volumes for:

Jul 1st 00:00:00 - July 31st 11:59:59 (last 1 month)
May-1st 00:00:00 - July 31st 11:59:59 (last 3 months)
Feb 1st 00:00:00 - July 31st 11:59:59 (last 6 months)
Aug 1st 00:00:00 - July 31st 11:59:59 (last 12 months)

I tried to reuse snippets from the pivot query examples that can be found related to sales volumes but could not succeed. I started over from scratch by first trying to create datetimes which I can use for the time spans but that's already quite fiddly and somehow feels wrong.

I thought that I need to end up with 4 single queries which then need to be joined to get all the columns into one single table:

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

My progress so far:

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])

For my datetimes I wanted to build them as follows:

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

I However did not succeed yet as I am having trouble with int to date conversion and I think the month() for Jan-Sep will not work as it return only 1 digit...

Could anyone help pointing me into the right direction?

Thanks in advance!

Update 1:

@imerd pointed me into the right direction. I was able to build my query as desired. Sharing my intermediate result as this works for now. Query needs some cleaning and optimisation and the recurring main part sourced out into a 2nd function.

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

Update 2:

I now combined both suggestions/hints from @imerd and @Gordon Linoff and got this little nifty query which pretty much exactly does want I wanted.

Thank you very much!

Here's my solution:

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: '=' needed to be '<=' because I want sales volume from e.g. all of the last 12 months and not the volume from just the single month one year ago.

Thanks again for the very quick help!

Gordon Linoff · Accepted Answer · 2021-03-29T13:53:10.923

2

This looks like a simple example of conditional aggregation:

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;

Your query is more complex than your description suggests, but this should show you how to do the calculations at the heart of the question.

Note that datediff() counts the number of boundaries between two dates, so works perfectly for calendar months.

edited Mar 29 '21 at 13:53

answered Mar 29 '21 at 11:51

Gordon Linoff

1,242,037
58
646
786

I just updated my question right now with my intermediate solution. – little_endian Mar 29 '21 at 11:57
@little_endian . . . You don't need to define your own function for this. – Gordon Linoff Mar 29 '21 at 12:44
Had to tweak the comparison ( '=' / '<=') - apart from that very nice suggestion - thanks! – little_endian Mar 29 '21 at 13:50

irnerd · Answer 2 · 2021-03-29T09:18:21.483

The conversion needs tweaking convert all inputs to a single data type to then convert to date data type -

For US style date format

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)

For ISO Date format

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)

Assuming ISO Format as my puney brain has never understood why Mon DD YYYY is practical for data processing other than presentation

Create a function that you can pass "number of months to report" - to reduce the code in your main report query

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

Then use function in query as needed (e.g.)

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

Hey @imerd - thanks for your thoughts; looks very promising so far. I just need to figure out if it is possible to temporarily create functions within SAP B1 queries. — little_endian, Mar 29 '21 at 10:12

B1 query | Monthly sales consolidated for different time periods (i.e. 1 / 3 / 6 / 12 months)

2 Answers2