SQLTeam.com | Weblogs | Forums

How to check nested condition in sql?

sql2012

#1

Hi Team,

This is my data which showing in a table.
I want update my table like below giving scenario
1)First I have to check max sodate then update all matching group work with that cluster which have max sodate
2) If sodate is not available then I have to check max sodateany then update all matching group work with that cluster which have max sodateany
3)If sodateany not available then I have to check max lastedate then update all matching group work with that cluster which have max lastedate

work |cluster | sodate |sodateany | lastedate

1st scenario

22356894 | 123645 | 2014-05-07 | 2015-05-17 | 2015-03-16
22356894 | 126 | 2014-05-05 | 2015-05-18 | 2015-03-17
22356894 | 145 | 2014-04-03 | 2015-04-17 | 2015-03-18

2nd scenario

24432464 | 456 | ----- | 2015-05-17 | 2015-03-16
24432464 | 321 | 2014-05-05 | 2015-05-18 | 2015-03-17
24432464 | 215 | 2014-04-03 | 2015-04-17 | 2015-03-18

3rd scenario

24432423 | 987 | -- | 2015-05-17 | 2015-03-16
24432423 | 56 | -- | 2015-05-18 | 2015-03-17

4th scenario

24432412 |326 | -- |-- |2015-03-16
24432412 |576 | -- | 2015-05-18 | 2015-03-17

5th scenario
-------------|-- | | |
24432423 |326 | -- |-- |2015-03-16
24432423 |305 | -- | -- | 2015-03-17

o/p looking like

This output comming by using below scenario.

1)First I have to check max sodate then update all matching group work with that cluster which have max sodate
2) If sodate is not available then I have to check max sodateany then update all matching group work with that cluster which have max sodateany
3)If sodateany not available then I have to check max lastedate then update all matching group work with that cluster which have max lastedate

work |cluster | sodate | sodateany| lastedate

1st scenario

22356894 |123645 | 2014-05-07 |2015-05-17 | 2015-03-16
22356894 |123645 | 2014-05-05 |2015-05-18 | 2015-03-17
22356894 |123645 | 2014-04-03 |2015-04-17 | 2015-03-18

2nd scenario

24432464 |321 | ----- |2015-05-17 | 2015-03-16
24432464 |321 | 2014-05-05 | 2015-05-18 | 2015-03-17
24432464 |321 | 2014-04-03 |2015-04-17 | 2015-03-18

3rd scenario

24432423 |56 | -- | 2015-05-17 | 2015-03-16
24432423 |56 | -- | 2015-05-18 | 2015-03-17

4th scenario

24432412 |576 | -- |-- | 2015-03-16
24432412 |576 | -- | 2015-05-18 | 2015-03-17

5th scenario

24432423 |305 | -- |-- | 2015-03-16
24432423 |305 | -- | -- | 2015-03-17

Here I am uploading problem image where data will clear.

Thanks