Advanced Search SQL Query - All Unpublished Reports Scheduled for the Next 30 days

Ken Knowlton
Ken Knowlton
Fourth Anniversary Kudos 1 Participant Facilitator 1
in Blackbaud Grantmaking™ General Discussions

Hi all -

I've been playing around with creating an advanced search query for a weekly report that returns all requirements due in the next 30 days that are not published. I have the following:

Activities.Publish_Date IS NULL AND (Activities.Type_ID = 526 OR Activities.Type_ID = 157 OR Activities.Type_ID = 527 OR Activities.Type_ID = 5154) AND Activities.Schedule_Date - GETDATE() > 30

However, the query is returning all requirement types with due dates well into 2023 - where am I off here? Alas, Advanced Reporting is not available from BB for SKY and they are not longer running the Classic course. Welcome any insights you have.

Comments

  • Justin Finighan
    Justin Finighan
    Ninth Anniversary Kudos 2 Name Dropper Participant

    Hi Ken,

    Try:

    Activities.Publish_Date IS NULL AND (Activities.Type_ID = 526 OR Activities.Type_ID = 157 OR Activities.Type_ID = 527 OR Activities.Type_ID = 5154) AND ((Activities.Schedule_Date - GETDATE()) <= 30)

    Also, you can use a simpler format for your Type_IDs

    Activities.Publish_Date IS NULL AND (Activities.Type_ID IN(526,157,527,5154)) AND ((Activities.Schedule_Date - GETDATE()) <= 30)

  • Ken Knowlton
    Ken Knowlton
    Fourth Anniversary Kudos 1 Participant Facilitator 1

    Justin thank you so much for the quick reply and for the Type_ID bundling, didn't know that was an option. Unfortunately, BB is returning results dating back to the start of the Foundation in 2002. I thought the 30 would bind the return to 30 days, but it doesn't seem to.

    Background here is that the Foundation is 20+ years old, but never really created an online application system so many requirements were scheduled but never published online.

  • Justin Finighan
    Justin Finighan
    Ninth Anniversary Kudos 2 Name Dropper Participant

    We can further restrict the records returned by this query by specifying that the reports also need to have been created after a certain date. For example, if in your new work flow you started publishing all these reports after June 30 this year, you could use the following:

    Activities.Publish_Date IS NULL AND (Activities.Type_ID IN(526,157,527,5154)) AND ((Activities.Schedule_Date - GETDATE()) <= 30) AND (Activities.Create_Date > '2021-06-30')

Categories