SmartList Builder: Sales Summary By Quarter

Just recently I was approached by a partner wanting to leverage the info in the Receivables Summary table (RM00104) to build a smart list showing sales summary by quarter. The smart list needed to display the following information:

Customer Number	Year	Q1	Q2	Q3	Q4
AARONFIT0001	2013	0.00	4224.67	0.00	10277.37
AARONFIT0001	2014	21468.68	0.00	0.00	0.00
AARONFIT0001	2016	12164.15	0.00	0.00	0.00
AARONFIT0001	2017	4945.70	5809.40	0.00	0.00
AARONFIT0001	2018	0.00	0.00	877.50	0.00

As is customary with these types of request, the best bet is to create a SQL Server view that can then be leveraged from Smartlist Builder. By using a SQL Server view, we can leverage some cool T-SQL set-based data manipulation capabilities.

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT custnmbr as [Customer Number], year1 as [Year], isnull([1], 0.00) as Q1, isnull([2], 0.00) as Q2, isnull([3], 0.00) as Q3, isnull([4], 0.00) as Q4
FROM (
  SELECT custnmbr, year1, datepart(qq, datefromparts(year1, periodid, 1)) as qtr, smrysals 
  FROM RM00104
  WHERE histtype = 1
) p
PIVOT 
( SUM (smrysals) FOR qtr IN ([1], [2], [3], [4])
) AS pvt
GO

The above query, produces the results required, but what makes it happen is the beauty of the PIVOT operator. In addition, if you are using SQL Server 2012, you can take advantage of the DATEFROMPARTS function to simplify the conversion of the date parts (year1 and periodid) to a full date to then calculate the quarter with the DATEPART function.

If you are using SQL Server 2008 or earlier, the following query should do:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
SELECT custnmbr as [Customer Number], year1 as [Year], isnull([1], 0.00) as Q1, isnull([2], 0.00) as Q2, isnull([3], 0.00) as Q3, isnull([4], 0.00) as Q4
FROM  (
  SELECT custnmbr, year1, datepart(qq, CAST(CAST(year1 AS varchar) + '-' + CAST(periodid AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME)) as qtr, smrysals 
  FROM RM00104
  WHERE histtype = 1
) p
PIVOT 
( SUM (smrysals) 
  FOR qtr IN ([1], [2], [3], [4])
) as pvt
GO

Note that the above version of the query uses the CAST function to determine the date.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/