SQL Select most current record date

I have a table that has multiple job records and each job record can have multiple accounting date entries.
For example:
Job#     Date           %             $
1            1/1/11        50%      $100
1            2/1/11        75%      $150
2            1/1/11        50%      $75
2             3/1/11       60%        $90

I need to get the MOST RECENT Date entry per job record and its % and $ for that record.
Thanks!
Norm-alNetwork EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:
//you havent really given the field names

with cte
as
(  select Job#,     Date,          [ %],            [ $], ROW_NUMBER() over (partition by date order by date desc) as RN
  from yourtable
)

select * from CTE where RN = 1
0
Ephraim WangoyaCommented:
sorry, partion by Job#

with cte
as
(  select Job#,     Date,          [ %],            [ $], ROW_NUMBER() over (partition by Job# order by date desc) as RN
  from yourtable
)

select * from CTE where RN = 1
0
Norm-alNetwork EngineerAuthor Commented:
The fields are JobID, Date, Percent and Amount
I want one JobID with the most recent date entry.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bryan ButlerCommented:
select <date column name> DCN, <%column name>, <$colun name> from <tablename> where DCN In (select MAX(DCN) from <tablename>)


0
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,

you can use following query

select tbl.*
From tbl
      Inner Join (select cast(Job as varchar) + cast(max(Date) as varchar) as datakey from tbl Group by Job) as tempTbl
            on astempTbl.datakey = cast(Job as varchar) + cast(Date as varchar)

Thanks
0
Bryan ButlerCommented:
with your var names:

select Date, Percent, Amount from <tablename> where Date In (select MAX(Date) from <tablename>)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ephraim WangoyaCommented:
here
with cte
as
(  select JobID, [Date], [Percent], Amount, ROW_NUMBER() over (partition by JobID order by [date] desc) as RN
  from yourtable
)

select * from CTE where RN = 1

Open in new window

0
Bryan ButlerCommented:
Ooops, misread it.  

select TOP 1 (Date), JobID, Percent, Amount from <tablename> groupby JobID
0
mani2meetCommented:
SELECT TOP 1 JobID, Date, Percent, Amount FROM tabename order by Date desc
0
mani2meetCommented:
Correction:
SELECT TOP 1 JobID, (Date), Percent, Amount FROM tabename order by (Date) desc
0
Norm-alNetwork EngineerAuthor Commented:
This seems to work:

SELECT     JobID, Jobnum, Part, PercentDate, CurrentPercent, CurrentAmount
FROM         WIPView AS WIPView_1
WHERE     (PercentDate IN
                          (SELECT     MAX(PercentDate) AS Expr1
                            FROM          WIPView
                            WHERE      (JobID = WIPView_1.JobID)))

Now part 2 of my problem... now that I have this correct result set, I am trying to join it with a Job table where WIPView.JobID = PCJobData.ID so I can see all job records and also show the percentdate, currentpercent and currentamount for those that have it. I should be getting record count of 26000, I am only getting 2700 (the number of records in the WIPView.
0
Norm-alNetwork EngineerAuthor Commented:
Hmm... I'm not sure if this is best practice or not but what I did was created a new view with the above SELECT statement that gave me the most recent date entries and created a 2nd view and joined this view with my jobs table. Looks like that might work... seemed like a lot of work just to get the most recent dates to join with my jobs table....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.