Group Ranking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group Ranking

Author

Topic

BCrowe
Starting Member

23 Posts

Posted - 2005-01-11 : 12:08:15

I need to rank customer sales by Division/FiscalPeriod/FiscalYear in order of sales and i am hoping that someone in here can help. I can obtain the necessary records with the following statement with the exception of the rank.


SELECT CustID,
	Right(GLAcctNo, 2) As Division,
	Month(PostDate) As FiscalPeriod,
	Year(PostDate) As FiscalYear,
	Sum(ExtAmt) As Sales
FROM vdv_gw_sales
WHERE Month(PostDate) = 11 and Year(PostDate) = 2004
GROUP BY Month(PostDate), Year(PostDate), Right(GLAcctNo, 2), CustID
ORDER BY FiscalYear, FiscalPeriod, Division, Sales DESC

I need the rank to reset at each GLAcctNo change.
I would prefer to do this without creating a new table but am open to any suggestion that solves the problem.
Obviously this could be solved with a CURSOR but I would prefer to avoid this if possible.

Thank You,
BCrowe

BCrowe
Starting Member

23 Posts

Posted - 2005-01-11 : 12:09:14

Correction:

I need the rank to reset at each Division change not GLAcctNo

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-11 : 13:30:03

Here's one way (untested, but I think it should work) -- However, this is going to perform absolutely horribly if you have a big set of data; you have no predicates anywhere... It would be much wiser to do this on the client side.


SELECT CustID,
	Right(GLAcctNo, 2) As Division,
	Month(PostDate) As FiscalPeriod,
	Year(PostDate) As FiscalYear,
	Sum(ExtAmt) As Sales,
	(SELECT COUNT(DISTINCT Month(s1.PostDate) + Year(s1.PostDate))
	FROM vdv_gw_sales s1
	WHERE Right(s1.GLAcctNo, 2) = Right(vdv_gw_sales.GLAcctNo, 2)
	AND ((Year(s1.PostDate) = Year(vdv_gw_sales.PostDate) AND Month(s1.PostDate) > Month(vdv_gw_sales.PostDate)
	OR (Year(s1.PostDate) > Year(vdv_gw_sales.PostDate)))
FROM vdv_gw_sales
WHERE Month(PostDate) = 11 and Year(PostDate) = 2004
GROUP BY Month(PostDate), Year(PostDate), Right(GLAcctNo, 2), CustID
ORDER BY FiscalYear, FiscalPeriod, Division, Sales DESC

BCrowe
Starting Member

23 Posts

Posted - 2005-01-11 : 13:57:16

Didn't work...part of that may be because I didn't explain the problem correctly. I have a couple of thousand customers that I want to rank their purchases within each division(department might be a better analogy) within each month. From their invoices I can obtain a customers total purchases by division and month and even sort them appropriately. All I need to do is to be able to assign the rank within each division/month. The purpose is to generate a report per customer that gives their purchases by division and month along with their relative rank.


SELECT CustID,
	Right(GLAcctNo, 2) As Division,
	Month(PostDate) As FiscalPeriod,
	Year(PostDate) As FiscalYear,
	Sum(ExtAmt) As Sales
FROM vdv_gw_sales
GROUP BY Month(PostDate), Year(PostDate), Right(GLAcctNo, 2), CustID
ORDER BY FiscalYear, FiscalPeriod, Division, Sales DESC

BCrowe

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-11 : 14:58:30

Can you post DDL, sample data, and sample output?

BCrowe
Starting Member

23 Posts

Posted - 2005-01-11 : 15:03:17

I was able to come up with a solution using a CURSOR...

DECLARE @Rank int
DECLARE @RankPrev int
DECLARE @CustID varchar(50)
DECLARE @Division char(2)
DECLARE @DivisionPrev char(2)
DECLARE @FiscalPeriod int
DECLARE @FiscalYear int
DECLARE @Sales decimal(15,3)

SET @DivisionPrev = ''

DECLARE @tblCustomerSales TABLE(
   Rank int DEFAULT 0,
   CustID char(12),
   Division char(2),
   FiscalPeriod int,
   FiscalYear int,
   Sales decimal(15,3)
)

INSERT INTO @tblCustomerSales (CustID, Division, FiscalPeriod, FiscalYear, Sales)
SELECT CustID,
   Right(GLAcctNo, 2) As Division,
   Month(PostDate) As FiscalPeriod,
   Year(PostDate) As FiscalYear,
   Sum(ExtAmt) As Sales
FROM vdv_gw_sales
GROUP BY Month(PostDate), Year(PostDate), Right(GLAcctNo, 2), CustID
ORDER BY FiscalYear, FiscalPeriod, Division, Sales DESC


DECLARE crsRank CURSOR LOCAL FORWARD_ONLY FOR
   SELECT Rank, CustID, Division, FiscalPeriod, FiscalYear, Sales
   FROM @tblCustomerSales
FOR UPDATE OF Rank

OPEN crsRank

FETCH NEXT FROM crsRank
   INTO @Rank, @CustID, @Division, @FiscalPeriod, @FiscalYear, @Sales

WHILE @@FETCH_STATUS = 0
BEGIN
   IF @Division <> @DivisionPrev
   BEGIN
      SET @RankPrev = 0
      SET @DivisionPrev = @Division
   END
   SET @Rank = @RankPrev + 1
   SET @RankPrev = @RankPrev + 1

   UPDATE @tblCustomerSales SET Rank = @Rank
   WHERE CURRENT OF crsRank

   FETCH NEXT FROM crsRank
      INTO @Rank, @CustID, @Division, @FiscalPeriod, @FiscalYear, @Sales
END

CLOSE crsRank
DEALLOCATE crsRank


SELECT * FROM @tblCustomerSales
ORDER BY FiscalYear, FiscalPeriod, Division, Rank

BCrowe

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-01-12 : 05:24:30

Would be far better (if possible) to do this at the presentation layer.

BCrowe
Starting Member

23 Posts

Posted - 2005-01-12 : 16:05:28

I agree but I don't want to send 60k records to Crystal Reports. The report that is going to use this data is CustID specific so only about 120 records are necessary to complete the form.

I've settled on storing the data in a table. I came to the realization that only the current period and possibly the previous are dynamic so that if I only need to run a few thousand records through the cursor and append them to the table before querying the table. It works and frankly is much more efficient and compact than what the $125/hr consultant wrote.

Thank you all for your input...i'm sure i'll be back for more later
BCrowe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources