comparison query?

Options
I'm trying to create a query that shows me everyone who gave a single donation of 1k or more last year and who gave a single donation of 1k or more for the current year in order to find who the repeat givers are. The criteria I entered looked something like this:


[gift amount greater than or = 1k and gift date = January 1, 2017 - December 31, 2017] and [gift amount greater than or = 1k and gift date = January 1, 2018 - present]


The query always pulls zero results and I know for a fact there should be several. What am I doing wrong? Any help/insight would be greatly appreciated.


Best,

Aaron

 

Comments

  • I would take the criteria and divide it into two queries, one for each year. Then I would merge the two queries using the AND operator, then the merged query will pull all constituents that appear in both queries. 
  • Aaron Sorensen:

    I'm trying to create a query that shows me everyone who gave a single donation of 1k or more last year and who gave a single donation of 1k or more for the current year in order to find who the repeat givers are. The criteria I entered looked something like this:


    [gift amount greater than or = 1k and gift date = January 1, 2017 - December 31, 2017] and [gift amount greater than or = 1k and gift date = January 1, 2018 - present]


    The query always pulls zero results and I know for a fact there should be several. What am I doing wrong? Any help/insight would be greatly appreciated.


    Best,

    Aaron

     

    If you're needing ANY gift (not summary giving for a specific time period) then I don't think this will be possible to do with a single Constituent Query because when SQL looks at the Gift records a single Gift can't match both halves of your AND criteria.

  • Your criteria looks good to me so perhaps you can post a screenshot of the criteria as it appears in a query? The issue may be with your parentheitcal notation.


    This is how I would to it:


    f970172c714eb9f3d0cacab7f032f103-huge-qu



     
  • Whoops. John is right.
  • Marie Stark
    Marie Stark Community All-Star
    Ancient Membership 1,000 Likes 500 Comments Photogenic


    38c8552ed2f5646f7378c698e011c860-huge-qu

    What about adding 2 gift summary fields? for total number of gifts?  In each field criteria you can say Total  number of gifts greater than 1, and in the filter add the gift dates and gift amount

     

  • Marie Stark:


    38c8552ed2f5646f7378c698e011c860-huge-qu

    What about adding 2 gift summary fields? for total number of gifts?  In each field criteria you can say Total  number of gifts greater than 1, and in the filter add the gift dates and gift amount

     

     

    According to Blackbaud, you have to use two different queries to see results for summary giving on two different date ranges. https://kb.blackbaud.com/articles/Article/38648


    I used to use multiple gift summary fields in queries all the time. At some point over the last few years, queries with more than one gift summary started hanging for so long it wasn't worth waiting for results. AND merging queries with multiple gift summary fields can take just as long.


    Does anyone else have this issue? Did anyone else used to not have this issue?

  • Marie Stark:

     


    38c8552ed2f5646f7378c698e011c860-huge-qu

    What about adding 2 gift summary fields? for total number of gifts?  In each field criteria you can say Total  number of gifts greater than 1, and in the filter add the gift dates and gift amount

     

     

    Marie, I've had this both work and not work, so I don't tend to trust it anymore.  My vote is for merging the 2 queries on this one.

Categories