quote:

---

Originally posted by sz1

thats fair enough, I've just run this and I know whats happening now.I'm getting more records back because the sales team has an Incident somewhere in the record, so If I could only say give me the last record (the one which is live) that = sales then that would do it. Tried putting a MAX in front as below but still getting same result, thought that would have done it as Max should be the last record = sales?

Does this help?

ALTER View [dbo].[SDCalls]
AS


SELECT i.[IncidentNumber],Max(t.[OwnerTeam]) As [OwnerTeam],i.[Status],
COUNT(Distinct i.[IncidentNumber]) As [LiveCalls]
FROM
dbo.Incident AS i
Join dbo.Task AS t ON i.[Recid] = t.[ParentLink_RecID]
Where
i.[Status] IN ('Active','Waiting')
and t.[OwnerTeam] = 'Sales'
Group by
i.IncidentNumber,
i.Status,
t.OwnerTeam

GO

---

USE [IT1]
GO

/****** Object: View [dbo].[SDeskCalls] Script Date: 03/22/2012 11:38:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER View [dbo].[SDeskCalls]
AS

SELECT i.[IncidentNumber],
Max(t.[CreatedDateTime]) As LastAssignment, --only show last record
t.[OwnerTeam],
i.[Status],
COUNT(Distinct i.[IncidentNumber]) As [Live ITSM Calls]
FROM
dbo.Incident AS i
Join dbo.Task AS t ON i.[Recid] = t.[ParentLink_RecID]
Where
i.[Status] IN ('Active','Waiting')
and t.[OwnerTeam] = 'Service Desk'
--and t.[OwnerTeam] Not In ('Asset Management','Systems')
Group by
i.IncidentNumber,
i.Status,
t.OwnerTeam

--Order By i.[IncidentNumber]

--select * from [SDeskCalls]

GO