Multiple Top 25's
-
Hi first time poster.
I have a large amount of data that shows item sales for all stores, pulled from the Trans Sales Entry table.
What I'd like to do is query this and get the top 25 sales for each store, however when I use the Top 25 function at the start of my query, it only pulls the top 25 of the first store.
Is it possible to specify multiple top 25's based on one of the fields within the data? (in this case, store number)?
Here is my code:
select top 25
P.[Period Name]
,TSES.[Store No_]
,TSES.[Item No_]
,SUM(TSES.[Quantity]*-1) as 'Sales Units'
,SUM(TSES.[Gross Amount]*-1) as 'Sales Gross Amount'
,tableb.unitqty
,tableb.valueqty
from [Head Office LIVE$Trans_ Sales Entry] as TSES with (nolock)
join [Head Office LIVE$Accounting Period] as P with (nolock)
on P.[Period Start] <= TSES.[Date]
and P.[Period End] >= TSES.[Date]
and P.[Period Start] <= GETDATE()
and P.[Period End] >= GETDATE()
and P.[Period Type] = 2
and TSES.[Return No Sale] = 0
and TSES.[Transaction Status] in (0,2)
left outer join (select P.[Period Name]
,TSES.[Store No_]
,SUM(TSES.[Quantity]*-1) as unitqty
,SUM(TSES.[Gross Amount]*-1) as valueqty
from [Head Office LIVE$Trans_ Sales Entry] as TSES with (nolock)
join [Head Office LIVE$Accounting Period] as P with (nolock)
on P.[Period Start] <= TSES.[Date]
and P.[Period End] >= TSES.[Date]
and P.[Period Start] <= GETDATE()
and P.[Period End] >= GETDATE()
and P.[Period Type] = 2
and TSES.[Return No Sale] = 0
and TSES.[Transaction Status] in (0,2)
where TSES.[Date] > '2012-04-01'
group by P.[Period Name]
,TSES.[Store No_]
) as tableb
on tableb.[Store No_] = tses.[Store No_]
and tableb.[Period Name] = p.[Period Name]
where TSES.[Date] > '2012-04-01'
group by P.[Period Name]
,TSES.[Store No_]
,TSES.[Item No_]
,tableb.unitqty
,tableb.valueqty
order by P.[Period Name]
,TSES.[Store No_]
,SUM(TSES.[Quantity]*-1) desc
,TSES.[Item No_]
,tableb.unitqty
,tableb.valueqty
-
I cannot write sql based on your input, but doing some study of ROW_NUMBER PARTITION BY will help
-
bring results into a CTE, then use row_number() with over(partition) by the store to set a row number for each store then select your collums from cte where your row_number <=25
EDIT i realy should use spelchecker lol :w00t:
***The first step is always the hardest *******
-
Please clarify - do you want a query that return top 25 sales of top 25 stores?
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thank you very much for the quick replies!
To clarify - I want top 25 by store.
so for example the report to show
store 1 - then list all top 25 sales.
store 2 - top 25 sales.
store 3 - ""
store 4 - ""
etc etc.
-
Hi and welcome to SSC. It seems you have gotten some good suggestions at finding a solution. You may be wondering why nobody responded with some sql for you. That is because we would need ddl, sample data and desired output. Take a look at the first link in my signature about best practices when posting questions.
I know this is not part of your question but why all the NOLOCK hints? Unless you can explain why this is needed you should find another way (snapshot isolation for example).
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/ -
Thanks for the comments on Nolock.
Well basically i use it because I've been learning sql for 6 months only, and this is what i have been shown by other members of staff far more experienced than I - it seems that if it is not used then we get other members of staff complaining about various tables being locked and thus they are not able to edit them whilst reports are running.
-
jeezontorst (7/24/2012)
Thanks for the comments on Nolock.Well basically i use it because I've been learning sql for 6 months only, and this is what i have been shown by other members of staff far more experienced than I - it seems that if it is not used then we get other members of staff complaining about various tables being locked and thus they are not able to edit them whilst reports are running.
Go back to these more experienced staff members and have them read those articles. NOLOCK is NOT a magic go fast pill. It is however an easy way to generate duplicate and/or missing data. It is also a good way create software bugs that are nearly impossible to reproduce. And in some situations can even corrupt your entire database.
Look into using isolation instead of the NOLOCK hint.
_______________________________________________________________
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/ -
There are several things that can be done when it comes to the reporting vs updaing conflicts. One is move reporting to a another server or at least another database. How current does the reporting data have to be is the first question to ask.
There are other things that can be done as well but as you are fairly new, I'll leave that for another discussion.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply