Click here to Skip to main content
15,940,921 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a data set (AdventureWorks db) with fields like 'totalDue, CustomerID, OrderDate, OrderNumber'. I have to show the top customer and total amount they spent over any 2 quarter period from the 3 years (07/01/2005 to 07/31/2008) available in the data. We were told that it can be a range of any period of 9 consecutive months, but for like every year. And perhaps the best way to do it is with "Over BY Partition function".

I have tried numerous ways, but I am not getting the right answer.

What I have tried so far isn't giving me the right answer. The answer is supposed to be CustomerID: 29641. And we are also supposed to show the total amount spent by that Customer, but we weren't given that part of the answer.

Can anyone give me an insight to this problem please?

What I have tried:

select top 1 CustomerID,
Sum(TotalDue) Over (Partition By dateadd(qq, 3, OrderDate)) as TotalSpent
From Sales.SalesOrderHeader
Posted
Updated 31-Jan-17 7:03am
v2
Comments
Afzaal Ahmad Zeeshan 22-Jan-17 4:02am    
How do we know that the customer ID must be that one, we have no idea how your tables are structured.

Besides, to also get the total amount spent, just add that as column query (such as SUM(amount_spent). But remember, for aggregate functions you also need to use grouping in SQL.

1 solution

It might have been best if you had just typed in what your homework assignment said because you didn't explain it very well.

However, first problem - you are using Partition By for the quarter, but you also need to partition by the CustomerID
SQL
Sum(TotalDue) Over (Partition By CustomerID, dateadd(qq, 3, OrderDate)) as TotalSpent
Also, you have used top 1 but you have not ordered the results so the single row returned is quite random. You need to include an order by clause e.g.
SQL
order by Sum(TotalDue) Over (Partition By CustomerID, dateadd(qq, 3, OrderDate)) desc
OR
SQL
order by 2 desc
will also work - read this as "order by the 2nd item in the SELECT list, highest value first"

I'm not sure if the overall total for that customer is supposed to be in the same query or not - if it is then include another Sum using Partition By but this time you don't need to partition by the quarter.

That should be enough to get you moving on this again
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900