Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

combine sql not in and not exists

here is my situation

i have 3 sql server tables
one is a customer table
one is an orderhistory table
and

one is a log table

the customer table has customerid along with name mailing address and email address

the orderhistory table has customer id and order total amount and order date

the log table has 2 fields
customerid
and insert date

I need to write an entry into the log table with the customerid for those customers that have placed an order over $50 in a given month that have an email address

this process is once a month on the 15


i need to check the log table before writing an entry to make sure customerid does not already exist

here is some sample data

customer table

customerid emailaddress
12345           jdoe@gmail.com




orderhistory table

customerid  order total orderdate

12345    40   11/20/2018
12345    60    02/19/2019
12345    70    04/14/2019

log table would have an entry
for customer 12345 since his 60 order on 02/19/2019

would have generated an entry

therefore
when the process ran on 4/15. i would not have wanted another entry written for customer 12345 written
even though there was an order over 50 placed on 4/14/2019

i was thinking of needing to combine a sql not in for the order amount rule and a not exists to determine if record already exists

looking for an example though

thanks!
ASKER CERTIFIED SOLUTION
Avatar of John_Vidmar
John_Vidmar
Flag of Canada 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