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.
| Author |
Topic |
|
RSewell
Starting Member
5 Posts |
 Posted - 2005-06-01 : 15:42:47
|
Hi, folks. I've racked my brain and worn holes in Query Analyzer, but just can't figure this one out.  I hope you more experienced gurus can help. Here's the data:Employees:eid ename ext 1 Jack 310 2 Jane 315 3 Jill 312 TimeLog:eid timestamp status ikey 2 05/31/2005 17:00 OUT 19 1 05/31/2005 17:02 OUT 20 3 06/01/2005 07:50 IN 21 1 06/01/2005 07:58 IN 22 1 06/01/2005 11:33 LUNCH 23 3 06/01/2005 12:00 OUT 24 1 06/01/2005 12:30 IN 25 I need the status and timestamp for the last entry in table Timelog for each employee for the current day, plus employee data such as name, phone extension. Each employee should have an entry even if he/she has no entry for the day.So, given the above entries, on 06/01/2005 my query should return:Jack, 310, IN, 06/01/2005 12:30Jane, 315, <null>, <null>Jill, 312, OUT, 06/01/2005 12:00Any ideas?Thanks in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jaymedavis
Starting Member
12 Posts |
Posted - 2005-06-01 : 15:50:52
|
| select ename, ext, status, timestamp, ikey from employees e join timelog t on e.eid = t.eid where t.timestamp in (select max(timestamp) from timelog where eid = e.eid)That should do it...Jayme |
 |
|
|
jaymedavis
Starting Member
12 Posts |
Posted - 2005-06-01 : 15:57:45
|
| or rather... t.timestamp '=' (select max(timestamp) from timelog where eid = e.eid), not 'in'.Jayme |
|
|
|
RSewell
Starting Member
5 Posts |
Posted - 2005-06-01 : 16:04:55
|
| Jayme Davis... dang, you're good. I think that works. Thank you! When you need to figure out the proper query for a complex problem like that (well, complex for me, anyway) how do you go about finding the solution?tduggan, I'd be glad to post it in that format, but doesn't the sample data make that unnecessary? Or is that to make it easier for people to put in their SQL Servers to test their potential answers on? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-01 : 16:08:50
|
quote:
Originally posted by RSewell
[b]tduggan, I'd be glad to post it in that format, but doesn't the sample data make that unnecessary? Or is that to make it easier for people to put in their SQL Servers to test their potential answers on?
Yes it's so that we can pull it onto our machines and test it out. You can't expect that we type all of this up especially since we are offering help for free. If you post it in the form of DDL and DML, you might have a few people working on your problem at the same time. If you don't, you'll be lucky to find one person willing to do all of the typing to get it to work for our environments.Tara |
|
|
|
RSewell
Starting Member
5 Posts |
Posted - 2005-06-01 : 16:15:51
|
That makes sense. I would never expect anyone (except my mother  ) to go to that kind of trouble for free. I guess I thought that most folks knowledgable and/or experienced enough to attempt an answer would be able to "see" the solution without having to do all that. Someone like me wouldn't be able to figure it out even after putting the data into a DB... |
|
|
|
jaymedavis
Starting Member
12 Posts |
Posted - 2005-06-01 : 16:32:39
|
| Finding a solution for a complicated query is just a matter of asking questions. Be sure when you have an answer to your problem that you understand how it's working. That's the most important part. Feel free to ask questions if you don't understand. Following those guidelines, the 'solutions for complex problems' work themselves out. :)As for your current issue, I did just eye-ball it. However, the possibility existed that I could have messed up the query (I just typed it in the reply window) so the DDL/DML makes sense and probably helps a lot of folks (especially if it's more complicated).Jayme |
|
|
|
RSewell
Starting Member
5 Posts |
Posted - 2005-06-01 : 16:59:39
|
| Jayme, your solution is perfect. The only thing still lacking is that it doesn't return a result for anyone who doesn't have an entry for the current day, even if I make it a left join. I wonder why?For anyone else thinking of getting into the game (thank you!), here's the DDL/DML:CREATE TABLE [dbo].[Employees] ( [eid] [smallint] IDENTITY (1, 1) NOT NULL , [ename] [varchar] (40) , [ext] [varchar] (14) ,) ON [PRIMARY]CREATE TABLE [dbo].[TimeLog] ( [eid] [smallint] NOT NULL , [logtimestamp] [smalldatetime] NOT NULL , [status] [varchar] (15) , [ikey] [int] IDENTITY (1, 1) NOT NULL ) ON [PRIMARY]INSERT INTO Employees (ename, ext) values ('Jack', '310')INSERT INTO Employees (ename, ext) values ('Jane', '315')INSERT INTO Employees (ename, ext) values ('Jill', '312')INSERT INTO TimeLog (eid, logtimestamp, status) values (2, '05/31/2005 05:00 PM', 'OUT')INSERT INTO TimeLog (eid, logtimestamp, status) values (1, '05/31/2005 05:02 PM', 'OUT')INSERT INTO TimeLog (eid, logtimestamp, status) values (3, '06/01/2005 07:50 AM', 'IN')INSERT INTO TimeLog (eid, logtimestamp, status) values (1, '06/01/2005 07:58 AM', 'IN')INSERT INTO TimeLog (eid, logtimestamp, status) values (1, '06/01/2005 11:33 AM', 'LUNCH')INSERT INTO TimeLog (eid, logtimestamp, status) values (3, '06/01/2005 12:00 PM', 'OUT')INSERT INTO TimeLog (eid, logtimestamp, status) values (1, '06/01/2005 12:30 PM', 'IN')Jayme's query is closer than anything I've come up with, so I'll offer that as a starting point. I changed the WHERE clause to search on ikey rather than logtimestamp because ikey is guaranteed to be unique (some users are managing to enter duplicate entries for the status and time stamp), and to select only entries from the current day (hard-coded to 6/1/2005 in this example):select ename, ext, status, logtimestamp, ikey from employees eleft join timelog t on e.eid = t.eidwhere t.ikey =(select max(ikey) from timelog where eid = e.eid)and logtimestamp >= '6/1/2005 00:00:00'and logtimestamp <= '6/1/2005 23:59:59'order by ename[edited to correct typos] |
|
|
|
jaymedavis
Starting Member
12 Posts |
Posted - 2005-06-01 : 17:10:20
|
| Ahh, that would be because the nested query is comparing results with the main query... you will have to move it up to the left join.(once again, I'm eye-balling, hehe)try this-select ename, ext, status, logtimestamp, ikey from employees eleft join timelog t on e.eid = t.eid AND t.ikey = (select max(ikey) from timelog where eid = e.eid)and logtimestamp >= '6/1/2005 00:00:00'and logtimestamp <= '6/1/2005 23:59:59'order by enameJayme |
|
|
|
RSewell
Starting Member
5 Posts |
Posted - 2005-06-01 : 17:24:15
|
| Well, your eyeballs are obviously much better than mine. That seems to work perfectly.Thank you! |
|
|
|
|
|
|
|
|
Getting Most Recent W/Two Tables