question

dennisdg00 avatar image
dennisdg00 asked

TOP 10 RECORDS FOR SPECIFIC LOCATIONS BY TOTALS SALES

Trying to figure out how to get the top 10 customer sales from each of my 18 locations. Each location has a salonid. If I use the below listed query. It only returns top (n) no matter what location. SQL Server 2005 select top ( 20 ) Client_General.ClientUID , Client_General.ID , Client_General.FirstName , Client_General.LastName , sum(Reg_Transactions.Price) as 'TOTAL_SALES' , Client_General.LastTanVisitSalon from Reg_Transactions inner join Client_General on Reg_Transactions.ClientUID = Client_General.ClientUID where ( Reg_Transactions.Deleted = 0 ) and ( Reg_Transactions.DateofSale > convert(datetime,'2012-07-01 00:00:00',102) ) and ( Client_General.IsEmployee = 0 ) and ( not ( Client_General.LastName = N'NONMEMBER' ) ) and ( not ( Client_General.LastName = N'GIFT CARD' ) ) and ( not ( Client_General.ID = N'WALKIN' ) ) and ( not ( Client_General.ID = N'CUSTOMER' ) ) group by Client_General.LastTanVisitSalon , Client_General.ID , Client_General.FirstName , Client_General.LastName , Client_General.LastTanVisit , Client_General.ClientUID order by 'TOTAL_SALES' desc , Client_General.ID , Client_General.FirstName , Client_General.LastName
sql-server-2005toprowcount
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
Sample data would have been helpful. Since we don't have any, let's pretend for a moment that `sys.tables` represents your locations table (is that `client_general`?), and `sys.columns` represents your customer sales tables (multiple columns per table sounds a lot like multiple customer sales per location). To get the top 10 customer sales, you just `CROSS APPLY` a `TOP 10` sub-query that ties the location from the inner query to the location in the outer query in the `WHERE` clause. SELECT OBJECT_NAME(st.object_id) AS TableName , TopTenColumns.name AS ColumnName , TopTenColumns.column_id AS ColumnPosition FROM sys.tables AS st CROSS APPLY ( SELECT TOP 10 * FROM sys.columns WHERE object_id = st.object_id ORDER BY column_id ) AS TopTenColumns
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you... i will try this and get back to you!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.