• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 45
  • Last Modified:

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.
0
mounty95
Asked:
mounty95
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now