Real Blonde Moment - Statement Help

Question

Post reply

Real Blonde Moment - Statement Help

SQL_Enthusiast

Hall of Fame

Points: 3805
More actions
February 18, 2011 at 6:52 pm

#247048

Here's the problem...
I have 1000's of code(s) in the table. Each one has several entries with effective dates. I need to get a query that pulls the max effective date for each distinct code. Thoughts?
select guid, code, effective_date, quantity
from LIST_PRICES where code = '000001'
guid code effective_date quantity
-------------------------------- ----------- ------------------------ --------C0A80116000000F010096AD700000C4F 000001 1990-01-01 00:00:00.000 1.000
C0A80116000000F010096AD700000C50 000001 1991-01-01 00:00:00.000 1.000
C0A80116000000F010096AD700000C51 000001 1992-01-01 00:00:00.000 1.000
C0A80116000000F010096AD700000C52 000001 1993-01-01 00:00:00.000 1.000
C0A80116000000F299CBB7C30000151C 000001 1995-01-18 00:00:00.000 1.000
C0A80116000000EF867DFA2E00000372 000001 2002-08-01 00:00:00.000 1.000
UPDP0E93241488BE4396A8818D2D92DD 000001 2010-01-01 00:00:00.000 1.000
UPDPFED314B8E0FB48A7B79817C6AB76 000001 2010-08-01 00:00:00.000 1.000
UPDP823D37F0F06B41C2BC231475982F 000001 2010-08-24 00:00:00.000 1.000

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQL_Enthusiast Hall of Fame Points: 3805 More actions · Answer 1

I am truly my own teacher... I got it to work:w00t:

select code, MAX(effective_date) as eff_date

from LIST_PRICES

group by CODE

having MAX(EFFECTIVE_DATE) > 0

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

SQL_Student (2/18/2011)
I am truly my own teacher... I got it to work:w00t:
select code, MAX(effective_date) as eff_date
from LIST_PRICES
group by CODE
having MAX(EFFECTIVE_DATE) > 0

Well done! I love it when folks science things out on their own. 🙂 And thanks for posting your solution.

Shifting gears, I don't believe you need the HAVING clause unless you expect some of the codes to be listed without having an Effective_Date.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)