Update table from other tables

Question

Update table from other tables

jamesstirling01

Mr or Mrs. 500

Points: 596
More actions
August 5, 2021 at 3:01 pm

#3917186

Hi,
Basically i have 2 tables check-in and check-out where users check-in and check-out using a RFID card. I am wanting to display who's in the building or been in the building on a given day.
I have a VB.net program writing to the tables how in SQL would it best to display this data. As some users will have checked out and some still in the building. I was thinking another SQL table but cannot think on how to create such a table to display the data i require.
Thanks in advance James

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

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

Michael L John One Orange Chip Points: 27054 More actions · Answer 1

Well, it seems like you would need the max date/time for the checkin, and the same thing for the checkout on a per user basis.

If the check in date/time is greater than the checkout time, then they should be in the building.

Some pseudo code:

SELECT MAX(Checkin), MAX(Checkout), IN.UserID,

FROM Checkin IN

LEFT JOIN CheckOut OUT ON IN.UserID - OUT.UserID

GROUP BY IN.UserID

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ktflash Default port Points: 1400 More actions · Answer 2

For future posts, please post some example code:

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

jamesstirling01 Mr or Mrs. 500 Points: 596 More actions · Answer 3

Thanks Michael,

I will test your SQL Query and let you know if any problems.

Cheers James

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

>> Basically I have 2 tables check-in and check-out where users check-in and check-out using an RFID card. I am wanting to display who's in the building or has been in the building on a given day. <<

Your design is fundamentally wrong. Your checking in and out as if they are separate attributes; they are attributes of a single event. Here is a simple skeleton for the table you should have. A copy of temporal queries in SQL by Rick Snodgrass is free as a PDF from the University of Arizona.

This design flaw is called attributes splitting. It means things that should be in one table or column have been modeled in multiple tables or multiple columns in the same table. A NULL end_timestamp means the event is still ongoing. You can use other constraints to prevent people from spending too much time in an event, simple arithmetic to determine how many hours the user spent at an event, and so forth when they're all in one place instead of being split like this

CREATE TABLE Events

(user_id CHAR(10) NOT NULL,

start_timestamp DATETIME2(0) NOT NULL,

end_timestamp DATETIME2(0),

CHECK(start_timestamp <= end_timestamp),

PRIMARY KEY (user_id, start_timestamp));

It would help if you would follow basic netiquette and post DDL. That's what we've done for over 30 years on SQL forums.

Please post DDL and follow ANSI/ISO standards when asking for help.