Link to home
Start Free TrialLog in
Avatar of Oscar Reyes
Oscar ReyesFlag for United States of America

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.DateReceived

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!
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

You should look at joins first: https://www.w3schools.com/sql/sql_join.asp
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).
Find accounts with service order or invoice in the last 3 years from today:
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))

Open in new window


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))

Open in new window

Avatar of Oscar Reyes

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.
ASKER CERTIFIED SOLUTION
Avatar of zephyr_hex (Megan)
zephyr_hex (Megan)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Worked just needed to change one field name (servicedate to DateReceived) and added "DISTINCT" to the SELECT statement (SELECT DISTINCT ).

Thank you again!