Help with my Max Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with my Max Query

Author

Topic

jodders
Starting Member

41 Posts

Posted - 2012-06-12 : 05:15:47

Hi,

I have run into a bit of a road block with a query that I am having trouble with. Basically, my code below monitors comments added by users in Real Time and ideally, I would like my code to return only the most recent narrative added. At the moment, its returning all comments which is not what I am after.

Really stuck,can anybody help?

Thanks


SELECT  t1.ActivityID,t1.CreateDate, t1.client_id,t1.matter_id,t1.invoice_num,(SELECT [Narrative] FROM tblActivity a WHERE a.ActivityID = t1.ActivityID) as [Narrative]
FROM (
      SELECT client_id, matter_id, invoice_num, invoice_date, MAX(tblActivity.ActivityID) as ActivityID, MAX(CreateDate) as CreateDate
      FROM tblActivity
            INNER JOIN tblActivityInstance  ON tblActivityInstance.ActivityID = tblActivity.ActivityID
      WHERE tblActivity.ActivityTypeID = 2 AND tblActivity.ActionID in ('1','2','3','4','5','6','7','8','9','10','11','12','13')
      GROUP BY invoice_num,ActionID, client_id, matter_id,  invoice_date
) AS t1


Current Result:
ActivityID	CreateDate	  client_id	matter_id	invoice_num	Narrative
19299	        12/06/2012 09:32	AA	1	         101201171	Bannana
19298	        12/06/2012 09:31	AA	1	          101201171	Apple
           

Current Wanted Back:

ActivityID	CreateDate	client_id	matter_id	invoice_num	Narrative
19299	12/06/2012 09:32	AA	1	101201171	Bannana




ORDER BY  t1.CreateDate desc --,t1.invoice_num,t1.client_id desc

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-12 : 05:25:56

A lot of people would be willing to help you if you ask the question the right way.

The right way is to post DDL of the tables, some sample Data.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources