SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 top for each group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gavakie
Posting Yak Master

221 Posts

Posted - 04/15/2008 :  15:33:13  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
Select Top 15 T.Total - T.SalesTax - IsNull( ttTemp.Price,0) as Trans,
T.Time, T.StoreID, StoreName, C.Name
From Reporting.dbo.QSCHQ_TransactionWithPesoConversion T

Left Join

(Select Time, Sum(Price) as Price, T.StoreID
From Reporting.dbo.QSCHQ_TransactionEntryWithPesoConversion TE
Inner Join Reporting.dbo.QSCHQ_TransactionWithPesoConversion T
on TE.StoreID = T.StoreID
and TE.TransactionNumber = T.RecallID
Where T.Time >= getdate()-365
Group By Time, T.StoreID)ttTemp
on T.Time = ttTemp.Time
and T.StoreID = ttTemp.StoreID

Inner Join QSCHQ.dbo.Cashier C
on T.CashierID = C.[ID]
and T.StoreID = C.StoreID
Inner Join DelSolNet2.dbo.Store S on T.StoreID = S.StoreID
Where T.Time >= getdate()-365

Order By T.Total - T.SalesTax - IsNull( ttTemp.Price,0) desc


So this finds the top 15 transactions but it shows for say all stores I want to show the top 15 transactions for all stores.

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 04/15/2008 :  15:35:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You mean top 15 transactions for each and one store?
If you are using Microsoft SQL Server 2005, have a look at the ROW_NUMBER(), RANK() and DENSE_RANK() functions.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 04/15/2008 :  15:37:57  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
No im using 2000, i need the top 15 transactions for each store, sorry i worded that bad.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 04/15/2008 :  15:47:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
A quick'n'dirty solution can look like this
Select		T.Total - T.SalesTax - IsNull( ttTemp.Price,0) as Trans, 
		T.Time,
		T.StoreID,
		StoreName,
		C.Name,
		IDENTITY(INT, 1, 1) AS RowID
INTO		#Stage
From		Reporting.dbo.QSCHQ_TransactionWithPesoConversion T
Left Join	(
			Select		Time,
					Sum(Price) as Price,
					T.StoreID
			From		Reporting.dbo.QSCHQ_TransactionEntryWithPesoConversion TE 
			Inner Join	Reporting.dbo.QSCHQ_TransactionWithPesoConversion T on TE.StoreID = T.StoreID
						and TE.TransactionNumber = T.RecallID
			Where		T.Time >= getdate() - 365
			Group By	Time,
					T.StoreID
		) ttTemp on T.Time = ttTemp.Time
			and T.StoreID = ttTemp.StoreID
Inner Join	QSCHQ.dbo.Cashier C on T.CashierID = C.[ID]
			and T.StoreID = C.StoreID
Inner Join	DelSolNet2.dbo.Store S on T.StoreID = S.StoreID
Where		T.Time >= getdate() - 365
Order By	T.StoreID,
		T.Total - T.SalesTax - IsNull( ttTemp.Price,0) desc

select		t.trans,
		t.time,
		t.storeid,
		t.storename,
		t.name
from		#stage AS t
inner join	(
			select		storeid,
					min(rowid) as minrowid
			from		#stage
			group by	storeid
		) AS x ON x.storeid = t.storeid
where		t.rowid between x.minrowid and x.minrowid + 14
order by	t.rowid



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 04/15/2008 :  16:07:32  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
Im sorry it doesnt seem to be working, there should in essence be over 1500 rows its only bringing back 170
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 04/15/2008 :  16:13:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have 100 different storeid's?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 04/15/2008 :  16:14:42  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
atleast
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 04/15/2008 :  16:19:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
With my suggestion above, how many trans records per store do you get?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

gavakie
Posting Yak Master

221 Posts

Posted - 04/15/2008 :  16:23:39  Show Profile  Visit gavakie's Homepage  Click to see gavakie's MSN Messenger address  Reply with Quote
anywhere from 1-8
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 04/15/2008 :  16:46:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I'd say there is something wrong with the original query (which selects into the #stage table).



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 04/15/2008 :  17:01:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Run the two queries as suggestion above.
The first one by you, and the second one by me.

Add a third query,

SELECT COUNT(DISTINCT StoreID)
FROM #Stage

What result does it return?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000