NOT BETWEEN is not working - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

NOT BETWEEN is not working

Author

Topic

LOLCatLady
Starting Member

24 Posts

Posted - 2011-11-01 : 16:54:34

Can someone tell me why the "AND gifteffdat NOT BETWEEN '03/01/2008' AND '02/28/2009'" (the last line in this code section) is not limiting the records by those dates?

Running this code still returns records that show gifts dated between the dates 3/1/08 and 2/28/09. The records do match the criteria in the INNER JOIN. Why doesn't the NOT BETWEEN exclude those records that match it's criteria? Is there a way to code it so it will exclude those records?

Any help will be appreciated!

SELECT distinct nameid, rtrim(nameformn) as nameformn, rtrim(addrcntry) as addrcntry,
giftRange.solicitation, @description as description

FROM dbo.corebio
INNER JOIN dbo.address WITH (NOLOCK) ON coreid = addrid
INNER JOIN dbo.name WITH (NOLOCK) ON nameid = coreid
INNER JOIN dbo.gifts WITH (NOLOCK) ON giftid = nameid
INNER JOIN
(SELECT giftid, SUM(giftdeduct) AS cumGift
FROM dbo.gifts WITH (NOLOCK)
WHERE gifteffdat BETWEEN '03/01/2007' AND '02/29/2008' --gifts FY08
AND giftid not in (select giftid from gifts WITH (NOLOCK)
where gifteffdat BETWEEN '03/01/2005' AND '02/28/2007') --no gifts FY07, FY06
GROUP BY giftid) as cumGiving on cumGiving.giftid = coreid

INNER JOIN (select @solav1 as solicitation, @sol1Min as minVal, @sol1Max as maxVal
union select @solav2, @sol2Min, @sol2Max
union select @solav3, @sol3Min, @sol3Max
union select @solav4, @sol4Min, @sol4Max
union select @solav5, @sol5Min, @sol5Max
union select @solav6, @sol6Min, @sol6Max
union select @solav7, @sol7Min, @sol7Max
) as giftRange ON (cumGiving.cumGift >= giftRange.minVal) AND (cumGiving.cumGift <= giftRange.maxVal)

WHERE addrcntry = 'USA'--include only US addresses
AND cumgiving >= 0.01
AND creationdate <= '03/01/2006'
AND gifteffdat NOT BETWEEN '03/01/2008' AND '02/28/2009'

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-01 : 16:57:54

AND convert(varchar,gifteffdat,112) < '20080301' AND convert(varchar,gifteffdat,112)>'20090228'

LOLCatLady
Starting Member

24 Posts

Posted - 2011-11-01 : 17:03:09

quote:
Originally posted by vikki.seth

AND convert(varchar,gifteffdat,112) < '20080301' AND convert(varchar,gifteffdat,112)>'20090228'

I changed it to your suggestion, but now I don't get ANY records!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 18:36:55

what is the data type for gifteffdat ?

KH
[spoiler]Time is always against us[/spoiler]

LOLCatLady
Starting Member

24 Posts

Posted - 2011-11-02 : 11:23:04

quote:
Originally posted by khtan

what is the data type for gifteffdat ?

KH
[spoiler]Time is always against us[/spoiler]

It's datetime. The problem I'm having is that the data returned matches this inner join criteria:
INNER JOIN
(SELECT giftid, SUM(giftdeduct) AS cumGift
FROM dbo.gifts WITH (NOLOCK)
WHERE gifteffdat BETWEEN '03/01/2007' AND '02/29/2008' --gifts FY08
AND giftid not in (select giftid from gifts WITH (NOLOCK)
where gifteffdat BETWEEN '03/01/2005' AND '02/28/2007') --no gifts FY07, FY06
GROUP BY giftid) as cumGiving on cumGiving.giftid = coreid.

This pulls the data to segment according to this inner join:
INNER JOIN (select @solav1 as solicitation, @sol1Min as minVal, @sol1Max as maxVal
union select @solav2, @sol2Min, @sol2Max
union select @solav3, @sol3Min, @sol3Max
union select @solav4, @sol4Min, @sol4Max
union select @solav5, @sol5Min, @sol5Max
union select @solav6, @sol6Min, @sol6Max
union select @solav7, @sol7Min, @sol7Max
) as giftRange ON (cumGiving.cumGift >= giftRange.minVal) AND (cumGiving.cumGift <= giftRange.maxVal)

What I want the query to do with this: AND gifteffdat NOT BETWEEN '03/01/2008' AND '02/28/2009' --no gifts FY09
is to limit the select above by those records who meet this criteria.

I don't know how (and at this point, IF) that can be done.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 20:23:54

try this,

include the gifteffdat in your select clause and run the query and see any record with gifteffdat that is between '03/01/2008' AND '02/28/2009' in the result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources