sqlsql-serverclrstoredprocedure

SQL Query for all systemusers who's organisationunits has a datevalue < ... with pivot table in between

I have 3 tables,

All tables have an Active bit column and the OrganisationalUnit table has a CoveredTill datetime column.

I want a list of all UserOrganisationUnits where OrganisationalUnit "CoveredTill" is < x (value doesn't matter as will be set in clr) date and all tables records are active.

So far I've only managed to get unique users from my query so any advice would be much appreciated.

SELECT     
   SystemUsers.DisplayName, SystemUsers.Email, 
   OrganisationalUnits.Description, OrganisationalUnits.CoveredTill, 
   OrganisationalUnits.Id, OrganisationalUnits.ParentId, OrganisationalUnits.TypeId
FROM         
   SystemUsers 
RIGHT OUTER JOIN
   OrganisationalUnits ON SystemUsers.Id = OrganisationalUnits.Id 
INNER JOIN
   UserOrganisationUnits ON SystemUsers.Id = UserOrganisationUnits.Id
WHERE     
   (OrganisationalUnits.Active = 1) 
   AND (SystemUsers.Active = 1) 
   AND (UserOrganisationUnits.Active = 1) 
   AND (OrganisationalUnits.CoveredTill < '2017-03-31 00:00:00.000')

I randomly set the date as the above since I manually set the covered till date as "2015-03-31 00:00:00.000" on all OrganisationalUnits "CoveredTill" columns.

My aim is to have a single sql query string which I can use in my clr stored procedure to create a list of all users who need to be emailed because there organisational unit is no longer covered and for said email to include which organisational unit hence why just having a unique list of users isn't sufficient.

I already have the email code so it's just the query I'm having issues with.

Edit:-

I had first approached this as needing 3 separate queries which I guess after a bit more testing I'll have to revisit unless someone can spot what I'm doing wrong with the above query?

Edit 2:-

After working through the issue with sarin and preparing to provide him a sql db/query copy I found that some of the joins were pointing towards the wrong ID fields (automatically created from different panes so always be wary of anything they create) so below I've included the final solution. (Which includes a couple extra fields I decided might be useful).

SELECT  OrganisationalUnits.Description, OrganisationalUnits.CoveredTill, SystemUsers.DisplayName, SystemUsers.Email, 
        UserOrganisationUnits.LastVisited AS UOULastVisited, SystemUsers.LastVisited AS SULastVisited
FROM    UserOrganisationUnits INNER JOIN
        OrganisationalUnits ON UserOrganisationUnits.OrganisationUnitId = OrganisationalUnits.Id INNER JOIN
        SystemUsers ON UserOrganisationUnits.UserId = SystemUsers.Id
WHERE   (OrganisationalUnits.Active = 1) AND (SystemUsers.Active = 1) AND (UserOrganisationUnits.Active = 1) AND 
        (OrganisationalUnits.CoveredTill < '2017-03-31 00:00:00.000')
Solution

  • Performing a Right\left join before you do an inner join can give you unexpected results as it may produce nulls and your inner join will then attempt to filter them out. I'm not entirely clear about the relationships between the tables but can you turn them all into INNER JOINS to get the same list and produce you a similar result based on your expected outcome?

    "My aim is.... to create a list of all users who need to be emailed because there organisational unit is no longer covered"

    UPDATED:

    SELECT     
   SystemUsers.DisplayName, SystemUsers.Email, 
   OrganisationalUnits.Description, OrganisationalUnits.CoveredTill, 
   OrganisationalUnits.Id, OrganisationalUnits.ParentId, OrganisationalUnits.TypeId
FROM         
   UserOrganisationUnits
INNER JOIN SystemUsers ON SystemUsers.Id = UserOrganisationUnits.Id
                       AND (SystemUsers.Active = 1) 

INNER JOIN OrganisationalUnits ON OrganisationalUnits.Id = SystemUsers.Id
                               AND OrganisationalUnits.Active = 1
                               AND OrganisationalUnits.CoveredTill < '2017-03-31 00:00:00.000'

WHERE UserOrganisationUnits.Active = 1