Use TEST
Go

With RankResult as
(

select 
i.[IncidentNumber],
i.[CreatedDateTime],
i.[ResolutionDateAndTime],
i.[Priority],
i.[Status],
i.[ClientName],
i.[ClientSite],
t.[OwnerTeam],
t.[Owner],

row_number() over( partition by i.RecID 
order by t.CreatedDateTime desc, t.Owner ) seq,
Cnt = COUNT(1) Over(Partition By i.RecID)

From Incident as i


Left Join Task as t
On i.RecID = t.ParentLink_RecID
Where t.OwnerTeam = 'Info Services'
And i.CreatedDateTime >= '2012-10-01'
And i.CreatedDateTime <= '2013-10-01'

)
select 
Distinct 
[IncidentNumber],
[CreatedDateTime],
[ResolutionDateAndTime],
[Priority],
[Status],
[ClientName],
[ClientSite],
[OwnerTeam],
[Owner]

From RankResult
Where Cnt = 1

Order By IncidentNumber Asc
GO

quote:

---

Originally posted by sz1

mmm I'm seeing the odd record with 2 assignments for the team I select although its looking better as the number of records have reduced quite a lot too, is there a way to add a count to see how many assignments are on each IncidentNumber?
Thanks

SZ1
Please help me to enable me to help others!

---

quote:

---

Originally posted by sz1

The RecID is the main key for Incident, there will only be one row with the IncidentNumber unless there are more assignments in Task, each time a new assignment in Task is created another IncidentNumber row will appear to count for the extra assignments from Task. So even though the code above removes the duplicates IncidentNumber rows when I open up an IncidentNumber I can see the 2 instances of the same team. For example record 332837 has 2 assignments for the same team. I can only tell by opening up an Incident to check so I cant really add an image of the live db. If we can add a count for number of assignments attached to the Incident Number that might help?

There can be any number of assignments on one RecID (IncidentNumber) from the Task table, so its really a matter of saying show me all the records that have the team selected but with only 1 assignment, if that team has more than 1 as in case 332837 dont return.
Thanks

SZ1
Please help me to enable me to help others!

---