Group By with a Rank

[JoeZim]

I have the following table:

CustID StmtID StmtDate StmtYr Method (Rank)
29874 9 12/31/04 2004 Tax Return (6)
29874 7 12/31/03 2003 Co.Prep'd (5)
29874 8 12/31/03 2003 Tax Return (6)
29874 5 12/31/02 2002 Tax Return (6)
29874 4 12/31/01 2001 Tax Return (6)

** Note: the (Rank) is not in the table, but this is just for display purposes - Assigned in the proc

What I need to select is the 2 most-recent years statments. Based on the example table above:

CustID StmtID StmtDate StmtYr Method
29874 9 12/31/04 2004 Tax Return
29874 7 12/31/03 2003 Co.Prep'd

When there are two statements for a given StmtYr, I need to default to the AuditMethod Rank that is lowest (done in the proc). However, there could be 2 valid statements in one StmtYr, like the following example:

CustID StmtID StmtDate StmtYr Method
29874 9 12/31/04 2004 Tax Return (6)
29874 7 01/01/04 2004 Co.Prep'd (5)

So far, I have written the following:

select top 2
	Max(z.CustomerID) as CustID,
	Max(z.StatementID)as StmtID,
	Min(CASE z.AuditMethod
	        WHEN 'Unqualif''d' THEN 1
	        WHEN 'Qualified' THEN 2
	        WHEN 'Reviewed' THEN 3
	        WHEN 'Compiled' THEN 4
	        WHEN 'Co.Prep''d' THEN 5
	        WHEN 'Tax Return' THEN 6
	ELSE ''
	END) AS [RankID]
from 
	dbo.upHistStmtMTMMAS z,
	dbo.upCustomer c
where 
	z.CustomerID = c.CustomerID AND
	z.StatementType = 'Annual' AND
	z.StatementMonths = '12' AND
	(z.AuditMethod = 'Unqualif''d' OR
	z.AuditMethod = 'Qualified' OR
	z.AuditMethod = 'Reviewed' OR
	z.AuditMethod = 'Compiled' OR
	z.AuditMethod = 'Co.Prep''d' OR
	z.AuditMethod = 'Tax Return') AND
	z.StatementDate < GETDATE() AND
	c.ProfileNumber = '1104071'
group by
	z.StatementYear, z.StatementDate
order by 
	z.StatementYear desc

Which produces the following results:

CustID StmtID MethodRank
29874 9 6
29874 8 5

I need to get the following instead:

CustID StmtID MethodRank
29874 9 6
29874 7 5


The Max(z.StatementID) seems to be the culprit, but I need the StatementID field in the results so I can use it to pull additional data further down in the proc.

Hope this makes sense.

 

[JoeZim]

Please let me know if further explanation is needed.

Thanks

 

[gmmastros]

further explanation is needed.

-George

"the screen with the little boxes in the window." - Moron

 

[Rofeu]

Hi,

your top 2 relates to Max(z.StatementID)as StmtID, so it'll first take the top 2 statementIDs, which are in fact 9 and 8.

You could try ordering

ORDER BY z.StatementYear desc, CASE z.AuditMethod
            WHEN 'Unqualif''d' THEN 1
            WHEN 'Qualified' THEN 2
            WHEN 'Reviewed' THEN 3
            WHEN 'Compiled' THEN 4
            WHEN 'Co.Prep''d' THEN 5
            WHEN 'Tax Return' THEN 6
    ELSE ''
    END

See if that gives the right records.

Another option might be removing the Max from z.StatementID

Cheers,

Roel

 

[JoeZim]

Thanks for the replies. I decided to take a different approach. Since the result set will be on the small side (2 to 30 records), I used a cursor to accomplish my goal:

Declare @unique table
	(
	CustID int,
	StmtID int,
	StmtDate datetime,
	StmtYr numeric,
	RankID int
	)

Declare @cCustID int, @cStmtID int, @cStmtDate datetime, @cStmtYr numeric, @cRankID int, --Current Values
	@pcCustID int, @pcStmtID int, @pcStmtDate datetime, @pcStmtYr numeric, @pcRankID int,  --Previous Values
	@cRecCount int

Set @cRecCount = 0

DECLARE UniqueStmt CURSOR FOR
		select
			z.CustomerID as CustID,
			z.StatementID as StmtID,
			z.StatementDate as StmtDate,
			z.StatementYear as StmtYr,
			CASE z.AuditMethod
			        WHEN 'Unqualif''d' THEN 1
			        WHEN 'Qualified' THEN 2
			        WHEN 'Reviewed' THEN 3
			        WHEN 'Compiled' THEN 4
			        WHEN 'Co.Prep''d' THEN 5
			        WHEN 'Tax Return' THEN 6
			ELSE ''
			END AS [RankID]
		from 
			dbo.upHistStmtMTMMAS z,
			dbo.upCustomer c
		where 
			z.CustomerID = c.CustomerID AND
			z.StatementType = 'Annual' AND
			z.StatementMonths = '12' AND
			(z.AuditMethod = 'Unqualif''d' OR
			z.AuditMethod = 'Qualified' OR
			z.AuditMethod = 'Reviewed' OR
			z.AuditMethod = 'Compiled' OR
			z.AuditMethod = 'Co.Prep''d' OR
			z.AuditMethod = 'Tax Return') AND
			z.StatementDate < GETDATE() AND
			c.ProfileNumber = @profile
		order by 
			z.StatementDate desc, RankID asc

OPEN UniqueStmt

FETCH NEXT FROM UniqueStmt
	INTO @cCustID, @cStmtID, @cStmtDate, @cStmtYr, @cRankID
WHILE @@FETCH_STATUS = 0
BEGIN
	If @cRecCount = 0
		Begin
			Insert into @unique values (@cCustID, @cStmtID, @cStmtDate, @cStmtYr, @cRankID)
		End
	Else
	IF @cStmtYr = @pcStmtYr AND @pcStmtDate - @cStmtDate > 345
		Begin
			Insert into @unique values (@cCustID, @cStmtID, @cStmtDate, @cStmtYr, @cRankID)
		End
	Else
	IF @cStmtYr <> @pcStmtYr
		Begin
			Insert into @unique values (@cCustID, @cStmtID, @cStmtDate, @cStmtYr, @cRankID)
		End

	Set @pcCustID = @cCustID
	Set @pcStmtID = @cStmtID
	Set @pcStmtDate = @cStmtDate
	Set @pcStmtYr = @cStmtYr
	Set @pcRankID = @cRankID

	Set @cRecCount = @cRecCount + 1		
	FETCH NEXT FROM UniqueStmt
		INTO @cCustID, @cStmtID, @cStmtDate, @cStmtYr, @cRankID
END

CLOSE UniqueStmt
DEALLOCATE UniqueStmt

Joe