November 13, 2009 at 6:09 am
Hi Guys, need some help here.. My manager has asked me to write a query that will return all customers who have bought for the first time the previous day. Seems pretty simple but so far I've not been able to nail it, it just returns all the customers who bought the previous day not only those who's first purchase was the previous day. Can someone tell me where I'm going wrong
SELECT COUNT(DISTINCT dbo.orders.user_id) AS Users_Amount,
MIN(dbo.orders.created_date) AS First_Purchase_Date
FROM dbo.orders
WHERE (dbo.orders.created_date >= DATEADD(DAYOFYEAR, - 1, GETDATE()))
AND (dbo.orders.base_price > 0)
November 13, 2009 at 6:31 am
This gives you a detail list:
SELECT F.[user_id], F.First_Purchase_Date
FROM (
SELECT [user_id],
MIN(dbo.orders.created_date) AS First_Purchase_Date
FROM dbo.orders
WHERE base_price > 0
GROUP BY [user_id]) F
WHERE F.First_Purchase_Date >= DATEADD(DAYOFYEAR, - 1, GETDATE())
November 13, 2009 at 7:55 am
Thanks dude, works now. Never though of doing a select in the FROM, that's where experience comes in handy, cheers! 😀
November 15, 2009 at 1:46 pm
SELECTCOUNT(*) AS User_Count
FROM(
SELECT[User_ID],
MIN(Created_Date) AS First_Purchase_Date
FROMdbo.Orders
GROUP BY[User_ID]
HAVINGMIN(Created_Date >= DATEDIFF(DAY, 1, GETDATE())
AND MIN(Created_Date < DATEDIFF(DAY, 0, GETDATE())
) AS d
N 56°04'39.16"
E 12°55'05.25"
November 16, 2009 at 6:56 am
I'd try something like this:
SELECT
O.user_id
FROM
dbo.orders AS O
GROUP BY
O.user_id
HAVING
MIN(created_date) >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-1, 0) AND
MIN(created_date) < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
I'd also test the other solutions. The only issue I would have with the other solutions is that they do not take into account the time portion of the day, if you are using the new DATE datatype in SQL Server 2008 that is not an issue, but with datetime or smalldatetime it will be an issue, and they will give you the anyone with a first order date of yesterday or today, not just yesterday which was the request.
I tested the solutions using my copy of AdventureWorks and they evaluate out to the same execution plan on on my desktop (dual core).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply