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:
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.
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:
Code:
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.