SQL Query - Trouble with null Date and Join

Hello There Experts!

I am having a bit of trouble with my query.  I am LEFT JOINING to a table.

DECLARE @passedInOrderDatedate = '2017-08-01'
Select * from Customer LEFT JOIN Order where orderDate > @passedInOrderDate

Need to find:
1) Customers that do not have records that have an orderDate > @passedInOrderDate
2) Customers that do not have any Order records at all

Please ask questions as I have already taken too much time to solve this on my own.

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, you are missing the ON clause that spells out how Customer and Order are related.

SELECT * 
FROM Customer 
   LEFT JOIN Order ON Customer.??? = Order.???
WHERE orderDate > @passedInOrderDate

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming the above is figured out, and using some air code..

-- 1) Customers that do not have records that have an orderDate > @passedInOrderDate
SELECT * 
FROM Customer 
   LEFT JOIN Order ON Customer.CustomerID = Order.CustomerID
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Customer WHERE orderDate > @passedInOrderDate

-- 2) Customers that do not have any Order records at all 
SELECT * 
FROM Customer 
   LEFT JOIN Order ON Customer.CustomerID = Order.CustomerID
WHERE Order.CustomerID IS NULL

Open in new window

0
ste5anSenior DeveloperCommented:
Both in one query:

DECLARE @passedInOrderDate DATE = '20170801';

SELECT C.*
FROM   PayPerClick.Customers C
       LEFT JOIN Orders O ON O.CustomerID = C.CustomerID
                             AND O.orderDate > @passedInOrderDate
WHERE  O.OrderID IS NULL;

Open in new window


p.s. use unambiguous date literals!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
...set @passedInOrderDate as required...
Select C.*
From Customer C
OUTER APPLY (
    Select Max(O.orderDate)
    From [Order] O
    Where O.CustomerID = C.CustomerID
) AS OA1
WHERE OA1.orderDate IS NULL /*No orders found*/ Or
    OA1.orderDate <= @passedInOrderDate /*earliest date not > @passedInOrderDate*/
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
Jimbo99999Author Commented:
Thank you all for your help.  I ended up working with Scott's or in the where.  But I wanted to say thanks to everyone for their responses.  

jimbo99999
0
ste5anSenior DeveloperCommented:
Well, just compare the execution plans..
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
SQL

From novice to tech pro — start learning today.