asked on SQL Query Across Multiple Tables - Help
Hello Experts!
I am OK with queries when it involves one table but not so great with multiple table and filtering. I have the following issue i hope someone can help me with:
Tables
tblAccounts
tblServiceOrders
tblInvoices
Relevant Fields
tblAccounts.AccountName
tblAccounts.AccountNumber
tblAccounts.AccountStatus
tblAccounts.AccountType
tblInvoices.InvoiceDate
tblServiceOrders.DateRecei
All Linked by AccountNumber field in all tables.
This is a customer database with all accounts and all their respective service requests and invoices. Looking for a query to provide a list of accounts that meet the following:
AccountStatus=Active
AccountType=Customer
And has had a service order or invoice in the last three years.
Also would like query for the inverse of this; list of accounts that DON’T meet this filter.
Thank you!
I am OK with queries when it involves one table but not so great with multiple table and filtering. I have the following issue i hope someone can help me with:
Tables
tblAccounts
tblServiceOrders
tblInvoices
Relevant Fields
tblAccounts.AccountName
tblAccounts.AccountNumber
tblAccounts.AccountStatus
tblAccounts.AccountType
tblInvoices.InvoiceDate
tblServiceOrders.DateRecei
All Linked by AccountNumber field in all tables.
This is a customer database with all accounts and all their respective service requests and invoices. Looking for a query to provide a list of accounts that meet the following:
AccountStatus=Active
AccountType=Customer
And has had a service order or invoice in the last three years.
Also would like query for the inverse of this; list of accounts that DON’T meet this filter.
Thank you!
DatabasesMicrosoft SQL Server 2008Microsoft SQL Server
Last Comment
Oscar Reyes
Find accounts with service order or invoice in the last 3 years from today:
Find accounts that do not have a service order in the last 3 years and do not have an invoice in the last 3 years:
SELECT a.AccountName, a.AccountNumber
FROM tblAccounts a
LEFT OUTER JOIN tblServiceOrders s on s.AccountNumber = a.AccountNumber
LEFT OUTER JOIN tblInvoices i on i.AccountNumber = a.AccountNumber
WHERE (s.AccountNumber IS NOT NULL AND s.ServiceOrderDate >= CAST(DATEADD(YEAR,-3,GETDATE()) AS DATE))
OR (i.AccountNumber IS NOT NULL AND i.InvoiceDate >= CAST(DATEADD(YEAR,-3,GETDATE()) AS DATE))
Find accounts that do not have a service order in the last 3 years and do not have an invoice in the last 3 years:
SELECT a.AccountName, a.AccountNumber
FROM tblAccounts a
LEFT OUTER JOIN tblServiceOrders s on s.AccountNumber = a.AccountNumber
LEFT OUTER JOIN tblInvoices i on i.AccountNumber = a.AccountNumber
WHERE (s.AccountNumber IS NULL OR s.ServiceOrderDate < CAST(DATEADD(YEAR,-3,GETDATE()) AS DATE))
AND (i.AccountNumber IS NULL OR i.InvoiceDate < CAST(DATEADD(YEAR,-3,GETDATE()) AS DATE))
ASKER
Almost there. The query worked, thank you! I still need to filter a bit by AccountStatus=Active and AccountType=Customer from my original post. Where would i put that in your query? Could you add it to your query?
I know i did not post this my initial question but for the second query where it states the inverse can we feed the results to an action query to change the AccountStatus to "InActive"?
Thank you, learning A LOT from this query.
I know i did not post this my initial question but for the second query where it states the inverse can we feed the results to an action query to change the AccountStatus to "InActive"?
Thank you, learning A LOT from this query.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Worked just needed to change one field name (servicedate to DateReceived) and added "DISTINCT" to the SELECT statement (SELECT DISTINCT ).
Thank you again!
Thank you again!
Please note various join types - inner, left, right, full, ...
To decide if something is IN certain set of values look at the IN operator (https://www.w3schools.com/sql/sql_in.asp).