Count users who have not logged requests

[Jim]

I have been asked to return a count of users who have not logged requests within a given date range.

 

I've had to go back to basics on the query just to test and I'm not getting any results on this at all? 

 

select h_user_id from h_sys_accounts 
WHERE h_user_id NOT IN (select h_fk_user_id from h_itsm_requests)

If I choose the opposite and return a distinct list of users who have logged requests, the query works but not if I choose NOT IN. Am I missing something obvious here as there are definitely accounts that have not logged requests

[James Ainsworth]

Hi Jim,

Thanks for your post.

I believe that your query doesn't return anything because when using NOT IN, each result returns NULL and with a NULL value nothing gets displayed.

Try this.  

SELECT  h_user_id
FROM    h_sys_accounts
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    h_itsm_requests
        WHERE   h_user_id = h_fk_user_id
        )

 

[Jim]

Thank you that looks perfect, can add in the rest of my criteria now and start the measures.