Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Stored Procedure involving grouping

Answered Stored Procedure involving grouping

  • Tuesday, February 19, 2013 6:55 PM
     
     
    Sign In to Vote
    0

    Apologies but I have already posted a similar thread but I think this is slightly different in terms of grouping:

    I have the following table

    cid eid mark field1
    33 12 XOXXXXX  
    34 12 XXXOXXX  
    35 13 XXXXXXXOX  
    36 13 XXXXXXXXXOX  

    I want to be able to create a query that groups the eid field and carries out the following update

    cid eid mark field1
    33 12 XOXXXXX  
    34 12 XXXOXXX  

    Update both of the field1 to ‘Y’ Whichever field has the 0 last then the eid should be set to null.

    cid eid Mark field1
    33 12 XOXXXXX Yes 
    34   XXXOXXX Yes

    The query must work through the remaining groups and carry out the same update.  There may also be more than two of the same eid’s.


    Final Result

    cid eid mark field1
    33 12 XOXXXXX  Y
    34   XXXOXXX  Y
    35 13 XXXXXXXOX  Y
    36   XXXXXXXXXOX  Y

    If possible I would also like the query to create a table of the result (though this isn’t essential)

    cid eid Previouseid    Date
    33 12 12   1402012
    34   13    1402012
    35 13 13  1402012
    36   13   1402012



    • Edited by system243trd Tuesday, February 19, 2013 7:04 PM
    •  
     

All Replies

  • Tuesday, February 19, 2013 7:25 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0
     
    Create table test (cid int, eid int, mark varchar(50),  field1 varchar(10) null )
insert into test (cid, eid, mark) values (33, 12, 'XOXXXXX'),(34, 12, 'XXXOXXX'),(35, 13, 'XXXXXXXXOX'),(36, 13, 'XXXXXXXXXOX') 
;with mycte as
(select cid, eid, mark,field1, row_number() over( partition by eid order by charindex('O',Mark) DESC) rn from test)

Update mycte 
Set eid=Case WHEN rn=1 Then null Else eid End,field1='Yes'
 

SELECT * from Test
 

--Cleanup
drop table test

/*

cid	eid	mark	field1
33	12	XOXXXXX	Yes
34	NULL	XXXOXXX	Yes
35	13	XXXXXXXXOX	Yes
36	NULL	XXXXXXXXXOX	Yes


*/