How to Select This Query

Question

Post reply

How to Select This Query

tamil.selvanmca

Ten Centuries

Points: 1367
More actions
June 11, 2012 at 10:56 am

#270850

CardNo DateTime GateEntry
00000002012012-06-1108:34:23MainGateEntry-Big
00000002012012-06-1108:38:30FF LAB-8 Entry
00000002012012-06-1119:31:03MainGateExit-Big
00000002022012-06-1108:36:10MainGateEntry-Big
00000002022012-06-1115:43:03PilotPlant-1GF Exit
00000002022012-06-1117:31:00PilotPlant-1GF Entry
00000002022012-06-1117:43:04MainGateExit-Big
00000002072012-06-1116:19:41FF LAB-5 Entry
00000002072012-06-1116:24:48GF Main Door Exit
00000002072012-06-1116:28:58MainGateExit-Big
00000002082012-06-1111:45:31GFDoor -Exit
00000002082012-06-1113:56:37GF Door -Entry
00000002082012-06-1115:08:59MainGateExit-Big
I Want This Employee only .This Employee Does Not entry in MainGateEntry-Big
CardNo Date
00000002072012-06-11
00000002082012-06-11

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

sample, consumable data is the key to getting answers here.

if you put your data in a format a volunteer can copy/paste /adapt, you can get instant, tested answers.

i couldn't tell if your data was seperate columns for date and time, or one column .

the right code avoids confusion like that.

With SampleData (CardNo, [Date Time] ,GateEntry)

AS

(

SELECT '0000000201','2012-06-11 08:34:23','MainGateEntry-Big ' UNION ALL

SELECT '0000000201','2012-06-11 08:38:30','FF LAB-8 Entry ' UNION ALL

SELECT '0000000201','2012-06-11 19:31:03','MainGateExit-Big ' UNION ALL

SELECT '0000000202','2012-06-11 08:36:10','MainGateEntry-Big ' UNION ALL

SELECT '0000000202','2012-06-11 15:43:03','PilotPlant-1GF Exit ' UNION ALL

SELECT '0000000202','2012-06-11 17:31:00','PilotPlant-1GF Entry' UNION ALL

SELECT '0000000202','2012-06-11 17:43:04','MainGateExit-Big ' UNION ALL

SELECT '0000000207','2012-06-11 16:19:41','FF LAB-5 Entry ' UNION ALL

SELECT '0000000207','2012-06-11 16:24:48','GF Main Door Exit ' UNION ALL

SELECT '0000000207','2012-06-11 16:28:58','MainGateExit-Big ' UNION ALL

SELECT '0000000208','2012-06-11 11:45:31','GFDoor -Exit ' UNION ALL

SELECT '0000000208','2012-06-11 13:56:37','GF Door -Entry ' UNION ALL

SELECT '0000000208','2012-06-11 15:08:59','MainGateExit-Big'

)

select CardNo,[Date Time]

from SampleData

where CardNo NOT IN(SELECT CardNo FROM SampleData WHERE GateEntry ='MainGateEntry-Big')

GROUP BY

CardNo,[Date Time]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 2

tamil.selvanmca (6/11/2012)
CardNo DateTime GateEntry
00000002012012-06-1108:34:23MainGateEntry-Big
00000002012012-06-1108:38:30FF LAB-8 Entry
00000002012012-06-1119:31:03MainGateExit-Big
00000002022012-06-1108:36:10MainGateEntry-Big
00000002022012-06-1115:43:03PilotPlant-1GF Exit
00000002022012-06-1117:31:00PilotPlant-1GF Entry
00000002022012-06-1117:43:04MainGateExit-Big
00000002072012-06-1116:19:41FF LAB-5 Entry
00000002072012-06-1116:24:48GF Main Door Exit
00000002072012-06-1116:28:58MainGateExit-Big
00000002082012-06-1111:45:31GFDoor -Exit
00000002082012-06-1113:56:37GF Door -Entry
00000002082012-06-1115:08:59MainGateExit-Big
I Want This Employee only .This Employee Does Not entry in MainGateEntry-Big
CardNo Date
00000002072012-06-11
00000002082012-06-11

Huh?

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

SQLKnowItAll (6/11/2012)
tamil.selvanmca (6/11/2012)
CardNo DateTime GateEntry
00000002012012-06-1108:34:23MainGateEntry-Big
00000002012012-06-1108:38:30FF LAB-8 Entry
00000002012012-06-1119:31:03MainGateExit-Big
00000002022012-06-1108:36:10MainGateEntry-Big
00000002022012-06-1115:43:03PilotPlant-1GF Exit
00000002022012-06-1117:31:00PilotPlant-1GF Entry
00000002022012-06-1117:43:04MainGateExit-Big
00000002072012-06-1116:19:41FF LAB-5 Entry
00000002072012-06-1116:24:48GF Main Door Exit
00000002072012-06-1116:28:58MainGateExit-Big
00000002082012-06-1111:45:31GFDoor -Exit
00000002082012-06-1113:56:37GF Door -Entry
00000002082012-06-1115:08:59MainGateExit-Big
I Want This Employee only .This Employee Does Not entry in MainGateEntry-Big
CardNo Date
00000002072012-06-11
00000002082012-06-11
Huh?

Looking for employees who don't have an record for MainGateEntry-Big.

Jared SSC Guru Points: 61793 More actions · Answer 4

Lynn Pettis (6/11/2012)
SQLKnowItAll (6/11/2012)
tamil.selvanmca (6/11/2012)
CardNo DateTime GateEntry
00000002012012-06-1108:34:23MainGateEntry-Big
00000002012012-06-1108:38:30FF LAB-8 Entry
00000002012012-06-1119:31:03MainGateExit-Big
00000002022012-06-1108:36:10MainGateEntry-Big
00000002022012-06-1115:43:03PilotPlant-1GF Exit
00000002022012-06-1117:31:00PilotPlant-1GF Entry
00000002022012-06-1117:43:04MainGateExit-Big
00000002072012-06-1116:19:41FF LAB-5 Entry
00000002072012-06-1116:24:48GF Main Door Exit
00000002072012-06-1116:28:58MainGateExit-Big
00000002082012-06-1111:45:31GFDoor -Exit
00000002082012-06-1113:56:37GF Door -Entry
00000002082012-06-1115:08:59MainGateExit-Big
I Want This Employee only .This Employee Does Not entry in MainGateEntry-Big
CardNo Date
00000002072012-06-11
00000002082012-06-11
Huh?
Looking for employees who don't have an record for MainGateEntry-Big.

Aha!

Jared
CE - Microsoft