How to decision making in a record/ row in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to decision making in a record/ row in a table

Author

Topic

xanderp
Starting Member

8 Posts

Posted - 2011-07-03 : 06:43:40

Hi I'm new in SQL programming, I have a problem getting the record that I only want. I use several methods in getting my desired result but I didn't get it. Hope you could help me. Below is my sample table.

LOGNUM/ENTRYNUM/ENTRYTYPE/DATETIMEARRIVAL/FROMZONE/TOZONE/FIRSTNAME
_________________________________________________________________________________________________________________________
20811/2/Turnstile Entry-Entry1/2011-06-10 07:17:13.000/1/2/ARNOLD
22241/3/Turnstile Exit-Exit/2011-06-10 11:29:29.000/2/1/ARNOLD
22696/2/Turnstile Entry-Entry1/2011-06-10 12:47:32.000/1/2/ARNOLD
24057/3/Turnstile Exit-Exit/2011-06-10 17:15:18.000/2/1/ARNOLD

--- As you can see we have the "FROMZONE" and "TOZONE" column. Rows with FROMZONE=1 AND TOZONE=2 signifies that the employee is IN the office, while rows with FROMZONE=2 AND TOZONE=1 signifies that the employee is OUT of the site. LOGNUM is incremental, the highest LOGNUM is the last record in the table.

Assuming the last record has FROMZONE=2 AND FROMZONE=1 (which is employee is out of the office), If I query on the table the record SHOULD NOT be shown because he is out of the office.

Assuming the last record of the employee has FROMZONE=1 AND TOZONE=2 (which is employee is IN the office), If I query on the table the record should be SHOWN because he is IN the office.

In other words we will only get records of employees that is IN THE OFFICE within the current day and current time.

I hope you could help me with this. This is one of my school projects. Big Thanks to those who will help me...

xanderp24

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-03 : 09:25:59

You should show example data with actual dates.
You should show then the wanted result.
You should show what you have done so far...

No, you're never too old to Yak'n'Roll if you're too young to die.

xanderp
Starting Member

8 Posts

Posted - 2011-07-03 : 11:29:24

I want an SQL query that will decide if the employee is on the office or not. In the example above he is OUT OF THE OFFICE, therefore he's record should not be shown on the query because he is OUTSIDE of the office. I just want to get ONLY EMPLOYEES WHOS INSIDE THE OFFICE.. My sample query that i've done so far is this:

SELECT TOP (100) PERCENT MAX(Lognum), ENTRYTYPE, TYPEENTRY, DATETIMEARRIVAL, FromZOne, ToZone,
EMPFIRSTNAME, EMPLASTNAME
FROM TABLEX
WHERE DATEDIFF(DAY, GETDATE(), DATETIMEARRIVAL) = 0) AND FIRSTNAME='ARNOLD'
GROUP BY LOGNUM, ENTRYNUM, ENTRYTYPE, FromZOne, ToZone, FirstName, LastName, DATETIMEARRIVAL
HAVING (ToZone = 2)
ORDER BY LOGNUM DESC

First, I should get the last record, if the last record is EXIT then the record should not be shown. If the last record is ENTRY then the record should be shown and signify that HE IS ON THE OFFICE.

xanderp24

xanderp
Starting Member

8 Posts

Posted - 2011-07-03 : 11:34:06

When I use this query, it only shows the ENTRY RECORDS of employee, I want a result that will evaluate the LAST RECORD if it is ENTRY OR EXIT.If the last record is EXIT ---> LAST RECORD SHOULD NOT BE SHOWN. If the last record is ENTRY ---> it SHOULD BE SHOWN THE RECORDS OF EMPLOYEE.

Hope you could help me with this one. BIG THANKS!

xanderp24

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-03 : 13:11:49

not sure but maybe you mean something like this:

select * from TableX t1
where DATEDIFF(DAY, GETDATE(), DATETIMEARRIVAL) = 0)
AND FIRSTNAME='ARNOLD'
and ToZone = 2
and Lognum = (select max(lognum) from TableX t2 where t1.Firstname = t2.Firstname)

No, you're never too old to Yak'n'Roll if you're too young to die.

xanderp
Starting Member

8 Posts

Posted - 2011-07-03 : 13:35:23

I will try this webfred, I'll give you feedback as soon as possible.

xanderp24

xanderp
Starting Member

8 Posts

Posted - 2011-07-03 : 13:44:03

nope it didn't work. it returns no output.

xanderp24

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-03 : 13:45:52