troubleshooting Question

SQL Server syntax

Avatar of jknj72
jknj72 asked on
Microsoft SQL ServerSQL
11 Comments2 Solutions159 ViewsLast Modified:
I have this query but I only want the last comment returned from ItemComments table.
Could I do a MAX(ItemCommentID) to get the last comment back?

	SELECT WWT.WeekID, CONVERT(varchar(50), StartWorkDay, 101) + ' - ' + CONVERT(varchar(50), EndWorkDay, 101) as 'WeekRange', 
	CONVERT(varchar(50), StartWorkDay, 101) as 'StartWorkDay', StatusID, Comments, <--Need the last Comment entered???
		 ISNULL((SELECT COUNT(wid.WorkItemDetailID)
			 FROM WorkItem wi
				 INNER JOIN WorkItemDetails wid ON wi.WorkItemID=wid.WorkItemID
			 WHERE wi.WorkItemID = wwt.WorkItemID), 0) as 'WorkCount',
		ISNULL((SELECT COUNT(tid.TravelItemDetailID)
			 FROM TravelItem ti 
				 INNER JOIN TravelItemDetails tid ON ti.TravelItemID = tid.TravelItemID
			WHERE wwt.TravelItemID = ti.TravelItemID), 0) as 'TravelCount',
		 ISNULL((SELECT COUNT(iid.ImageItemDetailID)
			 FROM ImageItem ii 
				 INNER JOIN ImageItemDetails iid ON ii.ImageItemID = iid.ImageItemID
			  WHERE wwt.ImageItemID = ii.ImageItemID), 0) as 'ImageCount', WW.WorkWeekID as 'WeekKey', 
		CONVERT(varchar(50), StartDay, 101) as 'StartDay', CONVERT(varchar(50), EndDay, 101) as 'EndDay'
	FROM WorkWeek WW
	INNER JOIN WorkWeekTracker WWT ON WW.WorkWeekID = WWT.WorkWeekID
	LEFT JOIN ItemComments ic on wwt.WeekID = ic.WeekID and wwt.UserID=ic.UserID
	Where StatusID = @StatusID AND WWT.UserID = @UserID 
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros