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

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)
johnnyg123Asked:
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.

Kyle AbrahamsSenior .Net DeveloperCommented:
-- get the max email sent per customer
; with CTE as
(
select
CustomerID , max(EmailSentDate) ESD
CustomerSentEmail  
group by customerID
)

--insert the customerId and today
insert into CustomerScheduleEmailSchedule
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)
0
John_VidmarCommented:
INSERT	CustomerSentEmail
(	CustomerID
,	EmailSentDate
)
SELECT	a.CustomerID
,	current_timestamp
FROM	(	SELECT	CustomerID
		,	maxdate	=	MAX(OrderDate)
		FROM	CustomerOrderDate
		GROUP
		BY	CustomerID
	)	a
LEFT
JOIN	(	SELECT	CustomerID
		,	maxdate	=	MAX(EmailSentDate)
		FROM	CustomerSentEmail
		GROUP
		BY	CustomerID
	)	b	ON	a.CustomerID = b.CustomerID
WHERE	b.CustomerID IS NULL
OR	b.maxdate < dateadd(d, current_timestamp, -30)

Open in new window

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
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.