Max and group by issue - 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

Development Tools

Reporting Services Development

Max and group by issue

Author

Topic

KJN
Starting Member

2 Posts

Posted - 2010-03-10 : 13:53:20

I have created a query in Reporting Services 2003/sql server 2000 that shows all data related to the max timestamp. I want to add in the action_desc to the row that corresponds to that date.
If I add that field, I get the error.."not included in the aggregate or group by clause"
If I include it in the group by clause..."the text, ntext and image data types cannot be compared or sorted..."

Help please : ) Thank you!

SELECT MAX(DATEADD(ss, AHD.act_log.time_stamp - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102))) AS [Time Stamp],
AHD.ctct.c_first_name + ' ' + AHD.ctct.c_last_name AS Analyst, AHD.call_req.ref_num AS Request, AHD.ctct.id, DATEADD(ss,
AHD.call_req.open_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Date Opened], ctct_3.c_last_name AS [Group], DATEADD(ss,
AHD.call_req.close_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Date Closed], AHD.act_log.type
FROM AHD.call_req LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid = AHD.act_log.call_req_id LEFT OUTER JOIN
AHD.ctct ctct_3 ON AHD.call_req.group_id = ctct_3.id LEFT OUTER JOIN
AHD.ctct ON AHD.call_req.assignee = AHD.ctct.id
WHERE (ctct_3.c_last_name = 'LPCH IDX') AND (AHD.call_req.active_flag = 1) AND (AHD.act_log.type = 'ST')
GROUP BY AHD.ctct.c_first_name + ' ' + AHD.ctct.c_last_name, AHD.call_req.ref_num, AHD.ctct.id, DATEADD(ss, AHD.call_req.open_date - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), ctct_3.c_last_name, DATEADD(ss, AHD.call_req.close_date - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)), AHD.act_log.type
ORDER BY DATEADD(ss, AHD.call_req.open_date - 18000, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) DESC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 15:25:57

First we should see formatted code:

SELECT   Max(Dateadd(ss,ahd.act_log.time_stamp - 18000,Convert(DATETIME,'1970-01-01 00:00:00',102))) AS [Time Stamp], 
         ahd.ctct.c_first_name + ' ' + ahd.ctct.c_last_name                                          AS analyst, 
         ahd.call_req.ref_num                                                                        AS request, 
         ahd.ctct.id, 
         Dateadd(ss,ahd.call_req.open_date - 18000,Convert(DATETIME,'1970-01-01 00:00:00',102))      AS [Date Opened], 
         ctct_3.c_last_name                                                                          AS [Group], 
         Dateadd(ss,ahd.call_req.close_date - 18000,Convert(DATETIME,'1970-01-01 00:00:00',102))     AS [Date Closed], 
         ahd.act_log.TYPE 
FROM     ahd.call_req 
         LEFT OUTER JOIN ahd.act_log 
           ON ahd.call_req.persid = ahd.act_log.call_req_id 
         LEFT OUTER JOIN ahd.ctct ctct_3 
           ON ahd.call_req.group_id = ctct_3.id 
         LEFT OUTER JOIN ahd.ctct 
           ON ahd.call_req.assignee = ahd.ctct.id 
WHERE    (ctct_3.c_last_name = 'LPCH IDX') 
         AND (ahd.call_req.active_flag = 1) 
         AND (ahd.act_log.TYPE = 'ST') 
GROUP BY ahd.ctct.c_first_name + ' ' + ahd.ctct.c_last_name, 
         ahd.call_req.ref_num, 
         ahd.ctct.id, 
         Dateadd(ss,ahd.call_req.open_date - 18000,Convert(DATETIME,'1970-01-01 00:00:00',102)), 
         ctct_3.c_last_name, 
         Dateadd(ss,ahd.call_req.close_date - 18000,Convert(DATETIME,'1970-01-01 00:00:00',102)), 
         ahd.act_log.TYPE 
ORDER BY Dateadd(ss,ahd.call_req.open_date - 18000,Convert(DATETIME,'1970-01-01 00:00:00',102)) DESC

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 16:26:28

Next we should get a bit more information about table structure and data types.
Best with sample data and wanted output in relation to sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

KJN
Starting Member

2 Posts

Posted - 2010-03-10 : 20:55:20

could not find a way to attach a doc. Is this enough? I had a couple of screen shots.....

AHD.act_log.time_stamp number
AHD.ctct.c_first_name + ' ' + AHD.ctct.c_last_name text
Call_req.ref_num text
Ctct_.id number
AHD.call_req.open_date number
Ctct_3.c_last_name text
AHD.call_req.close_date number
Act_log.type text
Active_flag number
Act_log.action_desc memo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources