November 2, 2011 at 1:26 pm
Hi, I'm new here and hoping someone could give me direction on how to make this code work.
The INNER JOIN below with the select is pulling the correct data, however, I want that data to be limited by an additional date range, which is the "NOT BETWEEN" code (the last line.) I get the same data with that line as without. Is there any way to code this to limit the data by that date range as well as the "not in" date range in the INNER JOIN select? (I hope this makes sense!:w00t:)
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
AND giftid not in (select giftid from gifts WITH (NOLOCK)
where gifteffdat BETWEEN '03/01/2008' AND '02/28/2009')--no gifts FY09
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 nametype = 'a'--preferred name (this select the primary name for the donor entity)
AND addrmc1 = 'Y'--preferred address (this selects the primary address for the donor entity)
AND addrlocatr not in ('l ','r','form','d','am')--exclude lost, returned, former, deceased, address missing
AND addrmc2 = 'N'--System send no mail (exclude)
AND addrmc3 = 'N'--Req no postal mail (exclude)
AND addrmc4 = 'N'--send no appeals (exclude)
AND addrmc7 = 'N'--Send Appeal 4x/yr: Mar, Jun, Sep, Dec
AND addrmc8 = 'N'--Send Appeal 2x/yr: Jun, Dec
AND addrmc9 = 'N'--Send Appeal 1x/yr: Dec
AND addrfeb = 'Y'--effective month (include valid addresses for the correct month)
AND nameid not in (select distinct attrid from dbo.attribute where attrtype in ('59-102','59-193') and attrstop is null)--excludes Elec Fund Transfer Donor and Credit Card Donor gifts
AND nameid not in (select distinct attrid from dbo.attribute where attrtype in (
'59-636',--foundation board member
'59-189',--global board member
'59-095',--staff member
'59-099'--US Board Member
)) --excludes staff
AND coreconst in ('I','T','B')--include only individual, trusts and businesses
AND addrcntry = 'USA'--include only US addresses
AND cumGift >= 0.01
AND corecrdate <= '03/01/2006'
AND gifteffdat NOT BETWEEN '03/01/2008' AND '02/28/2009'--no gifts FY09
I'm currently trying this in the INNER JOIN select:
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
AND giftid not in (select giftid from gifts WITH (NOLOCK)
where gifteffdat BETWEEN '03/01/2008' AND '02/28/2009')--no gifts FY09
GROUP BY giftid) as cumGiving on cumGiving.giftid = coreid
but it's been running now for over 30 minutes, whereas the other way the query completes in about 6 minutes (incorrectly, though.) :crazy: I'm almost at the point where I'm thinking that this isn't going to work at all and I have to start over.
Thanks in advance for any help! 🙂
November 2, 2011 at 1:57 pm
AND not (gifteffdat BETWEEN '03/01/2008' AND '02/28/2009') --no gifts FY09
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 2, 2011 at 3:02 pm
I'm sorry, but that didn't limit the data - it returned the same data as before. The section of code I changed to this:
(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
AND giftid not in (select giftid from gifts WITH (NOLOCK)
where gifteffdat BETWEEN '03/01/2008' AND '02/28/2009')--no gifts FY09
GROUP BY giftid) as cumGiving on cumGiving.giftid = coreid
worked, but it took an hour and 38 minutes to run. But, it did return the correct data.
Thanks for replying!
November 2, 2011 at 6:21 pm
LOLCatLady (11/2/2011)
I'm sorry, but that didn't limit the data - it returned the same data as before. The section of code I changed to this:(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
AND giftid not in (select giftid from gifts WITH (NOLOCK)
where gifteffdat BETWEEN '03/01/2008' AND '02/28/2009')--no gifts FY09
GROUP BY giftid) as cumGiving on cumGiving.giftid = coreid
worked, but it took an hour and 38 minutes to run. But, it did return the correct data.
Thanks for replying!
Changing that to something like this might help with performance:
(SELECT giftid, SUM(giftdeduct) AS cumGift
FROM dbo.gifts as g1 WITH (NOLOCK)
WHERE gifteffdat BETWEEN '03/01/2007' AND '02/29/2008'--gifts FY08
AND not exists (
select 1
from gifts as g2 WITH (NOLOCK)
where g1.giftid = g2.giftid
and ((gifteffdat BETWEEN '03/01/2005' AND '02/28/2007')--no gifts FY07, FY06
or (gifteffdat BETWEEN '03/01/2008' AND '02/28/2009'))--no gifts FY09
)
GROUP BY giftid) as cumGiving on cumGiving.giftid = coreid
November 3, 2011 at 8:57 am
Thanks for code. It ran quickly, but did not return the correct data. Since I did find the coding to get the correct data, I can live with it being slow, for now, anyway.
I appreciate your help! 🙂
November 3, 2011 at 10:01 am
No problem. If you want to provide a script to create the table structures and populate with sample data, I can take another stab at it. My suspicion is that your performance issue is due to the multiple NOT IN subqueries. At least check the definition of the "giftid" column; is it declared NOT NULL, or is it nullable? If you try to execute a NOT IN subquery against a nullable column, even if the column doesn't actually contain any nulls, it can have a serious negative impact on performance. I recently dealt with a query that was taking over 12 hours to run; by doing nothing but just changing a couple of NOT IN subqueries to NOT EXISTS logic, it cut the time down to under 3 minutes.
Again, if you want to provide some scripts, I'll be happy to take another stab at rewriting that clause. Just be aware that if it's taking a significantly long time to run, it may be incurring a fair amount of unnecessary overhead on the server (causing excessive I/O, etc.) and may negatively impact the performance of other processes accessing that server.
Try running the query with SET STATISTICS IO ON and see if the reads look excessive, and take a look at the actual execution plan and check to see if it's doing something wildly inefficient..
November 7, 2011 at 11:02 am
LOLCatLady (11/2/2011)
Hi, I'm new here and hoping someone could give me direction on how to make this code work.The INNER JOIN below with the select is pulling the correct data, however, I want that data to be limited by an additional date range, which is the "NOT BETWEEN" code (the last line.) I get the same data with that line as without. Is there any way to code this to limit the data by that date range as well as the "not in" date range in the INNER JOIN select? (I hope this makes sense!:w00t:)
-- edited the inner join formatting, but code is the same
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
AND giftid not in (select giftid from gifts WITH (NOLOCK) where gifteffdat BETWEEN '03/01/2008' AND '02/28/2009') --no gifts FY09
GROUP BY giftid
) as cumGiving
ON cumGiving.giftid = coreid
After reviewing your query with a little more attention, I believe I see an issue based on your request as quoted above. How exactly do you need to limit the data based on those date ranges? Does the "giftid" need to exist only in the FY08 timeframe or can it have additional entries in the other time periods?
It appears the "AND gifteffdat NOT BETWEEN '03/01/2008' AND '02/28/2009' --no gifts FY09" you add to the where clause will not get used due to exclusion that results in the "INNER JOIN" clause with the two NOT IN sub-query clauses. Your added inner join appears to pull only the "giftid" items having an entry in FY08 only and that do not have an entry between either of the other two date ranges.
November 22, 2011 at 3:34 pm
I apologize for not getting back very quickly, I had to stop working on this project and start something else. :w00t: The giftid column is not nullable, it's a key field, so that's probably not the issue. I appreciate your offer to take a look but since I have it working, albeit slowly, I can at least finish this project and then maybe play with it here and there to figure out the efficiency issue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply