I'm trying to modify a stored procedure on a Microsoft SQL server that is used to get data for a SSRS table. It currently filters by a few parameters, one of them "Analyst" but I would like for the query to return the analyst as well to the report so if you leave the parameter empty it would you could still see what analyst was assigned the ticket
This is the code used to find the analyst info, how can I edit it to allow me to return the Analyst display name as well?
LEFT OUTER JOIN(
SELECT
Analyst.UserName AS AnalystASURITE,
Analyst.DisplayName AS DisplayName,
Analyst.UserDimKey,
WIATUFact.WorkItemDimKey
FROM
dbo.UserDim Analyst
JOIN
dbo.WorkItemAssignedToUserFactvw WIATUFact
ON Analyst.UserDimKey = WIATUFact.WorkItemAssignedToUser_UserDimKey
WHERE WIATUFact.DeletedDate IS NULL -- We only need the information for the last analyst assigned.
GROUP BY WIATUFact.WorkItemDimKey, Analyst.UserName, Analyst.DisplayName, Analyst.UserDimKey, WIATUFact.CreatedDate
) AssignedAnalystInfo
ON AssignedAnalystInfo.WorkItemDimKey = WI.WorkItemDimKey
I added
Analyst = AssignedAnalystInfo.DisplayName,
to the top of my procedure and it was correct syntax but got this error
Column 'AssignedAnalystInfo.DisplayName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I can add the whole procedure if that is needed but it's pretty long.
USE [DWDataMart]
GO
/****** Object: StoredProcedure [dbo].[RTS_Report_IncidentManagement_GetIncidentMetricData2018] Script Date: 11/29/2018 2:30:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RTS_Report_IncidentManagement_GetIncidentMetricData2018]
@StartDate datetime,
@EndDate datetime,
@LanguageCode nvarchar(max)= 'ENU',
@Department nvarchar(max) = '',
@Analyst nvarchar(max) = '',
@AffectedUser nvarchar(max) = '',
@DateFilter nvarchar(256) = 'CreatedOn',
@SupportGroups nvarchar(max) = -1,
@Priority nvarchar(max) = -1
AS
BEGIN
SET NOCOUNT ON
/* Adds a day to the End Date if it is set to midnight.
This is needed as the console picks midnight of the End Date which cuts off the last day.
Otherwise it simply leaves it as is.*/
If (@EndDate = cast(@EndDate as date))
SET @EndDate = DATEADD(DAY, 1, @EndDate)
DECLARE @Error int
DECLARE @ExecError int
DECLARE @tableDept TABLE (value nvarchar(256))
INSERT @tableDept (value)
SELECT * FROM dbo.fn_CSVToTableString(@Department)
DECLARE @tableAnalyst TABLE (value nvarchar(256))
INSERT @tableAnalyst(value)
SELECT * FROM dbo.fn_CSVToTableString(@Analyst)
DECLARE @tableAffectedUser TABLE (value nvarchar(256))
INSERT @tableAffectedUser(value)
SELECT * FROM dbo.fn_CSVToTableString(@AffectedUser)
DECLARE @tableSupportGroups TABLE (value nvarchar(256))
INSERT @tableSupportGroups (value)
SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroups)
DECLARE @tablePriority TABLE (value nvarchar(256))
INSERT @tablePriority (value)
SELECT * FROM dbo.fn_CSVToTableInt(@Priority)
SELECT
--We need some datasets. This SP will pull each incident based on the parameters. How it is displayed
--depends on the report running this SP.
IncidentTitle = I.Title,
AffectedUser = AffectedUserInfo.DisplayName,
IncidentIR = I.Id,
AAnalyst = AssignedAnalystInfo.DisplayName,
IRCreatedDate = I.CreatedDate,
IRResolvedDate = I.ResolvedDate,
TimeToAssignment = CASE
WHEN
(
FirstAssignedDate.CreatedDate IS NOT NULL
AND
I.Priority > 3
)
THEN
DATEDIFF(MINUTE, I.CreatedDate, FirstAssignedDate.CreatedDate)
ELSE NULL
END,
TimeWorked = CASE
WHEN
(
(TotalBT.TimeWorked IS NOT NULL)
)
THEN
TotalBT.TimeWorked
ELSE NULL
END,
TimeToResolution = CASE
WHEN
(
(I.Status = 'IncidentStatusEnum.Resolved' OR I.Status = 'IncidentStatusEnum.Closed')
AND
(I.CreatedDate IS NOT NULL)
AND
(I.ResolvedDate IS NOT NULL)
AND
(I.Priority > 3)
)
THEN
DATEDIFF(MINUTE, I.CreatedDate, I.ResolvedDate) - dFact.PendingDuration
ELSE NULL
END,
-- Unseen stuff is selected and processed accordingly
IncidentDimKey = I.IncidentDimKey
FROM dbo.IncidentDim I
-- JOINS to other needed tables
-- Join the incident dimension to the workitem dimension.
INNER JOIN dbo.WorkItemDim WI
ON WI.EntityDimKey = I.EntityDimKey
--Join the AssignedTo fact table to the workitem table. We can use this to get information on the assigned
--analyst.
LEFT OUTER JOIN(
SELECT
Analyst.UserName AS AnalystASURITE,
Analyst.DisplayName AS DisplayName,
Analyst.UserDimKey,
WIATUFact.WorkItemDimKey
FROM
dbo.UserDim Analyst
JOIN
dbo.WorkItemAssignedToUserFactvw WIATUFact
ON Analyst.UserDimKey = WIATUFact.WorkItemAssignedToUser_UserDimKey
WHERE WIATUFact.DeletedDate IS NULL -- We only need the information for the last analyst assigned.
GROUP BY WIATUFact.WorkItemDimKey, Analyst.UserName, Analyst.DisplayName, Analyst.UserDimKey, WIATUFact.CreatedDate
) AssignedAnalystInfo
ON AssignedAnalystInfo.WorkItemDimKey = WI.WorkItemDimKey
--Join the Assigned To fact table so we can calculate the assignment times. Only need the first assignment information.
LEFT OUTER JOIN(
SELECT
WorkItemDimKey,
MIN(CreatedDate) AS CreatedDate
FROM
dbo.WorkItemAssignedToUserFactvw WIATUFact
GROUP BY WorkItemDimKey
) FirstAssignedDate
ON FirstAssignedDate.WorkItemDimKey = WI.WorkItemDimKey
--Start Total TimeWorked joins. We can pull time and sum per incident.
LEFT OUTER JOIN(
SELECT
SUM(BT.TimeInMinutes) AS TimeWorked,
WIBTFact.WorkItemDimKey
FROM
dbo.BillableTimeDim BT
JOIN
dbo.WorkItemHasBillableTimeFactvw WIBTFact
ON BT.BillableTimeDimKey = WIBTFact.WorkItemHasBillableTime_BillableTimeDimKey
GROUP BY WIBTFact.WorkItemDimKey
) TotalBT
ON TotalBT.WorkItemDimKey = WI.WorkItemDimKey
--Join the AffectedUser fact table to the workitem table. We need this so we have some information about
--the affeted user.
LEFT OUTER JOIN(
SELECT
UserName,
DisplayName,
Department =
CASE
WHEN(Department = '' OR Department IS NULL)
THEN 'Unknown'
ELSE Department
END,
WIAUFact.WorkItemDimKey
FROM UserDim AffectedUserInfo
JOIN dbo.WorkItemAffectedUserFactvw WIAUFact ON AffectedUserInfo.UserDimKey = WIAUFact.WorkItemAffectedUser_UserDimKey
AND
WIAUFact.DeletedDate IS NULL
GROUP BY WorkItemDimKey, CreatedDate, UserName, Department, DisplayName
) AS AffectedUserInfo
ON AffectedUserInfo.WorkItemDimKey = WI.WorkItemDimKey
--Next two JOIN needed so we can pull the name and enum values for the support groups.
LEFT OUTER JOIN dbo.IncidentTierQueues AS SupportGroupEnum
ON SupportGroupEnum.IncidentTierQueuesId = I.TierQueue_IncidentTierQueuesId
LEFT OUTER JOIN
dbo.DisplayStringDim SupportGroupDS
ON SupportGroupEnum.EnumTypeId=SupportGroupDS.BaseManagedEntityId
AND SupportGroupDS.LanguageCode = @LanguageCode
LEFT OUTER JOIN
(
SELECT
ActiveDuration = SUM(
CASE
WHEN statusEnum.ID = 'IncidentStatusEnum.Active'
THEN dFact.TotalTimeMeasure
ELSE 0
END
),
PendingDuration = SUM(
CASE
WHEN statusEnum.ID = 'IncidentStatusEnum.Active.Pending'
THEN dFact.TotalTimeMeasure
ELSE 0
END
),
dFact.IncidentDimKey
FROM
dbo.IncidentStatusDurationFactvw dFact
LEFT OUTER JOIN
dbo.IncidentStatus statusEnum
ON statusEnum.IncidentStatusId = dFact.IncidentStatusId
GROUP BY dfact.IncidentDimKey
) dFact
ON dFact.IncidentDimKey = I.IncidentDimKey
WHERE
(@StartDate <= @EndDate)
AND
I.CreatedDate >= @StartDate
AND
I.CreatedDate <= @EndDate
AND
(
(@DateFilter = 'ClosedOn' AND ((I.ClosedDate >= @StartDate) AND (I.ClosedDate < @EndDate))) OR
(@DateFilter = 'CreatedOn' AND ((I.CreatedDate >= @StartDate) AND (I.CreatedDate < @EndDate))) OR
(@DateFilter = 'ResolvedOn' AND ((I.ResolvedDate >= @StartDate) AND (I.ResolvedDate < @EndDate)))
)
AND
((-1 IN (Select value from @tableSupportGroups)) OR (CASE WHEN (SupportGroupEnum.IncidentTierQueuesId IS NULL) THEN '0' ELSE SupportGroupEnum.IncidentTierQueuesId END IN (SELECT value FROM @tableSupportGroups)))
AND
(('' IN (Select value from @tableDept)) OR (AffectedUserInfo.Department IN (Select value from @tableDept)))
AND
(('' IN (Select value from @tableAnalyst)) OR (AssignedAnalystInfo.AnalystASURITE IN(Select value from @tableAnalyst)))
AND
(('' IN (Select value from @tableAffectedUser)) OR (AffectedUserInfo.UserName IN(Select value from @tableAffectedUser)))
AND
((-1 IN (Select value from @tablePriority)) OR (I.Priority IN (Select value from @tablePriority)))
GROUP BY
I.Title,
I.Id,
I.CreatedDate,
I.ResolvedDate,
I.Priority,
I.Status,
I.IncidentDimKey,
TimeWorked,
AffectedUserInfo.DisplayName,
FirstAssignedDate.CreatedDate,
dFact.PendingDuration
SET @Error = @@ERROR
QuitError:
Return @Error
END
