Author |
Topic |
gavakie
Posting Yak Master
221 Posts |
Posted - 2008-04-15 : 15:33:13
|
Select Top 15 T.Total - T.SalesTax - IsNull( ttTemp.Price,0) as Trans, T.Time, T.StoreID, StoreName, C.NameFrom Reporting.dbo.QSCHQ_TransactionWithPesoConversion TLeft Join (Select Time, Sum(Price) as Price, T.StoreIDFrom Reporting.dbo.QSCHQ_TransactionEntryWithPesoConversion TE Inner Join Reporting.dbo.QSCHQ_TransactionWithPesoConversion T on TE.StoreID = T.StoreID and TE.TransactionNumber = T.RecallIDWhere T.Time >= getdate()-365Group By Time, T.StoreID)ttTemp on T.Time = ttTemp.Time and T.StoreID = ttTemp.StoreIDInner Join QSCHQ.dbo.Cashier C on T.CashierID = C.[ID] and T.StoreID = C.StoreIDInner Join DelSolNet2.dbo.Store S on T.StoreID = S.StoreIDWhere T.Time >= getdate()-365 Order By T.Total - T.SalesTax - IsNull( ttTemp.Price,0) descSo 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
30421 Posts |
Posted - 2008-04-15 : 15:35:57
|
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" |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2008-04-15 : 15:37:57
|
No im using 2000, i need the top 15 transactions for each store, sorry i worded that bad. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 15:47:33
|
A quick'n'dirty solution can look like thisSelect T.Total - T.SalesTax - IsNull( ttTemp.Price,0) as Trans, T.Time, T.StoreID, StoreName, C.Name, IDENTITY(INT, 1, 1) AS RowIDINTO #StageFrom Reporting.dbo.QSCHQ_TransactionWithPesoConversion TLeft 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.StoreIDInner Join QSCHQ.dbo.Cashier C on T.CashierID = C.[ID] and T.StoreID = C.StoreIDInner Join DelSolNet2.dbo.Store S on T.StoreID = S.StoreIDWhere T.Time >= getdate() - 365Order By T.StoreID, T.Total - T.SalesTax - IsNull( ttTemp.Price,0) descselect t.trans, t.time, t.storeid, t.storename, t.namefrom #stage AS tinner join ( select storeid, min(rowid) as minrowid from #stage group by storeid ) AS x ON x.storeid = t.storeidwhere t.rowid between x.minrowid and x.minrowid + 14order by t.rowid E 12°55'05.25"N 56°04'39.16" |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2008-04-15 : 16:07:32
|
Im sorry it doesnt seem to be working, there should in essence be over 1500 rows its only bringing back 170 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 16:13:49
|
You have 100 different storeid's? E 12°55'05.25"N 56°04'39.16" |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2008-04-15 : 16:14:42
|
atleast |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 16:19:03
|
With my suggestion above, how many trans records per store do you get? E 12°55'05.25"N 56°04'39.16" |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2008-04-15 : 16:23:39
|
anywhere from 1-8 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 16:46:02
|
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" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-15 : 17:01:43
|
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 #StageWhat result does it return? E 12°55'05.25"N 56°04'39.16" |
|
|
|