Solved

Sql select Statemtn not working

Posted on 2009-05-13
22
174 Views
Last Modified: 2012-05-06
Hi I am trying to select people from one table based off dates in another table.
So I am selecting everyone from my recruit table that are eligible to ship, but then looking in tblACNRequest to see if they have a current request to ship, if they do i don't want o show them.  The new twist I added in is that there request can be canceled dtCancelAcn and if this is canceled I want to show them even if the dtExpire date is greater than cancel date.
Select	r.intRecruitID,

	r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname, 

	r.strSSN, 

	r.dtProcessing, 

	r.strMOS, 

	m.strMeps,

                      r.strAFQT 

from	tblRecruit as r Inner Join 

	tblMeps as m on m.intMepsId = r.intMepsId Where r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4) and 

	r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5) and

        intRecruitId  in (Select intRecruitId from tblACNRequest where dtExpire <= getdate()) or 

	intRecruitid in (Select intRecruitId from tblACNRequest where dtCancelAcn is not null) 

	Order by strLastName

Open in new window

0
Comment
Question by:kdeutsch
  • 11
  • 10
22 Comments
 
LVL 16

Expert Comment

by:brad2575
Comment Utility
should just be an issue of paren's
for your OR statement  try this
Select  r.intRecruitID,

        r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname, 

        r.strSSN, 

        r.dtProcessing, 

        r.strMOS, 

        m.strMeps,

                      r.strAFQT 

from    tblRecruit as r Inner Join 

        tblMeps as m on m.intMepsId = r.intMepsId Where r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4) and 

        r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5) and

        (

intRecruitId  in (Select intRecruitId from tblACNRequest where dtExpire <= getdate()) or 

        intRecruitid in (Select intRecruitId from tblACNRequest where dtCancelAcn is not null) 

)

        Order by strLastName

Open in new window

0
 

Author Comment

by:kdeutsch
Comment Utility
HI,
It still brings up someone who has been saved in the tblACNRequest  who has a dtExpire date of  <= today and the dtACnCancel date is null.

I am trying to pick all people from tblRecruit that do not have an active record in tblACNRequest.
The two ways they would have an active record is if dtExpire is greater than today's date or dtCancelACn is <= todays date, otherwise they canceled the record even though the dtexpire might be greater.
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
I think the reason brad2575's code didn't work is because (like in your original code) you're missing the table indicator.  In the last two lines of your WHERE clause, you should specify r.intRecruitId rather than just intRecruitId.

Regardless, take a look at my code below, which I think is a bit more efficient way of getting the info you want:


 
 

SELECT

	r.intRecruitID,

	r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,

	r.strSSN,

	r.dtProcessing,

	r.strMOS,

	m.strMeps,

	r.strAFQT 
 

FROM

	tblRecruit r,

	tblMeps m,

	tblACNRequest a
 

WHERE

	r.dtProcessing >= DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),4)

	AND r.dtProcessing < DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),5)

	AND r.intRecruitId = a.intRecruitId

	AND a.dtExpire <= GETDATE()

	AND ISNULL(a.dtCancelAcn,'') <> ''
 

ORDER BY

	r.strLastName ASC

Open in new window

0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
I do want to ask, however... what are you trying to accomplish with the first two lines of your WHERE clause?  I mean, what's the logic behind your DATEADD and DATEDIFF function calls?  I didn't touch either of those lines, and I suspect that code can be simplified greatly.  At the very least, since you're dealing with dates only (not time), there's no reason for you to specify the "12:00:00 AM"... Just put the date in as '10/7/2002' and you'll have the same effect.
0
 

Author Comment

by:kdeutsch
Comment Utility
Hi,
The dates that you are referencing are temp dates,   They will be getdates on production.  this was the only way to process them that I knew how, they are stored like thiis in field they come from.
2002-10-01 00:00:00.000  So i needed to add 4 days and catch everything on the 4th day.  There probably is a simpler way.
0
 

Author Comment

by:kdeutsch
Comment Utility
I ran the query and it comes up blank, it should come up with 13 records currently, ???
0
 

Author Comment

by:kdeutsch
Comment Utility
hi,
Ok if I take out my last recruitId statement, it works just fine, when I through it back in though, it does not matter whether I use   (And, or, in, not in, isNull, is not null)  it never gieves me the correct records.  Is there something else I can try????????   it should come up with records first where dtExpire <= todays dates.  Then it should grab records where dtACnCancel is not null or could be that dtACNCancel <= toadys date.  It is the second requirement that always throws it off.
0
 

Author Comment

by:kdeutsch
Comment Utility
I am wondering if these 2 statemetns are not canceling each other out.  here are the dates as stored in tbl
           dtExpire                                                   dtCancelACN
2009-05-19 00:00:00.000            2009-05-13 14:43:09.000
2009-05-19 00:00:00.000            2009-05-13 14:44:20.000
2009-05-19 00:00:00.000                            2009-05-13 14:47:33.000
So even though they might have canceled the action on  5/15 teh dtExpire would still not be less than today.  
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
Do this for me, please-- write a short description of each of the fields in your three tables (in other words, what's the point of each field?).  At the moment, I'm just finding it too hard to visualize what you're trying to do.
Thanks!
0
 

Author Comment

by:kdeutsch
Comment Utility
hi,
ok from tblRecruit which is my main table I am pulling from I am pulling the fcllowing information
intRecruitId, Name(combined), ssn, dtProcessed, MOS, AFQT.

Then I link into the next table to pull which location they need to show up to which is the tblMeps, I pull the strmeps which is linked back to tblRecruit via the intMepsId.

In teh above code dtProcessed is my first where clause because I only want personnel whom are going somewhere in the next 4 days, so i take todays date + 4.

Next i only want people whom have not been processed already, so I need to look in tblACNRequest to see whom has been processed.  The would be actively being processed if they have a dtExpire date int tblACNRequest of greater than today.  But I also want to see if they have canceled the existing request to put back in the field of personnel whom can be processed agian so this is where dtCancelACN comes in.  So i need to check for a dtExpire of less than today and see if the record was cancelled or not.  in this case the dtexpire might be greater than the dtCancelACN, which is what i think is throwing off the system.   HOpe this helped, because I am having a major brain drain on this.
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
I've got a few minutes to walk through your WHERE clause now.  However, I'm going to ignore the first two lines-- the DATEADD/DATEDIFF checks-- because I still don't understand the logic there.  But that's fine, if you know they work. :-)
Can you verify that those two lines are working properly?  If you haven't already, take everything out of FROM except tblRecruit r and take everything out of WHERE except those two lines.  Make sure you're getting results that make sense with only having those criteria.
I'll be back to you in a bit about the rest of it!
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
So what you're saying is that if the person has a dtExpire less than today AND dtCancelACN is not null (meaning they've not been canceled), you want them in your results.
Well, that's what the SQL above says.
However, I did just find an error in the SQL I put up in my first post.  Normally, I would think this error would result in more results rather than fewer (or none), but let's go ahead and try the fixed code anyway and see if it fixes your problem:

SELECT

	r.intRecruitID,

	r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,

	r.strSSN,

	r.dtProcessing,

	r.strMOS,

	m.strMeps,

	r.strAFQT 

 

FROM

	tblRecruit r,

	tblMeps m,

	tblACNRequest a

 

WHERE

	r.dtProcessing >= DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),4)

	AND r.dtProcessing < DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),5)

	AND m.intMepsId = r.intMepsId 

	AND r.intRecruitId = a.intRecruitId

	AND a.dtExpire <= GETDATE()

	AND ISNULL(a.dtCancelAcn,'') <> ''

 

ORDER BY

	r.strLastName ASC

Open in new window

0
 

Author Comment

by:kdeutsch
Comment Utility
Hi,
Sorry if I misstyped,  if they have a dtexpire greater than today and dtCancelACN has a date they need to be shown in datagrid because their recored was canceled and I need to show them agian.  But I do not need to show personnel whos dtexpire date is greater than today but there cancel date is null.
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
Gotcha.  So what you're saying is you want to take the following two lists and combine them:
  • anyone whose dtExpire is less than today (regardless of what dtCancelACN equals)
  • anyone whose dtCancelACN is not null (regardless of what dtExpire equals)
It's funny, but I noticed your use of OR at the end of the WHERE clause in your first post.  But with your explanation (which I obviously misunderstood), I thought it should have been AND.  You may have been pretty close originally... it's just that when you use an OR, the two conditions you use it between need to be enclosed in parentheses.
Regardless, the following SQL should be much more efficient than what you started with:
 

SELECT

	r.intRecruitID,

	r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,

	r.strSSN,

	r.dtProcessing,

	r.strMOS,

	m.strMeps,

	r.strAFQT 

 

FROM

	tblRecruit r,

	tblMeps m,

	tblACNRequest a

 

