Query for first record for each user for each day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query for first record for each user for each day

Author

Topic

t3kboi
Starting Member

3 Posts

Posted - 2009-03-12 : 12:32:14

I found a very similar post on this forum that I am using as a basis for my query:

I want to show just the first timecard access each day, for each user, for every day that exists in the database.

Fields:
LastName, Initials, Hostdate (float for datetime), TDate(float converted for date value), TStamp(float converted for Time Value)

My current query works great, but it is returning every timecard access each day, instead of just the first one for each user. This output is great, but I only want the first record for each user each day, and am stuck on what to do next.

Current Query:

SELECT
LastName,
Initials,
CONVERT(Varchar, CAST([HostDate] AS DATETIME), 1) As TDate,
CONVERT(Varchar, CAST([HostDate] AS DATETIME), 108)As TStamp

FROM

(
SELECT ROW_NUMBER() OVER(PARTITION BY Floor(Hostdate) ORDER BY HostDate) AS FSeq,*
FROM BadgeHistoryTable
) t

WHERE
--t.FSeq=1
LastName IN ('List of user names here')

Order by HostDate

Note: If I uncomment the t.FSeq=1, and add the AND Lastname... then my results return the first access for each day (also close to what I want), but I need the first access for each user, every day.

The Floor(Hostdate) Rolls up all the multiple time values for a single day into a single return entry - there may be another way to do this.

Regards,

t3kboi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 12:43:05

[code]
SELECT
LastName,
Initials,
CONVERT(Varchar, CAST([HostDate] AS DATETIME), 1) As TDate,
CONVERT(Varchar, CAST([HostDate] AS DATETIME), 108)As TStamp

FROM

(
SELECT ROW_NUMBER() OVER(PARTITION BY LastName,Initials,Floor(Hostdate) ORDER BY HostDate) AS FSeq,*
FROM BadgeHistoryTable
) t

WHERE
t.FSeq=1
AND LastName IN ('List of user names here')

Order by HostDate
[/code]

t3kboi
Starting Member

3 Posts

Posted - 2009-03-12 : 13:56:30

Thanks for the quick response!!

But, the new query yields the same result set.

Possibly my problem is that the [HostDate] field is a float containing both the date part and the time part.

So when I try to partition by just the date part, I still get one record for each date part that has a different time component.

I was trying to use Floor to make all of the same date parts appear as the same date part.

Do I need to send this to a temp table with a created date column, and then partition by date? or some such?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 14:03:35

why is date stored as a float value? also whats the logic applied in conversion?

t3kboi
Starting Member

3 Posts

Posted - 2009-03-12 : 16:42:21

The data comes from a proprietary application, that for some reason used a float in the field definition for hostdate instead of datetime. (It is a sql 2000 MSDE instance - I have copied it to a SQL 2005 Server so I can use the rownumber function).

I am converting the float value as in the query above where [Hostdate] is the field with the float.: CONVERT(Varchar, CAST([HostDate] AS DATETIME), 1) As TDate

A side effect of converting float to datetime, is that all dates are two days ahead of where they should be, i.e. 3/11 is shown as 3/13

One day could be accounted for by rounding, but 2 days?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources