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 |
|
bnoonan
Starting Member
2 Posts |
 Posted - 2011-09-07 : 12:57:03
|
| Hello,I have the following query below and I am looking for a date from a different table. Specifically, I want to know which was the last day the person worked before a holiday. The tables "Holidays" contains which day was the holiday for a specific person. The totals table would contain the other days worked. I am returning the day before the holiday that has values, however I want to exclude people if they have a vacation day, so for example if I include the following in the first 'where' statement:and t.code not in ('Vacation')and also place that in the sub select, I get no data returned. I am not sure what I am missing?? If I place it in either or, I get data but it would be wrong.Thank You!select t.name, t.id, t.job, t.hours, t.date, t.companyfrom totals t, holidays hwhere t.codetype = 'P'and t.id = h.idand t.date = (select max(t.date) from totals t, holidays hwhere t.date < h.dateand t.id = h.id)group by t.name, t.id, t.company,t.job, t.date |
|
|
headchef
Starting Member
1 Post |
Posted - 2011-09-08 : 15:19:50
|
| This might give you what you are looking for.Try this code:select a.name, a.id, a.job, a.hours, a.date, a.companyfrom (select t.name, t.id, t.job, t.hours, t.date, t.company, t.code from totals t, holidays h where t.codetype = 'P' and t.id = h.id and t.date = (select max(t.date) from totals t, holidays h where t.date < h.date and t.id = h.id) group by t.name, t.id, t.company, t.job, t.date )awhere a.code not in ('Vacation') |
 |
|
|
bnoonan
Starting Member
2 Posts |
Posted - 2011-09-12 : 12:20:58
|
| Thank you for the suggestion! |
|
|
|
|
|
|
|
|
Help w/ Query