Select most recent record when date and time are separate fields

Question

0.00/5 (No votes)

See more:

Hi,

I have a table with the following columns:

UserName, Date, Time, LoginStatus

The column types are:
UserName = varchar
Date = Date
Time = Time
LoginStatus = bit

People log in/out and these instances are captured in the table.

I would like to retrieve the most recent record for each person (UserName) in the table.

Thanks.

Posted 24-Apr-15 1:22am

Andrw_S

Add a Solution

Comments

Andrw_S 27-Apr-15 4:02am

This is not a solution to the original condition, however I shall add this in case it happens to be of use to anyone in a similar spot.

If you also have a primary ID column in there, then the below works:

Select
m.ID,
m.UserName,
l.Date,
l.Time,
l.LoginStatus
From LoginStatus l
inner join
(
Select
max(LoginStatusID) [ID],
UserName
From LoginStatus
group by
UserName
) m
on l.LoginStatusID=m.ID and l.UserName=m.UserName
Order by
m.UserName

3 solutions

Solution 1

See here: http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group[^]
But use

SQL

MAX(CAST([Date] AS datetime) + CAST([Time] AS datetime))

for the condition.

Posted 24-Apr-15 1:41am

OriginalGriff

Comments

Andrw_S 24-Apr-15 8:43am

Hi,
So if you put together a query that looks something like this:

Select
UserName,
MAX(CAST([Date] AS datetime) + CAST([Time] AS datetime)) [Last Entry],
LoginStatus
from LoginStatus
Group By
UserName,
LoginStatus
order by
UserName

Then you end up getting TWO results per person. One each for loginStatus=1 and loginStatus=0.

I've got this far with several queries, but not managed that last step of retrieving only the most recent record.
Thanks.

OriginalGriff 24-Apr-15 8:56am

So you didn't bother reading the link at all then...

Andrw_S 24-Apr-15 9:11am

Hi,
Yes, I did.
It's very similar to:
http://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records

Solution 2

Here's a method I honestly haven't tried (haven't needed to) - but why not put the two fields together, as one properly formatted datetime field and select on that.

This will require some casting to format the two fields into appropriate date-time formats into varchar's and then cast the two fields (after concatenating them so they're properly formatted) to datetime -> and find the max, order-by, etc., on your virtual field.

Posted 24-Apr-15 1:42am

W∴ Balboos, GHB

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pratap420 · Accepted Answer · 2015-04-24T06:04:00

Hi,
try this

SQL

Declare @t table (UserName varchar(10),_date date ,_time time , LoginStatus bit )

insert into @t
values
('user1 ',GETDATE()-1,'12:30',0),('user1 ',GETDATE()-1,'13:30',1),('user1 ',GETDATE()-1,'14:30',0),
('user2 ',GETDATE(),'14:30',0),('user2 ',GETDATE(),'15:30',1),('user2',GETDATE(),'16:30',0),('user2',GETDATE(),'17:30',1)

--Select * from @t

Select UserName,_date,_time,LoginStatus
from
( Select * ,ROW_NUMBER() over (partition by username order by  _date,_time desc ) rk
from @t) A
where rk=1

output is

CSS

UserName    _date   _time   LoginStatus
user1   2015-04-23  14:30:00.0000000    0
user2   2015-04-24  17:30:00.0000000    1