Need to find if a customer is new.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to find if a customer is new.

Author

Topic

Runnit4
Starting Member

2 Posts

Posted - 2010-09-13 : 18:33:31

I am extremely new to SQL and need to start somewhere...I am trying to find whether a customer is new to our company in, for example, the 2nd quarter of 2009.

The following will give me every order from Q2, but I can't seem to figure how to check whether:

A) the customer had an order before the time period (and thus exclude the returned row)
B) how to find if the customer ordered more than once during the time period (and thus return only the first order and exlcude the second order if new, exclude both if customer already existed)

I apologize in advance if i have excluded necessary information or is too confusing. I use Server 2005 and a non-production version of the database for practice. Thanks!

--How many new customers ordered in Q2 2009?

SELECT c.customer_id,
c.firstname,
c.lastname,
ca.order_number,
ca.order_date,
t.store,
e.store_entity
FROM customer c
LEFT OUTER JOIN orders ca
ON c.customer_id = ca.customer_id
LEFT OUTER JOIN competitors t
ON ca.store_name = t.store_id
LEFT OUTER JOIN store_entities e
ON ca.store_entity = e.entity_id
WHERE
ca.order_date > '2009-03-31' AND
ca.order_date < '2009-07-01'

ORDER BY
c.customer_id

The above works, but i know there will be duplicates (as written) and can't tell if some of the returned cust id's have orders before time frame.

Thanks!

PavanKK
Starting Member

32 Posts

Posted - 2010-09-14 : 02:20:00

Hope this helps

;WITH Cte AS
(
SELECT c.customer_id,
c.firstname,
c.lastname,
ca.order_number,
ca.order_date,
t.store,
e.store_entity,
ROW_NUMBER()OVER(PARTITION BY c.customer_id ORDER BY ca.order_date DESC) RNo
FROM customer c
LEFT OUTER JOIN orders ca
ON c.customer_id = ca.customer_id
LEFT OUTER JOIN competitors t
ON ca.store_name = t.store_id
LEFT OUTER JOIN store_entities e
ON ca.store_entity = e.entity_id
WHERE
ca.order_date > '2009-03-31' AND
ca.order_date < '2009-07-01' AND
c.customer_id NOT IN ( SELECT customer_id FROM orders WHERE order_date <= '2009-03-31' )
)

SELECT customer_id,firstname,lastname,order_number,order_date,store,store_entity
FROM Cte
WHERE RNo = 1

KK

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-14 : 02:27:32

Hello,

For example:

SELECT
c.customer_id,
c.firstname,
c.lastname,
ca.order_number,
ca.order_date
FROM customer c
INNER JOIN (select customer_id, min(order_date) as order_date from orders group by customer_id) ca
ON c.customer_id = ca.customer_id
WHERE
ca.order_date > convert(datetime,'2009-03-31',101) AND
ca.order_date < convert(datetime,'2009-07-01',101)
ORDER BY
c.customer_id

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-14 : 12:01:10

>> I am extremely new to SQL and need to start somewhere <<

It will take at least a year if full time programming in SQL to have an epiphany and to start thinking in sets and not procedural code.

>> I apologize in advance if I have excluded necessary information or is too confusing. <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etC. in your schema arE. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see iT. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Now for the critique:

1) Your singular table name said you had only one customer; I hope business gets better :)

2) What do Competitors have to do with your customers? And did you alias them as "T"?

3) What is a store_entity? What does the store have to do with your question?

4) Can you be more vague in your data element names? YES! The data element name "entity_id" is even worse. "To be is to be something in particular; to be nothing in particular is to be nothing" --Law of Identity. The basic ISO-11179 format is "<attribute>_<property>" and the "_id" property belongs to a particular entity, not some generic one.

5) Why did you do all of those expensive LEFT OUTER JOINs? You said that a customer does not have to place an order to be customer with your code. That makes no sense.

My guess is that all you really care about is the Customers and the Orders.

>> I am trying to find whether a customer is new to our company in, for example, the 2nd quarter of 2009. <<

So, we find everyone's first order based on the order date, If dates fall in the report period, we pick that subset!

SELECT DISTINCT X.customer_id, X.first_name, X.last_name,
FROM (SELECT C.customer_id, C.first_name, C.last_name,
MIN(O.order_date) OVER (PARTITION BY O.customer_id)
AS first_order_date
FROM Customers AS C, Orders AS O
WHERE O.customer_id = C.customer_id) AS X
WHERE first_order_date BETWEEN '2009-04-01' AND '2009-06-31';

Notice the use of BETWEEN instead of simple comparisons; that is part of the "think in sets" approach. The SELECT DISTINCT removes redundant duplicates, in case someone placed two or more orders on their first visit.

Does this help?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 12:08:55

Agree totally. Please post sample data/ structure/ required results.

However....

There may be a difference using BETWEEN and your to > < statements. (depending on the datatype involved)
If these are DATETIMES then


first_order_date BETWEEN '2009-04-01' AND '2009-06-30'

is not quite the same as


ca.order_date > '2009-03-31' AND
ca.order_date < '2009-07-01'

BETWEEN will find any order_date from 1st april 2009 (at 0000 hours) to 30th June 2009 (at 0000) hours

Using > < will find any order_date from 31-May-2009 (after 0000 hours) to 30th June 2009 (to 23:59:59.999)

If you are only storing dates then they are the same.

Also -- there are only 30 days in june

Example:


DECLARE @foo TABLE (
	[foo_id] INT PRIMARY KEY
	, [foo_date] DATETIME
	)

INSERT @Foo ([foo_id], [foo_date])
      SELECT 1, '2009-05-03T23:23:00.023'
UNION SELECT 2, '2009-04-01T00:00:00.000'
UNION SELECT 3, '2009-07-01T00:00:00.000'
UNION SELECT 4, '2009-06-30T12:00:00.000'

SELECT *
FROM 
	@foo
WHERE
	[foo_date] BETWEEN '2009-04-01' AND '2009-06-30'

SELECT *
FROM
	@foo
WHERE
	[foo_date] > '2009-03-31'
	AND [foo_date] < '2009-07-01'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Runnit4
Starting Member

2 Posts

Posted - 2010-09-15 : 18:38:29

Hi everyone, I do apologize for leaving out necessary information and not posting better (or any) sample data...That being said, I have attempted all the above options and could only get Pavan's code to run. I just have to figure whether its exactly what I'm looking for - or at least a quick way to check my results. For future posts, I'll have a little more practice and something not so seemingly elementary for the experts. Thanks everyone for the input!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources