Hi,
in MSSQL, I have 2 tables naming [Equipment Alarm History] , [EquipmentOperators] - [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
0 1253 Sign in to post your reply or Sign up for a free account.
Similar topics
by: greg |
last post by:
Discussion is invited on the following proto-PEP.
-------------------------------------------------------------
PEP ??? - Overloadable Boolean Operators...
|
by: Frank Buss |
last post by:
I've setup a challenge, mainly for C++, Java and Lisp, but every other
language is welcome:
http://www.frank-buss.de/challenge/index.html
There is nothing to win, but I hope there will be some...
|
by: Frank Buss |
last post by:
A new challenge:
http://www.frank-buss.de/marsrescue/index.html
Have fun! Now you can win real prices.
--
Frank Buß, fb@frank-buss.de
http://www.frank-buss.de, http://www.it4-systems.de
|
by: Steve Summit |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
It's often explained that the reason for some of the imprecision
in C's definition is so that C can be implemented on different
kinds of machines -- say, those...
|
by: Richard Jones |
last post by:
The date for the second PyWeek challenge has been set: Sunday 26th March
to Sunday 2nd April (00:00UTC to 00:00UTC).
The PyWeek challenge invites entrants to write a game in one week from...
|
by: richard |
last post by:
The date for the second PyWeek challenge has been set: Sunday 26th March
to Sunday 2nd April (00:00UTC to 00:00UTC).
The PyWeek challenge invites entrants to write a game in one week from...
|
by: wkehowski |
last post by:
The python code below generates a cartesian product subject to any
logical combination of wildcard exclusions. For example, suppose I want
to generate a cartesian product S^n, n>=3, of that...
|
by: Thierry |
last post by:
For those interested in <b>programming riddles</b>, I would like to
announce a new programming challenge I'm just launching at
http://software.challenge.googlepages.com
This challenge is in its...
|
by: Richard Jones |
last post by:
The fifth PyWeek is only a month away. Come along and join the fun: write a
video game in a week!
There's some really interesting new libraries that have popped up recently.
Have a gander on the...
|
by: jacob navia |
last post by:
Several people in this group argue that standard C
is not portable since there are no compilers for it, etc.
I propose this program in Standard C, that I have compiled
in several OSes to test if...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |