Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql select Statemtn not working

Posted on 2009-05-13
22
Medium Priority
?
185 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
ID: 24378524
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
ID: 24378676
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
ID: 24381006
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 8

Expert Comment

by:Bobaran98
ID: 24381016
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
ID: 24384177
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
ID: 24384207
I ran the query and it comes up blank, it should come up with 13 records currently, ???
0
 

Author Comment

by:kdeutsch
ID: 24384539
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
ID: 24384618
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
ID: 24385604
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
ID: 24386387
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
ID: 24387863
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
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24387947
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
ID: 24388185
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
ID: 24388450
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
ID: 24388483
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
ID: 24388626
HI,
See attached file
Help.txt
0
 
LVL 8

Accepted Solution

by:
Bobaran98 earned 1800 total points
ID: 24389738
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
ID: 24395332
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
ID: 24396386
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
ID: 24397119
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
ID: 31581175
Thanks
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24399210
It was my pleasure.  Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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