I am working to recreate some Excel pivots in SQL Server, which is requiring the use of Coalesce functions. Referring to online documentation like the following:
- Row and column total in dynamic pivot
- https://www.codeproject.com/Articles/232181/SQL-Pivot-with-Grand-Total-Column-and-Row
I have been able to create a count total with grand total column and row, along with a dynamic pivot counting open contacts (which is based on the primary table id) by user and workflow.
CREATE PROCEDURE [dbo].[RetrieveTotalOpenContacts]
@team_id int
AS
/* TEAM SPECIFIC PARAMETER */
DECLARE @team_string varchar(1)
SET @team_string = CONVERT(varchar(10), @team_id)
/* COLUMNS HEADERS */
DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders = COALESCE (@columnHeaders
+ ',[' + workflow + ']', '[' + workflow + ']')
FROM contact_workflow
WHERE team_id = @team_id
GROUP BY workflow
ORDER BY workflow
/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (workflow AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(workflow AS VARCHAR)+ '],0) + ')
FROM contact_workflow
WHERE team_id = @team_id
GROUP BY workflow
ORDER BY workflow
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(SUM([' +
CAST(workflow AS VARCHAR)+']),0)', 'ISNULL(SUM([' + CAST(workflow AS VARCHAR)+']),0)')
FROM contact_workflow
WHERE team_id = @team_id
GROUP BY workflow
ORDER BY workflow
/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 'SELECT *, (' + @GrandTotalCol + ')
AS [Grand Total] INTO #temp_MatchesTotal
FROM
(SELECT
o.full_name AS [User],
w.workflow AS [Workflow],
c.id AS [Contacts]
FROM
contact c
INNER JOIN app_user o ON c.owner_id = o.id
INNER JOIN contact_workflow w ON c.workflow_id = w.id
WHERE
c.resolver_id IS NULL
AND
o.active_indicator = 1
AND
o.team_id = ' + @team_string + '
AND
w.team_id = ' + @team_string + '
) A
PIVOT
(
count ([Contacts])
FOR [Workflow]
IN (' +@columnHeaders + ')
) B
SELECT * FROM #temp_MatchesTotal UNION ALL
SELECT ''Grand Total'' ,'+@GrandTotalRow +',
ISNULL (SUM([Grand Total]),0) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal'
EXECUTE(@FinalQuery)
This count aggregation, along with the grand total column and row, is working. However, I am trying to generate a minimum aggregation on a date/time field. I'm not used to this matrix-style of thinking, but I managed to get the body of the cross tabulation to show the minimum dates for plan_stamp based on a min pivot on workflow.
CREATE PROCEDURE [dbo].[RetrieveOldestOpenContacts]
@team_id int
AS
/* TEAM SPECIFIC PARAMETER */
DECLARE @team_string varchar(1)
SET @team_string = CONVERT(varchar(10), @team_id)
/* COLUMNS HEADERS */
DECLARE @columnHeaders NVARCHAR (MAX)
SELECT @columnHeaders = COALESCE (@columnHeaders
+ ',[' + workflow + ']', '[' + workflow + ']')
FROM contact_workflow
WHERE team_id = @team_id
GROUP BY workflow
ORDER BY workflow
/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL([' + CAST (workflow AS VARCHAR) +'],0) + ', 'ISNULL([' + CAST(workflow AS VARCHAR)+ '],0) + ')
FROM contact_workflow
WHERE team_id = @team_id
GROUP BY workflow
ORDER BY workflow
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
/* GRAND TOTAL ROW */
DECLARE @GrandTotalRow NVARCHAR(MAX)
SELECT @GrandTotalRow = COALESCE(@GrandTotalRow + ',ISNULL(min([' +
CAST(workflow AS VARCHAR)+']),null)', 'ISNULL(min([' + CAST(workflow AS VARCHAR)+']),null)')
FROM contact_workflow
WHERE team_id = @team_id
GROUP BY workflow
ORDER BY workflow
/* MAIN QUERY */
DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery = 'SELECT *, (' + @GrandTotalCol + ')
AS [Grand Total] INTO #temp_MatchesTotal
FROM
(SELECT
o.full_name AS [User],
w.workflow AS [Workflow],
c.plan_stamp AS [Contacts]
FROM
contact c
INNER JOIN app_user o ON c.owner_id = o.id
INNER JOIN contact_workflow w ON c.workflow_id = w.id
WHERE
c.resolver_id IS NULL
AND
o.active_indicator = 1
AND
o.team_id = ' + @team_string + '
AND
w.team_id = ' + @team_string + '
) A
PIVOT
(
min ([Contacts])
FOR [Workflow]
IN (' +@columnHeaders + ')
) B
SELECT * FROM #temp_MatchesTotal UNION ALL
SELECT ''Grand Total'' ,'+@GrandTotalRow +',
ISNULL (min([Grand Total]),null) FROM #temp_MatchesTotal
DROP TABLE #temp_MatchesTotal'
EXECUTE(@FinalQuery)
The grand total row is working appropriately, but NOT the grand total column. Instead of the minimum value of the row, the grand total column is returning the date values added together.
I need to fix this if possible, so I can show the oldest date (i.e. the min) for that particular user (i.e. the grand total column). I don't quite understand how to accomplish that modification, either using Coalesce or something else.
UPDATE: Casting the date field as a datetime type, to avoid it being read as a string, did not fix the issue. The grand total column still adds the values together, rather than finding the lowest. Please see result here.CAST(c.plan_stamp AS datetime)
