I have a query that is supposed to run thru every user that has an account in the Project_Users table and then return records for a transaction table (Source_of_fund_transaction) for each user and if the user did not commit any transaction or update any transactions return a placeholder row. Each row in the transaction table has a created_by, creation_date, modified_by, and modified_date. The application fills the created_by and modified_by the same and the creation_date and modified_date the same. Only when a transaction is updated do the modified_by and modified_date get changed.
I want to also return the records for the current month as this dataset is then going to make a report that is run at the end of the month.
Here is the query:
SELECT project_users.UserID, SOURCE_OF_FUND_TRANSACTION.transaction_id, 'Insert' as UserAction,SOURCE_OF_FUND_TRANSACTION.CREATION_DATE,
FROM PROJECT_USERS LEFT OUTER JOIN SOURCE_OF_FUND_TRANSACTION on project_users.UserID = SOURCE_OF_FUND_TRANSACTION.Created_By
where source_of_fund_transaction.creation_date is null or datepart(m,source_of_fund_transaction.CREATION_DATE)=datepart(m,(getdate())) and datepart(yyyy,source_of_fund_transaction.creation_date)=datepart(yyyy,(getdate()))
SELECT project_users.UserID, SOURCE_OF_FUND_TRANSACTION.TRANSACTION_ID, 'Update' as UserAction,SOURCE_OF_FUND_TRANSACTION.MODIFIED_DATE,
FROM project_users LEFT OUTER JOIN SOURCE_OF_FUND_TRANSACTION on project_users.UserID = SOURCE_OF_FUND_TRANSACTION.Modified_By
where source_of_fund_transaction.modified_date is null or datepart(m,source_of_fund_transaction.MODIFIED_DATE)=DATEPART(m,(getdate())) and DATEPART(yyyy,source_of_fund_transaction.modified_date)=DATEPART(yyyy,(getdate()))
My problem is that not all users from the Project_users table are being returned...even with a placeholder row with NULL values. I am not sure where I have failed. This was a previous question that I posted and got answers for that I (at the time) thought were correct, but upon further examination of the data realized that not every user was being returned.
Any and all help would be greatly appreciated.