query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-03 : 14:31:46

Hi All,

I have two tables, one is header table and one is details table. I have the following data in my Details table

Details Table

HNum DateTimeGroup SN Severe

AB222 201108250554 1 3
CD555 201108250554 243 2
BQ77 201108210365 999 4
AB222 201108250554 1 9
AB222 201108250554 1 10
AB222 201108250553 1 10

header Table

HNum DateTimeGroup SN Severe

AB222 201108250554 1 3
CD555 201108250554 243 2
BQ77 201108210365 999 4

The primary keys in HeaderTable and details table is the combination of HNum, DateTimeGroup and SN. I need to find out the higheset severe for the combination of the primary keys in the details table and then update the header table severe with that number so from the above table, I need to get this line from the details table because it has the higheset severe

AB222 201108250554 1 10

out of

AB222 201108250554 1 3
AB222 201108250554 1 9
AB222 201108250554 1 10

I discareded this line because its dateTimeGroup is deifferent than other 3, I am only taking the primary keys

AB222 201108250553 1 10

and then update the header table this record severe to 10

AB222 201108250554 1 3

so this will be

AB222 201108250554 1 10

any help will be apprecaited.

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-03 : 15:43:20

I really need help with this query. any hint will also be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 15:53:59

[code]
UPDATE h
SET h.Severe = d.Severe
FROM Header h
INNER JOIN (SELECT HNum, DateTimeGroup, SN,MAX(Severe) AS Severe
FROM Details
GROUP BY HNum, DateTimeGroup, SN
)d
ON d.HNum = h.HNum DateTimeGroup
AND d.DateTimeGroup = h.DateTimeGroup
AND d.SN = h.SN
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources