A challenge in Operators-Alarm

[thelightkeeper]

Hi,
in MSSQL, I have 2 tables naming [Equipment Alarm History] , [EquipmentOperat ors]

[Equipment Alarm History]
---------------------------------------
EquipmentID : nvarchar(50), FK, reference [EquipmentOperators].EquipmentID
AlarmID : nvarchar(50), PK
AlarmHappenTime : Datetime ,required, not null
AlarmClearTime : Datetime, required, not null

and

[EquipmentOperators]
-------------------------------------
OperatorID: nvarchar(50), PK
EquipmentID: nvarchar(50), PK
LoginTime: Datetime, required, not null  

Logic:

The operator can login a machine, then machine cause alarms which stored in [Equipment Alarm History].

or

Machine cause alarms , then operator login, clear alarm.

Now the question is:
For equipmentID= SPD1, how can I know the OperatorID who most handle the AlarmID ( for example : AlamrID= '122042') during Jan-2007 and Feb-2007 ?

Which SQL statements should I use ?

I tried :

SELECT  COUNT(EquipmentOperators.OperatorID) as #, EquipmentOperators.OperatorID,
Operators.Operator_Name
 
FROM [Equipment Alarm History] INNER JOIN EquipmentOperators 
ON [Equipment Alarm History].EquipmentID = EquipmentOperators.EquipmentID 
FULL OUTER JOIN Operators 
ON EquipmentOperators.OperatorID = Operators.Operator_ID
 
WHERE([Equipment Alarm History].AlarmID = '122042') 
AND ([Equipment Alarm History].EquipmentID = 'spd1') 
AND 
(
    [Equipment Alarm History].Time BETWEEN 
    CONVERT(DATETIME, '2007-02-01 00:00:00', 102) 
    AND    CONVERT(DATETIME, '2007-03-01 00:00:00', 102)
)
AND (EquipmentOperators.Datetime BETWEEN CONVERT(DATETIME, '2007-02-01 00:00:00', 102) 
AND    CONVERT(DATETIME, '2007-03-01 00:00:00', 102))
 
group by Operators.Operator_name,EquipmentOperators.OperatorID