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 I am getting:
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.
The "having" section id used for aggregation functions (min,max,sum ...). Also you're cartesian join except you don't join any fields (maybe you're trying to, in the "having" section). You should really consider using join. In you particular case:
from 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
inner join stagingdb.dbo.afl_mes2O_interface_data_arch as c
on c.dj_number=b.dj_number
and then your "having" section could lokke like this:
having max(b.action_time)>min(a.action_time)
and max(c.action_time)>max(b.action_time)
Also I find these lines strange. Seconds divided by 60 equals what?
Thank you for your help.
Here is what I have done and it worked perfectly!
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