CODEWITHSUNDEEP
sql-serversql-server-2008
Apollo
Apollo

Reputation: 2070

Query in SqlServer for Users Currently logged in

I have a tblData where users login. In the table they can be either IN or OUT. What I want is to display the users that are currently logged in.

enter image description here

Upvotes: 0

Views: 127

Answers (4)

Sonam
Sonam

Reputation: 3466

SELECT 
ID,MAX(QDateTime)  FROM tblData T1 WHERE [Type] = 'IN' AND  QDateTime>
    (SELECT max(QDateTime) FROM tblData T2 WHERE T1.ID=T2.ID AND [TYPE]='OUT')
GROUP BY
ID
UNION
SELECT 
ID, QDateTime  FROM tblData T1 WHERE [Type] = 'IN' AND NOT EXISTS
    (SELECT  QDateTime FROM tblData T2 WHERE T1.ID=T2.ID AND [TYPE]='OUT')

Upvotes: 0

Sonam
Sonam

Reputation: 3466

SELECT 
ID,MAX(convert(datetime,DATE)+' '+TIME)  FROM tblData T1 WHERE [Type] = 'IN' AND   convert(datetime,DATE)+' '+TIME>
    (SELECT max(convert(datetime,DATE)+' '+TIME) FROM tblData T2 WHERE T1.ID=T2.ID AND [TYPE]='OUT')
GROUP BY
ID
UNION
SELECT 
ID,convert(datetime,DATE)+' '+TIME  FROM tblData T1 WHERE [Type] = 'IN' AND NOT EXISTS
    (SELECT convert(datetime,DATE)+' '+TIME FROM tblData T2 WHERE T1.ID=T2.ID AND [TYPE]='OUT')

Upvotes: 0

bummi
bummi

Reputation: 27384

Declare @a table ([Date] date,[Time] Time(7),[Type] varchar(10),ID int, Location int)

Insert into @a Select '20130101','10:00','IN',280,2
union Select '20130101','11:00','IN',280,2
union Select '20130101','12:00','OUT',280,2
union Select '20130101','12:00','IN',111,2
union Select '20130101','12:00','OUT',1,2
union Select '20130101','13:00','IN',1,2


Select * from @a a2
JOIN
(
Select MAX(Cast([Date] as dateTime)+[Time]) DT,ID
from @a
Group by ID
) b
on b.ID=a2.ID and Cast(a2.[Date] as dateTime)+a2.[Time] = b.DT
Where a2.Type='IN'

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15399

SELECT *
FROM tblData E
WHERE E.date = convert(date, getdate())
and convert(time, E.time) <= convert(time, getdate())
and E.type = 'IN'
not exists(
    select 'x' from tblData U
    where U.id = E.id
    and U.type = 'OUT'
    and E.date = U.date
    and U.time > E.time
    and convert(time, U.time) <= convert(time, getdate()))

Upvotes: 1

Related Questions