johnnyg123
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.