Help With Query 1

[shawkz]

Hi i have the following query...

SELECT UserHistory.DateTime, Users.Name, Users.Surname, UserHistory.Type
FROM UserHistory INNER JOIN Users ON UserHistory.UserID = Users.UserID where UserHistory.Location = 'Warehouse' order by UserHistory.DateTime desc

This returns the following...

DateTime Name Surname Type
22/01/2007 14:26:00 Jim Brown In
22/01/2007 14:25:00 Stuart Jackson In
22/01/2007 13:38:00 Peter James Out
22/01/2007 13:30:00 Peter James In


I want to only display users who have not signed out of the warehouse, please can someone show me where i am going wrong?

Kindest thanks,

SHawkz

 

[gmmastros]

So, in the same data, you don't want to show either row for Peter James because in 1 record he is 'out'? Do I understand you correctly?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AngelB]

I may be reading this as easier than it actually is, but could it be as simple as adding a line to your WHERE clause?

WHERE UserHistory.Type = 'In'

or if there are more than two possible values for this field then:

WHERE UserHistory.Type <> 'Out'


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[AngelB]

Sorry, I failed to notice that the user name was the same in the last two rows. I assume that there is one record for people who have not signed out, and two for people who have.

SELECT UserHistory.DateTime, Users.Name, Users.Surname, UserHistory.Type
FROM UserHistory INNER JOIN Users ON UserHistory.UserID = Users.UserID where UserHistory.Location = 'Warehouse' order by UserHistory.DateTime desc
GROUP BY UserHistory.DateTime, Users.Name, Users.Surname, UserHistory.Type
HAVING COUNT(*) = 1

Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[shawkz]

Thanks for replying... The table will eventually hold all the comings and goings for every employee. I just want to display who is currently in the warehouse (has not signed out yet) by the most recent time...

It will be for health and safety so at any point in time a web page can be displayed to see who is in the warehouse.

Hope this helps, i should of mentioned this the fisrt time...

Kindest regards,

Steve

 

[gmmastros]

Well... why didn't you say so.

This query should tell you what's going on:

Select UserId, Type
From   UserHistory
       Inner Join (
                  Select UserId, Max(DateTime) As MaxDateTime
                  From   UserHistory
                  ) As A
         On  UserHistory.UserId = A.UserId
         And UserHistory.DateTime = A.MaxDateTime

If you want to see who's In, then simply add a where clause to the end Where Type = 'In'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry, I missed something very important.

Select UserId, Type
From   UserHistory
       Inner Join (
                  Select UserId, Max(DateTime) As MaxDateTime
                  From   UserHistory
                  [!]Group By UserId[/!]
                  ) As A
         On  UserHistory.UserId = A.UserId
         And UserHistory.DateTime = A.MaxDateTime

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shawkz]

Thanks for replying so fast!!! i tried this and keep getting the following...

[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'UserID'.

Sorry for being a pest...

 

[gmmastros]

On of my pet peeves! Since we are inner joining the table with the subquery on UserId, the values must be the same, so it shouldn't matter which one it is coming from. But, since it does matter, you need to include the table name, like this...

Select [!]UserHistory.[/!]UserId, Type
From   UserHistory
       Inner Join (
                  Select UserId, Max(DateTime) As MaxDateTime
                  From   UserHistory
                  Group By UserId
                  ) As A
         On  UserHistory.UserId = A.UserId
         And UserHistory.DateTime = A.MaxDateTime

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shawkz]

Perfect thanks gmmastros i'd never figure this one out!!!

Kindest regards,

SHawkz

 

[shawkz]

Sorry, just noticed the query has excluded the Users.Name, Users.Surname from the other table i was joining onto.. How can i get this back?

Regards,

SH

 

[gmmastros]

Just add the users table with an inner join, like this...

Select UserHistory.UserId, 
       A.MaxDateTime,
       [!]Users.Name,
       Users.SurName,[/!]
       UserHistory.Type
From   UserHistory
       Inner Join (
                  Select UserId, Max(DateTime) As MaxDateTime
                  From   UserHistory
                  Group By UserId
                  ) As A
         On  UserHistory.UserId = A.UserId
         And UserHistory.DateTime = A.MaxDateTime
       [!]Inner Join Users
         On A.UserId = Users.UserId[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom