trying to report on some info

[itmontyp]

I have some simple data in a table

username - the users username
event - the date and time of an event
other bits

what i want to return from my query is the first event per day for each user, grouped by user.

I seem to be banging my head against a wall with this one

any ideas

Cheers

ITmontyp

So Long and thanks for all the fish

 

[ssecca]

TRy This...

SELECT CONVERT(varchar(11), datefld, 101) AS myDate, user1, MIN(datefld) AS datefld
FROM dbo.tbl_TEST
GROUP BY CONVERT(varchar(11), datefld, 101), user1

Good Luck

 

[SQLBill]

The query will depend on what data type Event is.

if it's DATETIME or SMALLDATETIME:

SELECT username, MIN(event)
FROM mytable
GROUP BY username

if it's VARCHAR, you'll first have to make sure the data is in a valid DATETIME format. Let's say all the date times are mm/dd/yyyy hh:mm AM (or PM)

SET DATEFORMAT m/d/y
SELECT username, MIN(CONVERT(DATETIME, event)
FROM mytable
GROUP BY username

If your Event column is not DATETIME or SMALLDATETIME, give us examples of the dates and times in that column.

-SQLBill

 

[ClaireHCM]

select * from cc where cc.tim in
(select min(a.tim) from cc a inner join cc on a.tim = cc.tim group by a.name,convert(varchar(12),a.tim,101))

name is the username
tim is the event (datetime )