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
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
14 People are following this question.
