Clever Geek Handbook

SQL is different from ID and latest by date

I have the following SQL statement. I need to select the most recent entry for each System.Id using System.RevisedDate

SELECT     [System.Id],[System.RevisedDate], [System.Title], [System.State], [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], 
                      [Microsoft.VSTS.Scheduling.RemainingWork], [Microsoft.VSTS.Scheduling.CompletedWork], [Microsoft.VSTS.CMMI.Estimate]
FROM         WorkItems
WHERE     ([System.WorkItemType] = 'Change Request') AND ([System.CreatedDate] >= '09/30/2008') AND ([System.TeamProject] NOT LIKE '%Deleted%') AND 
                      ([System.TeamProject] NOT LIKE '%Sandbox%')

Can you help?

+1


source to share


5 answers


Try the following:



SELECT * FROM WorkItems w
JOIN (
SELECT     [System.Id],MAX([System.RevisedDate])
FROM WorkItems
WHERE ([System.WorkItemType] = 'Change Request') 
AND ([System.CreatedDate] >= '09/30/2008') 
AND ([System.TeamProject] NOT LIKE '%Deleted%') 
AND ([System.TeamProject] NOT LIKE '%Sandbox%')
GROUP BY {System.Id]
) x ON w.[System.Id] = x.[System.Id] AND w.[System.DateRevised] = x.[System.DateRevised]
+3


source


In general, you should get something like



SELECT ID,DATE_FIELD,FIELD1,FIELD2
FROM TBL1 AS A WHERE DATE_FIELD >= ALL (
   SELECT DATE_FIELD FROM TBL1 AS B
   WHERE A.ID = B.ID 
)
+1


source


or, (using a subquery)

SELECT ID,DATE_FIELD,FIELD1,FIELD2
FROM TBL1 T
Where DATE_FIELD = 
    (Select Max(Date_Field) From Tbl1 
     Where Id = T.Id)

but I couldn't decipher your example query ... Is "System" a table? Why are there no references to WorkItem in the Select clause? he doesn't seem to laugh together ...

+1


source


Try

SELECT [System.Id],[System.RevisedDate], [System.Title], [System.State], [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], 
[Microsoft.VSTS.Scheduling.RemainingWork], [Microsoft.VSTS.Scheduling.CompletedWork], [Microsoft.VSTS.CMMI.Estimate]
FROM         WorkItems
Left JOIN (Select [System.Id],Max([System.RevisedDate]) group by [System.Id]) LatestItems
    on Workitems.[System.Id] = LatestItems.[System.Id] and 
    Workitems.[System.RevisedDate] = LatestItems.[System.RevisedDate]
    WHERE     
([System.WorkItemType] = 'Change Request') AND ([System.CreatedDate] >= '09/30/2008') AND ([System.TeamProject] NOT LIKE '%Deleted%') AND 
                          ([System.TeamProject] NOT LIKE '%Sandbox%')
0


source


I love Alex's answer, but I thought I would pick another option. Sorry I haven't had a chance to test this. :-)

SELECT     WorkItems.[System.Id], WorkItems.[System.RevisedDate], [System.Title], [System.State],     [System.Reason], [System.CreatedDate], [System.WorkItemType], [System.TeamProject], 
                  [Microsoft.VSTS.Scheduling.RemainingWork], [Microsoft.VSTS.Scheduling.CompletedWork], [Microsoft.VSTS.CMMI.Estimate]
FROM         WorkItems join (SELECT     [System.Id],max([System.RevisedDate]) [MaxDate]
                         FROM         WorkItems
                         WHERE     ([System.WorkItemType] = 'Change Request') AND ([System.CreatedDate] >= '09/30/2008') AND ([System.TeamProject] NOT LIKE '%Deleted%') AND 
                         ([System.TeamProject] NOT LIKE '%Sandbox%')
                         group by [System.Id]) MaxDates
on  WorkItems.[System.Id] = MaxDates.[System.Id]
and WorkItems.[System.RevisedDate] = MaxDates.[MaxDate]
0


source






More articles:


All Articles