I'm building a database, which stores jobs, and users, users can set up alerts to get notified about jobs. I want the alerts to run daily.
The process should be;
List all distinct job sector, region, location combinations for the day
Select all users with alerts that match the selection above
I've written the SQL to select all distinct combinations for the day, which is
[Region], [RegionID], [Location], [LocationID],
[DatePosted] >= DATEADD(d,-1,GETDATE())
[RegionID], [LocationID], [SectorID]
COUNT(*) >= 1
My select statement to select the users looks a little like;
dbo.JBEmailNotification AD ON AD.JBENcandidateID = C.JBACAID
JBENlocation = ???? OR JBENlocation IS NULL
AND JBENlocation = ???? OR JBENlocation IS NULL
AND JBENcategory = ???? OR JBENcategory IS NULL
The output should be that for each distinct sector region location combination of the day I get a recordset containing;
Any suggestions on how to build this query?