Scenario: When you want a notifier to send an email on schedule even when no results are found, we can use the method described below to get the notifier to run and return a notice that no result was found.

Basic Template: Here is a basic template of the concept that will be illustrated by the example below. To get the notifier to send an email with a no results row, we need the query to produce a no results row. To accomplish this we will create a derived table in SQL consisting of a UNION ALL with the original query and a 2nd query that returns a result when the first returns NULL. In the example below, the 2nd query just returns a results from the HQCO view as long as the 1st query is NULL. This is accomplished by a simple select from HQCO and a WHERE clause of NOT EXISTS in the 1st QUERY.

So the basic template as as follows:

SELECT t1.Column1 as [Column1], etc 
FROM (
       QUERY 1
 
       UNION ALL --Note that the number of columns and format need to match in Query 1 and Query 2

       SELECT 'No Results Found' as Column1, etc FROM HQCO  
              --The No Results Found String needs to be in a column from Query 1 with a string format.
       WHERE NOT EXISTS (QUERY 1) 
              --Note that the select list can be replaced with 1 (i.e SELECT 1 FROM ...) 
)t1

Requirements: These steps assume that you own the WF Module.

Example Solution: (This example solution returns employees with part-time hours in the past 3 weeks)

  1. Open the WF Notifier Queries form
  2. Create a new Record by entering CTS_PREmployeesWithPartTimeHours in the Query Name Field
  3. Enter PR Employees with part time hours in the past three weeks in the Title Field
  4. Enter Returns Employees with hours less than threshold for the past three weeks or none if none exists. Threshold set in WF Notifier Job Manager in the Description Field
  5. Enter SQL Query #1 (found at the bottom) in the Select field. Modify the Earn Code in list to the actual earn codes desired for your company.
  6. Enter SQL Query #2 (found at the bottom) in the From where clause field
  7. Press the Test Query button to test the query.
  8. If the query tests ok, save and close the form.
  9. Open WF Notifier Job Manager
  10. Enter CTS_PREmployeesWithPartTimeHours in the Job Name & Description Fields.
  11. Set Query type to 0-WF Notifier Query
  12. Select CTS_PREmployeesWithPartTimeHours as the query.
  13. Set the desired schedule
  14. On the email tab, change the Format to HTML, enter the desired recipient, subject, and header.
  15. In the Body field, use the table button to create and format a table. 
  16. On the Parameters tab, setup the threshold parameter by inserting a row as @threshold in the Param column, 30 (or desired value) in the InputValue, and a description.
  17. Check the Consolidate Notifications field at the bottom of the form.
  18. On the Consolidations Group tab, add [To] to the Grouping Criteria.
  19. On the Email tab, press send Test button to test the notifier to make sure it functions as expected.

SQL Query Part #1

select t1.PRCo as [PRCo], t1.Employee as [Emp #], t1.Fullname as [Name] --Add additional columns as desired

SQL Query Part #2 

select E.PRCo
, E.Employee
, e.FullName
, E.Email
, E.CellPhone
, d.Description
from PREH E 
INNER JOIN PREHFullName e on E.PRCo = e.PRCo
and E.Employee = e.Employee
LEFT OUTER JOIN PRDP d on E.PRCo = d.PRCo
and E.PRDept = d.PRDept
INNER JOIN (select PRTH.PRCo 
, PRTH.Employee
, sum(Hours) as wk1Totalhours
from PRTH
WHERE EarnCode in (10,11,13,17,100,105,106)
and PREndDate = cast(dateadd(dd,-DATEPART(dw,getdate()),getdate()) as date)
GROUP BY PRCo
, Employee) wk1 on E.PRCo = wk1.PRCo
and E.Employee = wk1.Employee
and wk1.wk1Totalhours< @threshold
INNER JOIN (select PRTH.PRCo 
, PRTH.Employee
, sum(Hours) as wk2Totalhours
from PRTH
WHERE EarnCode in (10,11,13,17,100,105,106)
and PREndDate = DATEADD(dd,-7,cast(dateadd(dd,-DATEPART(dw,getdate()),getdate()) as date))
GROUP BY PRCo
, Employee) wk2 on E.PRCo = wk2.PRCo
and E.Employee = wk2.Employee
and wk2.wk2Totalhours< @threshold
INNER JOIN (select PRTH.PRCo 
, PRTH.Employee
, sum(Hours) as wk3Totalhours
from PRTH
WHERE EarnCode in (10,11,13,17,100,105,106)
and PREndDate = DATEADD(dd,-14,cast(dateadd(dd,-DATEPART(dw,getdate()),getdate()) as date))
GROUP BY PRCo
, Employee) wk3 on E.PRCo = wk3.PRCo
and E.Employee = wk3.Employee
and wk3.wk3Totalhours< @threshold

UNION ALL

select c.HQCo as [PRCo]
, NULL as Employee
, 'Threshold = ' + cast(@threshold as varchar(4)) + ' - None Meeting Criteria' as FullName
, null as Email
, null as CellPhone
, null as Description
FROM HQCO c
WHERE HQCo = 1
AND NOT EXISTS (
select 1
from PREH E 
INNER JOIN PREHFullName e on E.PRCo = e.PRCo
and E.Employee = e.Employee
LEFT OUTER JOIN PRDP d on E.PRCo = d.PRCo
and E.PRDept = d.PRDept
INNER JOIN (select PRTH.PRCo 
, PRTH.Employee
, sum(Hours) as wk1Totalhours
from PRTH
WHERE EarnCode in (10,11,13,17,100,105,106)
and PREndDate = cast(dateadd(dd,-DATEPART(dw,getdate()),getdate()) as date)
GROUP BY PRCo
, Employee) wk1 on E.PRCo = wk1.PRCo
and E.Employee = wk1.Employee
and wk1.wk1Totalhours< @threshold
INNER JOIN (select PRTH.PRCo 
, PRTH.Employee
, sum(Hours) as wk2Totalhours
from PRTH
WHERE EarnCode in (10,11,13,17,100,105,106)
and PREndDate = DATEADD(dd,-7,cast(dateadd(dd,-DATEPART(dw,getdate()),getdate()) as date))
GROUP BY PRCo
, Employee) wk2 on E.PRCo = wk2.PRCo
and E.Employee = wk2.Employee
and wk2.wk2Totalhours< @threshold
INNER JOIN (select PRTH.PRCo 
, PRTH.Employee
, sum(Hours) as wk3Totalhours
from PRTH
WHERE EarnCode in (10,11,13,17,100,105,106)
and PREndDate = DATEADD(dd,-14,cast(dateadd(dd,-DATEPART(dw,getdate()),getdate()) as date))
GROUP BY PRCo
, Employee) wk3 on E.PRCo = wk3.PRCo
and E.Employee = wk3.Employee
and wk3.wk3Totalhours< @threshold
)
) t1