looking for the - 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

SQL Server 2005 Forums

Transact-SQL (2005)

looking for the

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-05-05 : 17:19:40

I am trying to

If there is a previous service_key exist in Start_service table group by reg_key ( I sorted by service_key and assigned a row id group by reg_key ) ,set Action column as 'T' in Start_Service table

If there is a next service_key from service_key in End_service table exist in Start_service table group by reg_key ,set Action a column as 'T' in End_service table

For example, service_key = 234 in start_service table doesn’t have a previous service_key in Start_service table , so it doesn’t qualify for Action = 'T'
Next serviceKey = 237 in start_service table has a previous service_key = 234, so it qualify for Action = 'T'

Service_key = 234 in End_service table has a next service_key in Start_Service table., so it qualify for Action = 'T'
Service_key = 347 in End_service table doesn’t have a next serviceKey in Start_service table, so it does NOT qualify for Action =T'


Start_service 
 reg_key    service_key          Rowid       
 1001         234                  1
 1001         237                  2
 1001         238                  3
 1003         345                  1
 1003         347                  2
 1009         657                  1
 1011         777                  1
 1011         888                  2
 1011         999                  3
 1044         111                  1
1044          113                  2

End_service
Reg_key    service_key                    RowId       
1001            234                          1 
1003            347                          1              
1009            657                          1
1011            888                          1
1011            999                          2
1044           111                           1
1044           113                            2

result


Start service 
 reg_key    service_key         Rowid       Action
 1001         234                1
 1001         237                2              T
 1001         238                3              T
 1003         345                1              
 1003         347                2              T
 1009         657                1
 1011         777                1             
 1011         888                2              T
 1011         999                3              T
1044         111                 1
1044          113                2              T


End service
Reg_key    service_key              RowId         Action
1001            234                  1               T
1003            347                  1                
1009            657                  1               
1011            888                  1               T
1044           111                   1               T
1044           113                   2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 00:22:23

[code]UPDATE ss
SET ss.Action= CASE WHEN t.RecCount > 0 THEN 'T' END
FROM Start_Service ss
CROSS APPLY(SELECT COUNT(*) AS RecCount
FROM Start_Service
WHERE reg_key=ss.reg_key
AND service_key < ss.service_key)t

UPDATE es
SET es.Action= CASE WHEN t.RecCount > 0 THEN 'T' END
FROM End_Service es
CROSS APPLY(SELECT COUNT(*) AS RecCount
FROM End_Service
WHERE reg_key=es.reg_key
AND service_key > es.service_key)t[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources