johnnyg123
asked on
Conditional Sql Insert
Here is my situation
I have a table named CustomerOrderDate with the following fields (sql server 2012)
CustomerID int
OrderDate Date
Every night CustomerOrderDate table gets updated with a listing of customers that have placed orders during that day
(this table has like the last 5 years of order dates)
We would like to send an email to the customer once every 30 days (regardless of number of orders placed)
Once an email is sent an entry is written to a table named CustomerSentEmail with following fields
CustomerID int
EmailSentDate date
I need to write a query that writes an entry (with customerid and current date) to a table named CustomerScheduleEmailSched ule for either of the following
if there is a customer id match between CustomerOrderDate and CustomerSentEmail tables that has a max date that is 30 days or earlier than current date
if there is not a customer id match at between CustomerOrderDate and CustomerSentEmail tables
(this means no email has been sent)
For example
If customerorderdate table has following data,
CustomerID OrderDate
1 4/30/2014
1 5/30/2014
1 6/30/2014
1 7/30/2014
2 7/30/2014
3 7/15/2014
3 7/30/2014
If CustomerSentEmail table has following data,
CustomerID EmailSentDate
1 4/30/2014
1 5/30/2014
1 6/30/2014
3 7/15/2014
Assuming current date is 7/30/2014
The query should insert the following into CustomerScheduleEmailSched ule
CustomerID EmailScheduleDate
1 7/30/2014 (because last email was sent 30 days ago)
2 7/30/2014 (because no customer id match)
(no Entry for customer id 3 because last email was sent 15 days ago so not time)
I have a table named CustomerOrderDate with the following fields (sql server 2012)
CustomerID int
OrderDate Date
Every night CustomerOrderDate table gets updated with a listing of customers that have placed orders during that day
(this table has like the last 5 years of order dates)
We would like to send an email to the customer once every 30 days (regardless of number of orders placed)
Once an email is sent an entry is written to a table named CustomerSentEmail with following fields
CustomerID int
EmailSentDate date
I need to write a query that writes an entry (with customerid and current date) to a table named CustomerScheduleEmailSched
if there is a customer id match between CustomerOrderDate and CustomerSentEmail tables that has a max date that is 30 days or earlier than current date
if there is not a customer id match at between CustomerOrderDate and CustomerSentEmail tables
(this means no email has been sent)
For example
If customerorderdate table has following data,
CustomerID OrderDate
1 4/30/2014
1 5/30/2014
1 6/30/2014
1 7/30/2014
2 7/30/2014
3 7/15/2014
3 7/30/2014
If CustomerSentEmail table has following data,
CustomerID EmailSentDate
1 4/30/2014
1 5/30/2014
1 6/30/2014
3 7/15/2014
Assuming current date is 7/30/2014
The query should insert the following into CustomerScheduleEmailSched
CustomerID EmailScheduleDate
1 7/30/2014 (because last email was sent 30 days ago)
2 7/30/2014 (because no customer id match)
(no Entry for customer id 3 because last email was sent 15 days ago so not time)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
; with CTE as
(
select
CustomerID , max(EmailSentDate) ESD
CustomerSentEmail
group by customerID
)
--insert the customerId and today
insert into CustomerScheduleEmailSched
select cod.customerid, getdate()
from customerorderdate cod
left join CTE c on cod.CustomerId = c.CustomerID
where
--where there is no match found
c.CustomerID is null or
-- or the greatest date is less than 30 or equal to 30 days ago.
c.ESD <= dateadd(d, getdate(), -30)