Finding Max and update

Question

Post reply

Finding Max and update

skmoh2

Mr or Mrs. 500

Points: 565
More actions
March 8, 2018 at 1:01 am

#410301

Hi , I have a scenario and need some idea to build that.
i have a table where multiple stat-codes exist with different part-no with diff dates.I need to Get the maximum dates on each stat-codes and update.
exp: STAT_CODE,DATE,PART_NO A123,2018-02-02,101 A123,2018-02-01,201 A123,2018-01-02,301
AB22,2017-02-09,201 AB22,2017-02-03,401 AB22,2017-02-04,501 AB22,2017-02-03,101
My Output should be,
A123,2018-02-02,101 A123,2018-02-02,201 A123,2018-02-02,301 AB22,2017-02-04,501 AB22,2017-02-04,401
Here i want the STAT_CD and the highest date and based on that i will update that date for all Part no

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

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

george_at_sql SSCommitted Points: 1967 More actions · Answer 1

update table a set a.date=(select max(b.date) from table b where b.stat_cd=a.stat_cd)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

This may work, although I don't understand why the date for AB22 shouldn't be 2017-02-09?

WITH MaxDates AS ( SELECT STAT_CODE , DATE , PART_NO , MAX(DATE) OVER (PARTITION BY STAT_CODE)) AS MaxDate ) UPDATE MaxDates SET DATE = MaxDate

John

sgmunson SSC Guru Points: 110639 More actions · Answer 3

skmoh2 - Thursday, March 8, 2018 1:01 AM
Hi , I have a scenario and need some idea to build that.
i have a table where multiple stat-codes exist with different part-no with diff dates.I need to Get the maximum dates on each stat-codes and update.
exp: STAT_CODE,DATE,PART_NO A123,2018-02-02,101 A123,2018-02-01,201 A123,2018-01-02,301
AB22,2017-02-09,201 AB22,2017-02-03,401 AB22,2017-02-04,501 AB22,2017-02-03,101
My Output should be,
A123,2018-02-02,101 A123,2018-02-02,201 A123,2018-02-02,301 AB22,2017-02-04,501 AB22,2017-02-04,401
Here i want the STAT_CD and the highest date and based on that i will update that date for all Part no

You'll need to be very specific as to why all the A123 records get the February 2nd date, but the AB22 records end up with 2 missing records, and use February 4th instead of February 9th.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)