July 14, 2015 at 3:07 am
Hi
I have a code I wrote and I'm required to get Product Name and Quantity with the maximum quantity sold per day. My issue is with the "per day" part, if anyone can give me explanation as how how should I approve us problems, here's my code below.
SELECT
t.SalesOrder
,t.OrderQuantity
,t.OrderTotal
,t.OrderDate
,p.ProductName
FROM Transactions t
INNER JOIN SalesPerson sp
ON t.SalesPersonID = sp.SalesPersonID
INNER JOIN Product p
ON t.ProductID = p.ProductID
WHERE sp.SalesPersonName LIKE 'John%'
Now from this code I need to get only Product Name and Quantity with the maximum quantity sold per day, here's what I tried;
SELECT
SUM(t.OrderQuantity) AS Order_Quantity
,p.ProductName
FROM Transactions t
INNER JOIN Product p
ON t.ProductID = p.ProductID
WHERE sp.SalesPersonName LIKE 'John%'
GROUP BY p.ProductName, t.OrderQuantity
HAVING MAX(t.OrderQuantity)
Help please, daily max sold
July 14, 2015 at 3:36 am
A having clause is a predicate. So of the form HAVING <Expression> = <Expression>
Hence what you've got will give a syntax error
To be honest, I think more information is needed here, specifically of the form of sample tables (in the form of CREATE TABLE statements) and sample data (in the form of INSERT statements so that we can see exactly what the data looks like. Also expected results based off the sample data posted so that we can check and compare query results to expected output.
See this article for the most useful format for this http://www.sqlservercentral.com/articles/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2015 at 8:52 am
I agree with Gail, and be sure to indicate a bit more detail on the "maximum quantity sold per day". There are clearly other qualifications that need to go with this in order to figure it out. For example, is this simply the item that the given sales person sold the most of ? Or do you need to have that info for ALL sales persons? Or is it something else? We need to have that level of detail because we can't see what you see, and we can't read your mind.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 9:54 am
I'll throw this in for discussion
-- some test data
SELECT TOP 10000
ProdID = 1 + CAST(Abs(Checksum(Newid()) % 9 ) AS INT),
SalesQty = 1 + CAST(Rand(Checksum(Newid())) * 99 AS DECIMAL(5, 2)),
TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2014', '2015'), '2014')
INTO #TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
-- some details for a specific day
SELECT ProdID, SalesQty, TransDate
FROM #TransData
WHERE (TransDate = '2014-01-01')
ORDER BY ProdID
-- maybe what you are looking for????????????
SELECT ProdID, MAX(SalesQty) AS MaxQTY
FROM #TransData
WHERE (TransDate = '2014-01-01')
GROUP BY ProdID
ORDER BY ProdID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 14, 2015 at 3:15 pm
Or maybe this?:
SELECT
t.ProductID
,t.Order_Day
,t.Order_Quantity
FROM (
SELECT
ProductID
,Order_Day
,Order_Quantity
,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY Order_Quantity DESC) AS row_num
FROM (
SELECT
SUM(OrderQuantity) AS Order_Quantity
,ProductID
,DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0) AS Order_Day
FROM Transactions
GROUP BY ProductID, DATEADD(DAY, DATEDIFF(DAY, 0, OrderDate), 0)
) AS daily_totals
) AS t
WHERE t.row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply