Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databasesWe 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?
Post Follow-up to this messageSomething 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
Post Follow-up to this message
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread