Received, but not reviewed requirements either by grants staff or by financial staff
The financial review date field was created on March 17th, 2020 and that is taken under consideration when this query was written.
((REQUIREMENTS_EXT.Date_Approved_Date IS NULL OR (Requirements_Ext.Financial_Review_Approval_Date IS NULL AND Activities.Schedule_Date >= N'03/17/2020')) AND (Activities.Received_Date Is NOT Null) AND (Activities.Done_Flag = 0) AND (Activities.Type_ID = 526 OR Activities.Type_ID = 157 OR Activities.Type_ID = 527 OR Activities.Type_ID = 5044 OR Activities.Type_ID = 5047 OR Activities.Type_ID = 5046 OR Activities.Type_ID = 5054)) AND NOT Requests.Grant_Amount BETWEEN 1 AND 500 AND NOT Requests.Reference_Number = N'4525'
All active affiliations of all active grants
Requests.Disposition = 'Approved' AND Affiliations.End_Date Is Null AND Requests.Project_End_Date > getDate() AND Requests.Grant_Amount > 500 AND NOT (Requests.Status_ID = 5072)
Today's dynamic date
To make a filter dynamic based on today's date, you can use getDate(), for example, the following search will return every request that has an end date of any date bigger than today:
Requests.Project_End_Date > getDate()
All requirements that have no external documents attached to them
The logic of this search (which must be run as a Requirements Search) is "Show me all the Requirements for which there is no related External Document Activity".
Activities.ID NOT IN (SELECT r.ID FROM Activities r INNER JOIN Activities ed ON r.ID = ed.Activity_ID AND ed.Type_ID = 20)
All external documents that are attached to a particular type of requirement (for instance FSR)
Activities.Type_ID = 20 AND Activities.Activity_ID IN (SELECT r.ID FROM Activities r WHERE Type_ID = 5458)
List of requirement type IDs:
526 //Final Progress Report 157 //Financial Status Report 527 //Progress Report 5044 //Final Financial Report 5047 //Audited Financial Statement 5051 //Animal Subjects 5052 //Human Subjects IRB 5053 //Biological Material 5055 //Radioactive Materials 5046 //Non-Supplanting Questionnaire 5054 //Inter-Institutional Agreement
Finding duplicate contacts based on their first and last name:
(exists(select contactsx1.id from contacts as contactsx1 where contactsx1.Last_name = Contacts.last_name And contactsx1.First_name = Contacts.first_name and Contacts.last_name <> '' and contactsx1.id <> contacts.id))
All stage 2 requests:
Here is what I would do and it only works for requests which have a submission of stage 2 application form(s): If I am looking for specific stage 2 application, then I would do this: - First I would go to dashboard > ribbon menu > launch > applications > forms manager and find out the ID of that stage 2 application form - Then on advanced search type the following (where you replace the_stage_2_form_id with the id you found in previous step): Stage2_Form_ID = the_stage_2_form_id If I want everything from any stage 2 application form, then I would do this: - On advanced search, I would type the following: Stage2_Form_ID > 0 If you would like to check the results, edit the view > choose Request from dropdown > Add Forms This will show all forms submitted (stage 1 and 2) for the request.
Due n days from today:
When calculating the certain period of time in days, we use datediff and we through the date field that is being calculated and today's date (negative if it is before today and positive if it is more than).
For example, a list of any contact that is affiliated with any final and annual progress reports that is due within 60 days from today:
(Activities.Type_ID = 526 OR Activities.Type_ID = 527) AND datediff(Day, CAST(getDate() AS DATE),CAST(Activities.Schedule_Date AS DATE)) < 60 AND datediff(Day, CAST(getDate() AS DATE), CAST(Activities.Schedule_Date AS DATE))>= 0
Requirement search: all requirements that are overdue and have no external document attached to them:
Activities.ID NOT IN (SELECT r.ID FROM Activities r INNER JOIN Activities ed ON r.ID = ed.Activity_ID AND ed.Type_ID = 20) AND Activities.Schedule_Date < getDate()