0

I have been trying to get the number of customers who bought the first time, 2nd time and 3rd time over 12 months period. Like this: enter image description here

I have found a similar script that shows the repeat customers but there is no splitting over the months or times. How can i get count of customers per day by unique and repeat customer for specific date?

SELECT a.OrderDate, a.first, (b.total - a.first) AS repeated FROM
(SELECT OrderDate, COUNT(q1.CustomerID) AS first FROM 

(SELECT CustomerID, min(OrderDate) AS OrderDate FROM orders GROUP BY CustomerID)q1 GROUP BY q1.OrderDate)a

JOIN

(SELECT OrderDate, COUNT(OrderNumber) AS total FROM orders GROUP BY OrderDate)b
on(a.OrderDate = b.OrderDate)

Here is the sample data:

enter image description here

I am beginner for sql and have been trying to modify for a few days.

Thank you!

Andrea
  • 11,801
  • 17
  • 65
  • 72
user149635
  • 65
  • 2
  • 12
  • The output doesn't have structure of a SQL table. – Michał Turczyn Dec 07 '17 at 08:21
  • You should just update `DATE(T.OrderDate)` in select and group by sections both to what you need. F.ex. `datename(mm,T.OrderDate)+' '+cast(datepart(yyyy,T.OrderDate) as varchar)`. there are many questions how to convert date to desired format. F.ex. [this](https://stackoverflow.com/questions/45535/get-month-and-year-from-a-datetime-in-sql-server-2005) – Ivan Burlutskiy Dec 07 '17 at 08:23
  • @MichałTurczyn : Yes I think so. After that I may have to work into excel. – user149635 Dec 07 '17 at 08:58
  • I mean, it's impossible or at least, effect wouldn't satisfy your needs. – Michał Turczyn Dec 07 '17 at 09:00

0 Answers0