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 |
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
 Posted - 2009-03-09 : 09:19:54
|
I have a table called Ladetail which holds labor details. I need to find all the job numbers (fjobno) for which the fdate (datetime) field has labor on a certain day and no labor on any other day. The base query would be something like: select fjobno from ladetail where fdate = '3/05/2009 12:00:00 AM' How do I pull this off? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-09 : 09:41:53
|
Not sure if I understand your question correctly. May be this,,select fjobno from ladetail group by fjobno having count(distinct DATEADD(dd, 0, DATEDIFF(dd, 0, fdate )) )=1 |
 |
|
|
DavidChel
Constraint Violating Yak Guru
474 Posts |
Posted - 2009-03-09 : 11:46:23
|
| I'm sorry, I tried to explain this as well as I could. Let me try this: If a job has labor on it for any other day than '3/05/2009' I don't want it included. I only want jobs for which labor is recorded on that day and no other day. Does that help? |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-09 : 11:52:42
|
| [code]declare @datevar datetimeset @datevar='3/05/2009 12:00:00 AM'select distinct a.fjobno from ladetail a where a.fdate =DATEADD(dd, 0, DATEDIFF(dd, 0, @datevar )) and not exists (select 1 from ladetail where fjobno=a.fjobno and fdate<>DATEADD(dd, 0, DATEDIFF(dd, 0, @datevar )))[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 02:48:14
|
quote:
Originally posted by DavidChel
I'm sorry, I tried to explain this as well as I could. Let me try this: If a job has labor on it for any other day than '3/05/2009' I don't want it included. I only want jobs for which labor is recorded on that day and no other day. Does that help?
where's labor data stored? how will you link it to job? |
|
|
|
|
|
|
|
|
Subquery Help