August 27, 2014 at 10:11 am
Hi everyone!
First time post, I hope this is in the correct place and I give enough info!
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
If you need anything else, please just ask.
Thanks in advance.
Michael
August 27, 2014 at 10:21 am
michaeleaton 36224 (8/27/2014)
Hi everyone!First time post, I hope this is in the correct place and I give enough info!
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
If you need anything else, please just ask.
Thanks in advance.
Michael
Hi and welcome to the forums. We need a bit more detail about your tables than you posted. Ideally we would like a few more pieces of information like:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2014 at 10:41 am
michaeleaton 36224 (8/27/2014)
Hi everyone!First time post, I hope this is in the correct place and I give enough info!
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
If you need anything else, please just ask.
Thanks in advance.
Michael
You can start with
SELECT your desired columns
FROM Orders
INNER JOIN (
SELECT CustomerID
FROM Orders
WHERE PaymentDate >= '7/29/2014' AND PaymentDate <= '8/29/2014'
GROUP BY CustomerID
HAVING COUNT(*) = 1 ) AS C ON Orders.CustomerID = C.CustomerID;
There are other ways to do this but this was my first thought.
August 27, 2014 at 11:08 am
Definitely would be easier to answer with some sample data and expected outcome, but here's what I've come up with:
WITH newCustomers
AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY CustomerID) AS orderCount
FROM
dbo.Orders AS O
WHERE
/* since PaymentDate is defined as DateTime using this is more consistent
method then between because with between you'd need to include the time
portion to be sure you get the full day for most recent date */
O.PaymentDate >= '2014-07-29' AND
O.PaymentDate < '2014-08-27'
)
SELECT
*
FROM
newCustomers AS NC
WHERE
/* the NOT EXISTS enforces that they have not had an order outside the
date range as well. I'm doing this because I interpreted this,
"but also taking into account if they have ever had a paid order outside of that date range",
to mean that you didn't want customers who had an order outside the date range. */
NOT EXISTS ( SELECT
1
FROM
Orders AS O
WHERE
NC.CustomerID = O.CustomerID AND
(
O.PaymentDate < '2014-07-29' OR
O.PaymentDate >= '2014-08-27'
) ) AND
NC.orderCount = 1;
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
August 27, 2014 at 2:56 pm
Thanks everyone for your help. Jack's solution works just great. I probably should have posted the output I wanted, but in essence I wanted it grouped by Username with the count of orders for that user, I've tried to modify Jack's solution to do this but having no luck at the moment..
August 27, 2014 at 3:13 pm
michaeleaton 36224 (8/27/2014)
Thanks everyone for your help. Jack's solution works just great. I probably should have posted the output I wanted, but in essence I wanted it grouped by Username with the count of orders for that user, I've tried to modify Jack's solution to do this but having no luck at the moment..
So I think this might be want to you want:
WITH newCustomers
AS (
SELECT
*,
COUNT(*) OVER (PARTITION BY CustomerID) AS orderCount
FROM
dbo.Orders AS O
WHERE
/* since PaymentDate is defined as DateTime using this is more consistent
method then between because with between you'd need to include the time
portion to be sure you get the full day for most recent date */
O.PaymentDate >= '2014-07-29' AND
O.PaymentDate < '2014-08-27'
)
SELECT
COUNT(*) AS orderCountByUser,
NC.UserID,
U.UserName
FROM
newCustomers AS NC
JOIN dbo.aspnet_Users AS U
ON NC.UserID = U.UserID
WHERE
/* the NOT EXISTS enforces that they have not had an order outside the
date range as well. I'm doing this because I interpreted this,
"but also taking into account if they have ever had a paid order outside of that date range",
to mean that you didn't want customers who had an order outside the date range. */
NOT EXISTS ( SELECT
1
FROM
Orders AS O
WHERE
NC.CustomerID = O.CustomerID AND
(
O.PaymentDate < '2014-07-29' OR
O.PaymentDate >= '2014-08-27'
) ) AND
NC.orderCount = 1
GROUP BY
NC.UserID,
U.UserName;
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply