Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Making Query SQL 2000 compatable Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 2:33 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:22 AM
Points: 903, Visits: 1,868
I have a query i wrote for 2008 to get the top 3 salesPeople per territory by their total sales. Its not too difficult in 2005/2008 with CTE's and Partitioning however i cannot get this to work in SQL 2000 where there are no CTE's.

Id appreciate any help or nudges in the right direction:

Query is a mockup of the actual query, columns, tables and data is all fictional:

with ST AS
( select
ROW_NUMBER() over(partition by TerritoryID order by sum(totalDue) desc) as rowID,
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID
)
select
territoryID
, salesPersonID
, SalesTotal
from ST
where RowID <=3
order by territoryID


Thanks!
Post #1448613
Posted Wednesday, May 1, 2013 2:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 12,917, Visits: 32,083
wow it's been a long time since i had to do this in SQL2000;
SQL 2000 doesn't support row_number() functions either.

if you can go without row_number, it just becomes a sub select:
select 
territoryID
, salesPersonID
, SalesTotal
from ( select
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID
) ST
--where RowID <=3
order by territoryID

if you need row_number, it's going to require a tempt table, and updating the temp table with a neat trick i learned form Jeff Moden; unfortunately, it cannot be done in a single statement, either.

this is untested, since i don't have the sample data, but this should be kind of close:


--===== Create a test table.  
-- This is NOT part of the solution.
IF OBJECT_ID('TempDB..#ST') IS NOT NULL
DROP TABLE #ST

--create the temp table, with placeholders for the partition by() of the row_number
select
identity(int,1,1) AS ID,
0 As rowID,
territoryID,
salesPersonID
, SalesTotal
INTO #ST
from (select
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID ) ST
ORDER BY TerritoryID, SalesPersonID

--we need a clustered index
ALTER TABLE #ST
ADD PRIMARY KEY CLUSTERED (TerritoryID)

DECLARE @PrevTerritoryID INT,
@PrevSeq INT

--update the table and the variable/counter all in the same statemetn.
UPDATE #ST
SET @PrevSeq = rowid = CASE WHEN TerritoryID = @PrevTerritoryID THEN @PrevSeq + 1 ELSE 1 END,
@PrevTerritoryID = TerritoryID
FROM #ST WITH(INDEX(0),TABLOCKX)


--check the results?
SELECT * FROM #ST
--WHERE rowID <=3
ORDER BY TerritoryID



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1448617
Posted Wednesday, May 1, 2013 6:09 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 4,576, Visits: 8,351
There is a simpler way.
No updates required:

--===== Create a test table.  
-- This is NOT part of the solution.
IF OBJECT_ID('TempDB..#ST') IS NOT NULL DROP TABLE #ST

DECLARE @N int
SET @N = 3 -- how many rows to retrieve for each group

--create the temp table, with Identity column playing the role of RowNumber
select
identity(int,1,1) AS ID,
territoryID, salesPersonID, SalesTotal
INTO #ST
from (select
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where SalesPersonID is not null
group by TerritoryID, SalesPersonID ) ST
ORDER BY TerritoryID,
salesTotal DESC -- arranging ID's within the each group

SELECT T.*
FROM #ST T
INNER JOIN (
SELECT TerritoryID,
MIN(ID) FromID, MIN(ID) + @N ToID
-- MIN(ID) is a starting point for rowcount within each group
FROM #ST
GROUP BY TerritoryID
) DT ON DT.TerritoryID = T.TerritoryID
AND T.ID BETWEEN DT.FromID AND DT.ToID
ORDER BY T.TerritoryID, T.ID

Post #1448657
Posted Thursday, May 2, 2013 12:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,095, Visits: 3,146
I think this will do it; performance will depend on the indexes available on the table:



select sales.*
from (
select distinct TerritoryID
from [Sales].[SalesOrderHeader]
) AS terrs
inner join
( select top 3
TerritoryID,
salesPersonID,
sum(TotalDue) as salesTotal
from [Sales].[SalesOrderHeader]
where
TerritoryID = terrs.TerritoryID and
SalesPersonID is not null
group by TerritoryID, SalesPersonID
order by sum(TotalDue) DESC
) AS sales on
sales.TerrorityID = terrs.TerritoryID




SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1448948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse