Search code examples
sql-serverrepeat

Repeat Customers with multiple purchases on the same day counts a 1

I am trying to wrap my head around this problem. I was asked to create a report that show repeat customers in our database.

One of the requirements is if a customer has more than 1 order on a specific date, it would only count as 1. Then if they have more than 1 purchase date, they would then count as a repeat customer.

Searching on here, I found this which works for finding the Customers with more then 1 purchase on a specific purchase date.

SELECT DISTINCT s.[CustomerName], s.PurchaseDate

FROM Reports.vw_Repeat s WHERE s.PurchaseDate <> ''    

GROUP BY s.[CustomerName] , cast(s.PurchaseDate as date)

HAVING COUNT(*) > 1;

This MSSQL code works like it should, by showing customers who had more than 1 purchase on the same date. My problem is what would the best approach be to Join this into another query (this is where i need help) that then shows a complete repeat customer list where customers with more than 1 purchase would be returned.

I am using MSSQL. Any help would be greatly appreciated.

Solution

  • First thanks to everyone for the help. @MaxSzczurek suggested I use table-valued functions. After looking into this more, I ended up using just a temporary table first to get the DISTINCT purchase dates for each Customer. I then loaded that into another temp table RIGHT JOINED to the main table. This gave me the result I was looking for. Its a little(lot) ugly, but it works.