|
Home > Archive > Microsoft SQL Server forum > August 2005 > Select unique
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| nbrcrunch 2005-08-25, 3:36 am |
| We have a transaction record that, for the sake of brevity, I will use
a simple paradigm to convey my need:
A sales clerk sells several pairs of shoes over the period of a day,
each & every day the clerk works. Each transaction is recorded in
a database. All clerks use a single physical register but login to
capture unique ID. As each clerk logs in, it automatically logs out
the prior user and writes a zero-dollar record.
Each record logs the RcdNbr, clerk's ID, date-timestamp and sales
amount. (Other fields not pertinent to this discussion)
Example:
Rcd...CID...yyyy-mmdd.hhmm...Sale
001...ABC...2005-0101.0850...10.00
002...ABC...2005-0101.0930...00.00
003...DEF...2005-0101.1000...15.51
004...DEF...2005-0101.1200...00.00
005...ABC...2005-0101.1300...12.83
006...ABC...2005-0101.1530...00.00
The above is unsorted. I would first sort by CID (ClerkID) to get:
Rcd...CID...yyyy-mmdd.hhmm...Sale
001...ABC...2005-0101.0850...10.00
002...ABC...2005-0101.0930...00.00
005...ABC...2005-0101.1300...12.83
006...ABC...2005-0101.1530...00.00
003...DEF...2005-0101.1000...15.51
004...DEF...2005-0101.1200...00.00
As a double-check of a clerk's actual worked hours we want to extract
the last record for each clerk for each day. In the above sample, I'd
need records 004 & 006.
The transaction file covers 6 months worth of data.
Is there a statement that I can construct that would extract the last
(greatest?) time for each clerk for each day?
| |
| nbrcrunch 2005-08-25, 3:36 am |
| bump one more (last) time.
| |
| Simon Hayes 2005-08-25, 7:23 am |
| Something like this?
select
CID,
convert(char(8), tmstamp, 112) as 'Day',
max(tmstamp) as 'Last Logout'
from
dbo.SomeTable
group by
CID,
convert(char(8), tmstamp, 112)
order by
'CID', 'Day'
Simon
|
|
|
|
|