?
Solved

Conditional Sql Insert

Posted on 2014-07-30
2
Medium Priority
?
186 Views
Last Modified: 2014-07-31
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)
0
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40230217
-- 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
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 2000 total points
ID: 40230428
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question