Question about having and where with my Query code

Question

question

samn265 asked Feb 23, '19

Question about having and where with my Query code

I am getting an error with this code:

SELECT 
		A.DJ_NUMBER	as djNo
		,MAX(A.ACTION) as 'SetupStarted'
		,MIN(A.ACTION_TIME) AS 'SetupStart'
		,MAX(B.ACTION) as 'RunStarted'
		,MAX(B.ACTION_TIME) AS 'UpTimeStart'
		,MAX(C.ACTION) as 'RunEnded'
		,MAX(C.ACTION_TIME) AS 'UpTimeEnd'
		,FORMAT(DATEDIFF(SECOND, MAX(A.ACTION_TIME), MAX(B.ACTION_TIME))/60.0,'##.##') AS Setup
		,FORMAT(DATEDIFF(SECOND, MAX(B.ACTION_TIME), MAX(C.ACTION_TIME))/60.0, '##.##') AS UpTime	
			
FROM [AFL_MES2O_Interface_Data_arch] A, [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] B, [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] C
--WHERE (B.DJ_NUMBER = A.DJ_NUMBER AND MAX(B.ACTION_TIME) > Min(A.ACTION_TIME)) and (C.DJ_NUMBER = B.DJ_NUMBER AND MAX(C.ACTION_TIME) > MAX(B.ACTION_TIME)) 
GROUP BY  A.DJ_NUMBER	
HAVING (B.DJ_NUMBER = A.DJ_NUMBER AND MAX(B.ACTION_TIME) > Min(A.ACTION_TIME)) and (C.DJ_NUMBER = B.DJ_NUMBER AND MAX(C.ACTION_TIME) > MAX(B.ACTION_TIME))
ORDER BY SetupStart ASC, UpTimeEnd

Here is the error:

Msg 8121, Level 16, State 1, Line 15 Column 'StagingDB.dbo.AFL_MES2O_Interface_Data_arch.DJ_NUMBER' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8121, Level 16, State 1, Line 15 Column 'StagingDB.dbo.AFL_MES2O_Interface_Data_arch.DJ_NUMBER' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8121, Level 16, State 1, Line 15 Column 'StagingDB.dbo.AFL_MES2O_Interface_Data_arch.DJ_NUMBER' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

sql sql-server-2012

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-02-24T00:11:20Z

samn265 answered Feb 24, '19

I got it. Here's the solution:

				SELECT 
		
						 A.DJ_NUMBER	AS JobNo
						,MAX(A.ACTION) as SetupStarted
						,MIN(A.ACTION_TIME) AS SetupStart	  
				--		,MAX(A.ACTION_TIME) AS 'SetupStartEnd'
						,MAX(B.ACTION) as RunStarted
				--		,MIN(B.ACTION_TIME) as 'RunStarted'	
						,MAX(B.ACTION_TIME) AS UpTimeStart
						,MAX(C.ACTION) as 'RunEnded'
				--		,MIN(C.ACTION_TIME) as 'UpTimeEndStart'
						,MAX(C.ACTION_TIME) AS UpTimeEnd 
						,FORMAT(DATEDIFF(SECOND, MIN(A.ACTION_TIME), MAX(B.ACTION_TIME))/60.0,'##.##') AS Setup
						,FORMAT(DATEDIFF(SECOND, MAX(B.ACTION_TIME), MAX(C.ACTION_TIME))/60.0,'##.##') AS UpTime	


				FROM
						[StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] as A
						INNER JOIN [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] as B
								ON (B.DJ_NUMBER = A.DJ_NUMBER  AND A.ACTION_TIME IS NOT NULL) AND B.ACTION_TIME > A.ACTION_TIME
						INNER JOIN [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] as C
								ON (C.DJ_NUMBER = B.DJ_NUMBER  AND B.ACTION_TIME IS NOT NULL) AND C.ACTION_TIME > B.ACTION_TIME 
				GROUP BY  A.DJ_NUMBER

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2019-02-23T15:20:23Z

KenJ answered Feb 23, '19

You can't place your JOIN conditions in the having clause. If you use the ansi join syntax (always recommended!), it will prevent you from creating this type of error.

Updating the joins like this:

FROM [AFL_MES2O_Interface_Data_arch] A
inner join [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] B 
	on A.DJ_NUMBER = B.DJ_NUMBER
inner join [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] C 
	on B.DJ_NUMBER = C.DJ_NUMBER

cleans up your having clause like this:

HAVING MAX(B.ACTION_TIME)> Min(A.ACTION_TIME) 
	AND MAX(C.ACTION_TIME)> MAX(B.ACTION_TIME)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question