WHERE

	r.dtProcessing >= DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),4)

	AND r.dtProcessing < DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),5)

	AND m.intMepsId = r.intMepsId 

	AND r.intRecruitId = a.intRecruitId

	AND ( a.dtExpire <= GETDATE()

	       OR ISNULL(a.dtCancelAcn,'') <> ''

	)

 

ORDER BY

	r.strLastName ASC

Open in new window

0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
If you still have problems, I suggest going through and making sure you've got correct (consistent) capitalization on all of your table and field names.  I've noticed you aren't consistent with capitalization, and depending on your database settings, that may cause a problem without causing an error message.  I'm not expert enough on SQL Server to rule that out as a possibility. :-)
0
 

Author Comment

by:kdeutsch
Comment Utility
HI,
See attached file
Help.txt
0
 
LVL 8

Accepted Solution

by:
Bobaran98 earned 450 total points
Comment Utility
Okay.  You cannot intlude this in your WHERE clause...
AND (intRecruitId in (Select intRecruitId from tblACNRequest where dtExpire <= getdate()))
...and expect to get correct results, because you're requiring that every intRecruitId in your final result set also exist in tblACNRequest.  However, you've said there are only four records in all of tblACNRequest.
Perhaps you need to think of it this way:  I want all fourteen people from my original query to show up, EXCEPT:
  • anyone who is in tblACNRequest AND has a dtExpire > today
  • anyone who is in tblACNRequest AND has an active (non-canceled) record
If THAT's what you're looking for, check out the code below! :-)  If not, correct me.  But regardless, we're getting closer!


SELECT

	r.intRecruitID, 

	r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname, 

	r.strSSN, 

	r.dtProcessing, 

	r.strMOS,

        m.strMeps, 

	r.strAFQT 
 

FROM

	tblRecruit as r Inner Join 

	tblMeps as m on m.intMepsId = r.intMepsId 
 

WHERE

	r.dtProcessing >= DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),4) 

	AND r.dtProcessing < DATEADD(d, DATEDIFF(d, 0, '10/7/2002 12:00:00 AM'),5)

	AND r.intRecruitID NOT IN (SELECT intRecruitId FROM tblACNRequest WHERE dtExpire > GETDATE())

	AND r.intRecruitid NOT IN (SELECT intRecruitId FROM tblACNRequest WHERE ISNULL(dtCancelAcn,'')='')
 

ORDER BY

	r.strLastName

Open in new window

0
 

Author Comment

by:kdeutsch
Comment Utility
Hi,
I think we are backwards, I want to include everyone with a dtExpire of <= today and if their record was cancelled I want to show them as well.

So what I did was change the dtExpire date to less than or equal to and did not mess with the dtCancelACn and it seems to be working.  I tested by adding and canceling multiple files and it seems to work.  I see that you changed them to not in, which I tried but never got the results i wanted.  
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
I'm surprised it's working... if you use IN, then it requires that all of your results to also exist in the tblACNRequest table, which you've already said they don't.  By using NOT IN, I'm creating an exception clause rather than another requirement.

As for the logic of that, you remember the old maxim about two negatives making a positive?  Well, that's why I changed it to using a greater than, because I was using a NOT IN.  With the code I've posted most recently, what I'm essentially saying is Give me all of these results except

do NOT give me anyone who is IN tblACNRequest if their dtExpire is greater than today

AND

do NOT give me anyone who is IN tblACNRequest if their dtCancelAcn is empty

Whether or not I got that logic right, the NOT IN is required for what you want to do.  Using IN will require that all of your results be listed in tblACNRequest, either with a dtExpire less than or equal to today or a date in the dtCancelAcn field.

If you appear to be getting correct results using IN, let me ask you-- in the course of your testing, have you maybe added a bunch of entries to the tblACNRequest table?
0
 

Author Comment

by:kdeutsch
Comment Utility
Hi,
I used you last code with the Not In statements, the only thing I changed was the dtExpire <= today instead of greater than.  Now I understand more about IN statements and NOT IN, it was confusing me of what they actually did, now I know better.  I have added entries in my test environment to make sure it was all working.  When I cancel records from the tblACNRequest the personnel pop back into the process cycle, but they do not appear if they have a current record in tblACNrequest.  Thanks for your paitenence with me.
0
 

Author Closing Comment

by:kdeutsch
Comment Utility
Thanks
0
 
LVL 8

Expert Comment

by:Bobaran98
Comment Utility
It was my pleasure.  Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now