Need help with select criteria 1

[JoeZim]

I have query results from a table, based on ID = '27489':

ID Stmt Date Stmt Yr Method
27489 2003-12-31 2003 Reviewed
27489 2004-12-31 2004 Reviewed
27489 2005-12-31 2005 Co.Prep'd
27489 2005-12-31 2005 Unqualified
27489 2005-12-31 2005 Reviewed

My results need to be more narrow, selecting 2 years most recent statments. However, If one Stmt Date has multiple records (i.e. like 2005-12-31 in the above table), I need to pick the Stmt based on column "Method", based on the following hierarchy:

a. Unqualified
b. Qualified
c. Reviewed
d. Compiled
e. Company Prepared
f. Tax Return

Example: If there are three spreads for the same Stmt Date(like the table above), the one spread that has Audit Method of “Unqualified” would be selected for 2005-12-31 based on the hierarchy.

What I need for final results are the 2 most recent statments based on Stmt Date, like this:

27489 2005-12-31 2005 Unqualified
27489 2004-12-31 2004 Reviewed

I'm struggling with how to evaluate the stmt dates that have multiple methods. Any suggestions?

Regards,
Joe

 

[hneal98]

What does your query look like so far? Also you stated you wanted the most recent statements based on the stmt date, however your second item is 2004. Should that not be 2005?

 

[AlexCuse]

JoeZim

If I am reading correctly, you will need to set up a lookup table that will allow ranking of your methods. Like this:

ID Method
1 Unqualified
2 Qualified

etc....

You then will need to do some funky joining. I am not sure how strong your SQL is, so if you have trouble with any of this just ask questions. There is a lot here that I can't really get into at the moment, but I would be more than happy to answer any questions.

[COLOR=green]--set up sample of real table
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (ID [COLOR=blue]int[/color], [stmt [COLOR=blue]date[/color]] [COLOR=#FF00FF]datetime[/color], [stmt [COLOR=#FF00FF]year[/color]] [COLOR=blue]int[/color], method [COLOR=blue]varchar[/color](50))
[COLOR=blue]insert[/color] @t 
[COLOR=blue]select[/color] 27489,    [COLOR=red]'2003-12-31'[/color],     2003,    [COLOR=red]'Reviewed'[/color] union all
[COLOR=blue]select[/color] 27489,    [COLOR=red]'2004-12-31'[/color],     2004,    [COLOR=red]'Reviewed'[/color]  union all
[COLOR=blue]select[/color] 27489,    [COLOR=red]'2005-12-31'[/color],     2005,    [COLOR=red]'Co.Prepd'[/color]  union all
[COLOR=blue]select[/color] 27489,    [COLOR=red]'2005-12-31'[/color],     2005,    [COLOR=red]'Unqualified'[/color]  union all
[COLOR=blue]select[/color] 27489,    [COLOR=red]'2005-12-31'[/color],     2005,    [COLOR=red]'Reviewed'[/color]

[COLOR=green]--set up sample 'rank table'
[/color][COLOR=blue]declare[/color] @rank [COLOR=blue]table[/color] (id [COLOR=blue]int[/color], method [COLOR=blue]varchar[/color](50))
[COLOR=blue]insert[/color] @rank
[COLOR=blue]select[/color] 1, [COLOR=red]'Unqualified'[/color]
union all [COLOR=blue]select[/color] 2, [COLOR=red]'Qualified'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'Reviewed'[/color]
union all [COLOR=blue]select[/color] 4, [COLOR=red]'Compiled'[/color]
union all [COLOR=blue]select[/color] 5, [COLOR=red]'Company Prepared'[/color]
union all [COLOR=blue]select[/color] 6, [COLOR=red]'Tax Return'[/color]


[COLOR=green]--this query will draw from both a subquery of the two tables (containing the minimum integer value 
[/color][COLOR=green]--for rank ID when grouped by everything (but method) in your real table
[/color][COLOR=green]--and your rank table, to get the corresponding method from subquery's rankID
[/color][COLOR=blue]select[/color] a.ID, a.[stmt [COLOR=blue]date[/color]], a.[stmt [COLOR=#FF00FF]year[/color]], b.method
[COLOR=blue]from[/color]
(
[COLOR=blue]select[/color] z.ID, z.[stmt [COLOR=blue]date[/color]], z.[stmt [COLOR=#FF00FF]year[/color]], [COLOR=#FF00FF]min[/color](x.ID) [COLOR=blue]as[/color] RankID
[COLOR=blue]from[/color] @t z
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] @rank x
[COLOR=blue]on[/color] z.method = x.method
[COLOR=blue]group[/color] [COLOR=blue]by[/color] z.ID, z.[stmt [COLOR=blue]date[/color]], z.[stmt [COLOR=#FF00FF]year[/color]]
) a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
@rank b
[COLOR=blue]on[/color] a.RankID = b.ID 
[COLOR=blue]where[/color] a.[stmt [COLOR=blue]date[/color]] >= [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]year[/color], -2, [COLOR=#FF00FF]getdate[/color]())

Play around with this a bit (it uses table variables so it won't hurt anything) and see if this is what you're after.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JoeZim]

Thanks for your help Alex. I actually had a bunch more columns in my sp, so I used your example and tailored it to my needs. Works well!

Regards,
Joe

 

[AlexCuse]

Glad you got it working Joe!

Thanks for the pink thingy

Ignorance of certain subjects is a great part of wisdom