November 17, 2008 at 4:06 pm
Hello all,
New to SQL and having trouble with using MAX in a SELECT statement which is pulling info from a one many relationship.
I need some help with the best way to write this SELECT statement.
Any help would be much appreciated.
Here's what I'm working with:
SELECT
c.CustomerID,
MAX(CONVERT(VARCHAR(8), o.OrderDate, 1)) AS [LastOrderDate],
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
FROM
dbo.Customer AS c
JOIN
dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE
o.OrderDate > '2007-01-01'
AND
o.OrderDate NOT BETWEEN '5/17/08' AND '11/17/08'
GROUP BY
c.CustomerID,
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
ORDER BY
c.CustomerID DESC
November 17, 2008 at 11:56 pm
I would make the following simple change since you didn't say what is wrong with your existing query.
SELECT
c.CustomerID,
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus,
-- MAX(CONVERT(VARCHAR(8), o.OrderDate, 1)) AS [LastOrderDate]
max(dateadd(dd, datediff(dd, 0, o.OrderDate), 0)) as LastOrderDate -- assumes that OrderDate has a time portion as well
FROM
dbo.Customer AS c
JOIN
dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE
o.OrderDate > '2007-01-01'
AND
o.OrderDate NOT BETWEEN '2008-05-17' AND '2008-11-17'
GROUP BY
c.CustomerID,
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
ORDER BY
c.CustomerID DESC
November 18, 2008 at 12:30 am
mr (11/17/2008)
Hello all,New to SQL and having trouble with using MAX in a SELECT statement which is pulling info from a one many relationship.
I need some help with the best way to write this SELECT statement.
Any help would be much appreciated.
Here's what I'm working with:
SELECT
c.CustomerID,
MAX(CONVERT(VARCHAR(8), o.OrderDate, 1)) AS [LastOrderDate],
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
FROM
dbo.Customer AS c
JOIN
dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE
o.OrderDate > '2007-01-01'
AND
o.OrderDate NOT BETWEEN '5/17/08' AND '11/17/08'
GROUP BY
c.CustomerID,
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
ORDER BY
c.CustomerID DESC
can you try like this:
Note: Written the sample query based on Northwind database, you can change accordingly.
selectc.CustomerID
,c.ContactName
,max (o.OrderDate)
fromdbo.Customers c
joindbo.Orders o on (c.CustomerID = o.CustomerID)
whereconvert (varchar (10), o.OrderDate, 101)> '04/20/1996'
group by c.CustomerID, c.ContactName
November 18, 2008 at 8:10 am
I should have prefaced my original post with what I'm trying to accomplish as my SQL knowledgebase is pretty limited.
Both solutions here work, but return different results, so I'll take a stab at explaining what I'm trying to accomplish:
I have (2) tables with a one to many relationship:
tbl.Customer and tbl.Orders
Each table uses 'CustomerID' as their PK.
What I want to accomplish is to select all unique records from the "Customer" table which meet the following criteria in the "Orders" table:
1. Has a row in "Orders" where the "OrderDate" column is greater than '2007-01-01'
AND
2. Does NOT have a row in "Orders" with an "OrderDate" column entry between a given date range, i.e. '2008-05-01' AND '2008-11-01'
---
I initially though that using MAX to select the latest OrderDate and then evaluate that against my date range was the way to go, but now as I think about it more, I think its faulty logic.
Any help or advice on the best way to construct this query is greatly appreciated. It is safe to assume a base/novice understanding of SQL queries.
Thanks in advance for you help.
Cheers!
November 18, 2008 at 8:36 am
Hi "mr"
This should do the trick unless you have multiple orders per customer on a single day, in which case a small change will be required.
[font="Courier New"]DECLARE @MinOrderDate DATETIME, @ExcludeDateMin DATETIME, @ExcludeDateMax DATETIME
SET @MinOrderDate = '2007-01-01'
SET @ExcludeDateMin = '5/17/08'
SET @ExcludeDateMax = '11/17/08'
-- sanity check
SELECT @MinOrderDate, @ExcludeDateMin, @ExcludeDateMax
SELECT c.CustomerID,
CONVERT(VARCHAR(8), d.[LastOrderDate], 1) AS [LastOrderDate],
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
FROM dbo.Customer AS c
INNER JOIN (SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM dbo.Orders
WHERE OrderDate > @MinOrderDate
AND NOT OrderDate BETWEEN @ExcludeDateMin AND @ExcludeDateMax
GROUP BY CustomerID
) AS d ON d.CustomerID = c.CustomerID
INNER JOIN dbo.Orders o ON o.CustomerID = d.CustomerID AND o.OrderDate = d.[LastOrderDate]
ORDER BY c.CustomerID DESC
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 18, 2008 at 8:55 am
Thanks for speedy and thorough reply!
This is exactly what I was looking for.
I will study up on this to grow my SQL chops.
It is possible that a Customer could have more than (1) order on the same day, which is why I started to question myself for using the MAX() function.
Any insight on how to account for that possibility?
Finally, if I want to further qualify the returned recordset, can I simply add WHERE clauses after the INNER JOINs? I just wasn't sure on placement of these.
For example, if I wanted to select only "Shipped" orders, would I do the following (in green):
[font="Courier New"]DECLARE @MinOrderDate DATETIME, @ExcludeDateMin DATETIME, @ExcludeDateMax DATETIME
SET @MinOrderDate = '2007-01-01'
SET @ExcludeDateMin = '5/17/08'
SET @ExcludeDateMax = '11/17/08'
-- sanity check
SELECT @MinOrderDate, @ExcludeDateMin, @ExcludeDateMax
SELECT c.CustomerID,
CONVERT(VARCHAR(8), d.[LastOrderDate], 1) AS [LastOrderDate],
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
o.OrderStatus
FROM dbo.Customer AS c
INNER JOIN (SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM dbo.Orders
WHERE OrderDate > @MinOrderDate
AND NOT OrderDate BETWEEN @ExcludeDateMin AND @ExcludeDateMax
GROUP BY CustomerID
) AS d ON d.CustomerID = c.CustomerID
INNER JOIN dbo.Orders o ON o.CustomerID = d.CustomerID AND o.OrderDate = d.[LastOrderDate]
WHERE o.OrderStatus = 'Shipped'
ORDER BY c.CustomerID DESC
[/font]
Cheers!
Michael R.
November 18, 2008 at 9:13 am
Hi Michael
If orderdate is datetime then it doesn't matter if there are more than one order per customer on the same day, because they're unlikely to be exactly the same time.
You can check using this:
SELECT CustomerID, OrderDate, COUNT(*)
FROM dbo.Orders
GROUP BY CustomerID, OrderDate
HAVING COUNT(*) > 1
If you want to further qualify the returned recordset e.g. with shipped orders, then you would need to apply the filter to both references to the orders table as follows;
INNER JOIN (SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate], OrderStatus
FROM dbo.Orders
WHERE OrderStatus = 'Shipped' AND OrderDate > @MinOrderDate
AND NOT OrderDate BETWEEN @ExcludeDateMin AND @ExcludeDateMax
GROUP BY CustomerID
) AS d ON d.CustomerID = c.CustomerID
This construct incidentally is called a derived table - you can already see how useful it can be.
To finish off the statement, you would add OrderStatus to the join condition of the second reference to the orders table...
INNER JOIN dbo.Orders o ON o.CustomerID = d.CustomerID
AND o.OrderDate = d.[LastOrderDate] AND o.OrderStatus = d.OrderStatus
...although it's likely that if orderdate is datetime, the correct rows would be matched without it.
Hope this helps!
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 18, 2008 at 10:30 am
Hi Chris,
Thanks for this, it is an immense help!
OrderDate is in fact a DATETIME column and therefore unlikely that there would be Orders rows with identical OrderDates for the same CustomerID, correct?
For my own sanity, the query below returns only those CustomerIDs which have more than 1 Order row with an identical OrderDate column in it, correct?
SELECT CustomerID, OrderDate, COUNT(*)FROM dbo.Orders
GROUP BY CustomerID, OrderDate
HAVING COUNT(*) > 1
Thanks for taking the time to help with this and also for explaining what was going on here with the construct.
The derived table approach looks very powerful...I'll have to study it further so I can fully wrap my head around it.
For a beginner, your explanations are very very helpful!
Cheers!
Michael R.
November 18, 2008 at 10:46 am
Hi Michael, thanks for your generous feedback.
Yes, you're correct on both questions. Check that orderdate does have the time component - some systems remove it.
Run the query, see what it returns. No resultset and you're smiling.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 18, 2008 at 11:56 am
Hi Chris,
One last item...
If I wanted to create a view for this statement whereby the initially declared variables (i.e. @MinOrderDate, etc.) could be changed when running it, what would that look like?
Thanks again for all our help.
Cheers,
Michael R.
November 18, 2008 at 12:12 pm
Hi mr,
If I understand you correctly then you could put any hardcoded values after the SET @VariableName keyword.. and that would be your parameters being passed. So if you wanted to change dates you could change the hardcoded values... or if you have more variables, you could declare them at the top and then set them.
Hope it helps.
-Omair
--
:hehe:
November 19, 2008 at 2:48 am
Hi Michael
I don't think this could be converted into a view in the exact way that you've suggested because the parameters are required for the derived table, i.e. before the result set can be materialized. I'd be interested to hear otherwise! Personally, I'd convert this to a stored procedure, passing in the three required parameters. It's no big deal to get the results from a sproc into a table.
If it's really important that this is turned into a view, the only way I can see to do it is to create a new table having one row with three columns, one for each parameter, and join to this in the SELECT to retrieve the values.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 19, 2008 at 10:52 am
Hi Chris,
I discovered that passing parameters to a VIEW wasn't possible, at least I don't think so.
So, as you've suggested, I created a STORED PROCEDURE with accepts those date parameters, making it more flexible.
You're suggestion on how to create the VIEW though, using a separate table for the parameters is pretty clever. I'll have to try that one out!
Finally, because of where I'm using this query, using the STORED PROCEDURE isn't always possible, but using a FUNCTION is.
So, I've setup a FUNCTION as below where I'm trying to use SELECT TOP (@RowCount) to limited the number of records returned, but the results are not working out for me, returning fewer than I specify. When I created the STORED PROCEDURE, I simply used SET ROWCOUNT (@RowCount) which works perfectly, but not when using SELECT TOP in this FUNCTION. I'm sure I'm doing something wrong here! Any advice on how to get the FUNCTION to pull the correct number of records based on my @RowCount parameter?
I tried placing the SELECT TOP (@RowCount) in the first INNER JOIN as well as in the initial SELECT statement, but just wasn't sure on correct placement. Either way, it was returning too few records.
Here's the full function:
CREATE FUNCTION dbo.MyFunc (@RowCount INT, @MinOrderDate DATETIME, @ExcludeDateMin DATETIME, @ExcludeDateMax DATETIME)
RETURNS TABLE
AS
RETURN
(
SELECT
c.CustomerID,
CONVERT(VARCHAR(8), d.[LastOrderDate], 1) AS [LastOrderDate],
c.Email,
c.FirstName,
c.LastName,
c.CustomerType,
CASE
WHEN convert(varchar(50), c.EnrollerID) = 1 THEN 'grace'
WHEN convert(varchar(50), c.EnrollerID) = 7492 THEN 'FTC'
WHEN convert(varchar(50), c.EnrollerID) = 24167 THEN 'mzliz20'
ELSE convert(varchar(50), c.EnrollerID)
END AS WebAlias,
o.OrderStatus
FROM dbo.Customer AS c
INNER JOIN (SELECT TOP (@RowCount) CustomerID, OrderStatus, MAX(OrderDate) AS [LastOrderDate]
FROM dbo.Orders AS o
WHERE OrderStatus = 'Shipped' AND OrderDate > @MinOrderDate
AND NOT OrderDate BETWEEN @ExcludeDateMin AND @ExcludeDateMax
GROUP BY CustomerID, OrderStatus
) AS d ON d.CustomerID = c.CustomerID
INNER JOIN dbo.Orders o ON o.CustomerID = d.CustomerID AND o.OrderDate = d.[LastOrderDate] AND o.OrderStatus = 'Shipped'
)
Cheers!
Michael R.
November 20, 2008 at 2:21 am
Hi Michael
SELECT TOP (@RowCount) won't run (or let you create a function/sp etc) as it stands because TOP n is part of the statement. You would have to set this up as dynamic sql, which isn't permitted in functions. I'd suggest, because I'm lazy, that you use the function like this:
SELECT TOP 1 * FROM dbo.MyFunc (a, b, c) ORDER BY...
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply