CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
Doug Ancil
Doug Ancil

Reputation: 19

Using Max and group by

I have the following query:

SELECT a.* FROM 
(SELECT
 moncallAdd.FirstListing,
  max (Dateadd(MINUTE, moncalladd.addtime,
         DateAdd(Day,moncalladd.adddate,'12/31/1899'))) as AddStart,
 DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
 DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
                 DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
 'Added' AS Activity
 FROM
 mdr.dbo.mOnCallAdd
  WHERE DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
 DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) < GETDATE() 
AND 
 DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
                 DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) >  GETDATE()
AND  mOnCallAdd.SchedName = 'capital neph') a 

LEFT JOIN 
(SELECT
 moncallDelete.FirstListing,
 DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
         DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
 DATEADD(MINUTE, mOnCallDelete.duration,
         DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
                 DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,
'Deleted' AS Activity
FROM
   mdr.dbo.mOnCallDelete
  WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
 DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE() 
AND 
 DATEADD(MINUTE, mOnCallDelete.duration,
         DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
                 DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) >  GETDATE()
AND  mOnCallDelete.SchedName = 'capital neph') b 
ON a.FirstListing = b.FirstListing
and a.oncallstart = b.oncallstart
and a.oncallend = b.oncallend
group by FirstListing

and when I try to run this query I get the following error:

Ambiguous column name 'FirstListing'.

and when I try group by moncalladd.firstlisting, I get this error:

The column prefix 'moncalladd' does not match with a table name or alias name used in the query.

and when I try to group by a.firstlisting I get this error:

Column 'a.AddStart' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't know what I'm missing, but obviously it's something simple. Can anyone please help me with this?

Upvotes: 1

Views: 211

Answers (2)

p.campbell
p.campbell

Reputation: 100577

  1. Modify your derived table a to have its own GROUP BY. You've got a MAX() in there that forces you to include a GROUP BY. See Lines 4 thorugh 16 on my Pastie.

2.Modify your SELECT and GROUP BY on the first and last lines of your query:

SELECT a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity
FROM ---your big derived table
GROUP BY  a.FirstListing, a.AddStart, a.OnCallStart, a.OnCallEnd, a.Activity

Here's how your code should look: http://pastie.org/2428802

Upvotes: 0

AakashM
AakashM

Reputation: 63338

The moment you use a GROUP BY clause, you have to specify, for every column in the SELECT list, whether you want a group-aggregate operator (eg SUM, MAX) applied to it, or whether it is to form part of the group key - this latter is done by including it in the GROUP BY list.

Since you are SELECTing all 5 columns of your subquery a, you have to say what is to happen to every single one, when the grouping operation is performed.

It's not immediately obvious to me that your query is correct, so I can't say what the solution is, but it may be simply a metter of explicitly listing all the columns of a inthe GROUP BY clause.

Upvotes: 1

Related Questions