October 12, 2023 at 12:00 pm
I am newbie to SQL programming. wanted to get top 10 customer for the totaldue. tried to build the dynamically could not succeed. then did some trick with hard coded values here is my query. please let me know how to go about it
SELECT OrderYear,CustomerID,TotalDue FROM (
SELECT * FROM
(
SELECT YEAR(OrderDate) AS OrderYear,Customerid,sum(TotalDue) as TotalDue,
ROW_NUMBER() OVER(ORDER BY SUM(TOTALDUE) DESC) EN
FROM Sales.SalesOrderHeader
where YEAR(OrderDate)='2012'
group by YEAR(OrderDate),CustomerID
) A WHERE EN<=10
UNION ALL
SELECT * FROM
(
SELECT YEAR(OrderDate) AS OrderYear,Customerid,sum(TotalDue) as TotalDue,
ROW_NUMBER() OVER(ORDER BY SUM(TOTALDUE) DESC) EN
FROM Sales.SalesOrderHeader
where YEAR(OrderDate)='2013'
group by YEAR(OrderDate),CustomerID
) A WHERE EN<=10
UNION ALL
SELECT * FROM
(
SELECT YEAR(OrderDate) AS OrderYear,Customerid,sum(TotalDue) as TotalDue,
ROW_NUMBER() OVER(ORDER BY SUM(TOTALDUE) DESC) EN
FROM Sales.SalesOrderHeader
where YEAR(OrderDate)='2014'
group by YEAR(OrderDate),CustomerID
) A WHERE EN<=10
UNION ALL
SELECT * FROM
(
SELECT YEAR(OrderDate) AS OrderYear,Customerid,sum(TotalDue) as TotalDue,
ROW_NUMBER() OVER(ORDER BY SUM(TOTALDUE) DESC) EN
FROM Sales.SalesOrderHeader
where YEAR(OrderDate)='2015'
group by YEAR(OrderDate),CustomerID
) A WHERE EN<=10
) AS A
order by OrderYear ASC,TotalDue DESC
October 12, 2023 at 12:13 pm
What about simplifying this a bit and just use the TOP command. All you need is the ORDER BY. You don't have to go into all that row number stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 12, 2023 at 12:14 pm
Please provide some T-SQL statements tabledefinition , the sampledata and expected results based on that sampledata.
So we can copy paste & execute it in ssms without typing.
October 12, 2023 at 12:26 pm
You want the top 10 for each year, or just the top 10 for eternity?
(I ask, because your sample code references YEAR(OrderDate) all over the place, though you do not mention this in your requirement.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 12, 2023 at 2:24 pm
sorry my bad. I want top 1o maximum total due based on the customer id based on the year
October 12, 2023 at 2:45 pm
I think you just need to add a partition to your ROW_NUMBER().
SELECT * FROM
(
SELECT YEAR(OrderDate) AS OrderYear,
Customerid,
sum(TotalDue) as TotalDue,
ROW_NUMBER() OVER(PARTITION BY YEAR(OrderDate) ORDER BY SUM(TOTALDUE) DESC) EN
FROM Sales.SalesOrderHeader
group by YEAR(OrderDate),CustomerID
) A WHERE EN<=10
Drew
Edited to add Customerid to the partition.
Edited to remove Customerid from the partition. This is where having sample data would really help.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2023 at 2:49 pm
sorry my bad. I want top 1o maximum total due based on the customer id based on the year
Difficult to write this query without some sample DDL and data.
But to do it all in one query, you will need to PARTITION your row_number() by year.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2023 at 6:13 am
Thank you drew and phill.
October 13, 2023 at 6:27 pm
This was removed by the editor as SPAM
October 13, 2023 at 6:29 pm
This was removed by the editor as SPAM
October 18, 2023 at 6:26 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply