SQL Query Fix

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,
CURRENT_ALLOCATION,CURRENT_CONTRACT,CURRENT_EXPENDITURE,TRANSACTION_TYPE,
PROJECT_ID,PSC_NO
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()))

UNION ALL
SELECT project_users.UserID, SOURCE_OF_FUND_TRANSACTION.TRANSACTION_ID, 'Update' as UserAction,SOURCE_OF_FUND_TRANSACTION.MODIFIED_DATE,
CURRENT_ALLOCATION,CURRENT_CONTRACT,CURRENT_EXPENDITURE,TRANSACTION_TYPE,
PROJECT_ID,PSC_NO
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.
mounty95Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You're not getting "placeholder" rows because of the WHERE clause.  Any conditions in the LEFT JOINed table must be included in the JOIN, not in the WHERE (the LEFT JOIN sets all columns to NULL when a row is not found ... but those NULL columns can never match the conditions in the WHERE clause, and so the row can never be included; effectively, the WHERE clause makes it act like an INNER JOIN rather than a LOJ).

I had to guess for some of the columns on which was the parent table -- naturally if I guessed wrong, those column(s) will need their alias name changed.


SELECT pu.UserID, soft.transaction_id, 'Insert' as UserAction, soft.CREATION_DATE,
    soft.CURRENT_ALLOCATION, soft.CURRENT_CONTRACT, soft.CURRENT_EXPENDITURE,
    soft.TRANSACTION_TYPE, pu.PROJECT_ID, soft.PSC_NO
FROM PROJECT_USERS pu
LEFT OUTER JOIN SOURCE_OF_FUND_TRANSACTION soft ON pu.UserID = soft.Created_By AND (
    (soft.creation_date IS NULL) OR (
    soft.creation_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
    soft.creation_date <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)))
UNION ALL
SELECT pu.UserID, soft.transaction_id, 'Update' as UserAction, soft.modified_date,
    soft.CURRENT_ALLOCATION, soft.CURRENT_CONTRACT, soft.CURRENT_EXPENDITURE,
    soft.TRANSACTION_TYPE, pu.PROJECT_ID, soft.PSC_NO
FROM PROJECT_USERS pu
LEFT OUTER JOIN SOURCE_OF_FUND_TRANSACTION soft ON pu.UserID = soft.Created_By AND (
    (soft.modified_date IS NULL) OR (
    soft.modified_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
    soft.modified_date <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mounty95Author Commented:
Scott,
Thank you for the explanation on what I was doing wrong and offering a solution that was spot on.  I greatly appreciate the help.

Patrick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.