quote:

---

anyone know how to display this properly? <code> doesn't seem to work.

---

-- remove from member where they are in both
delete mem 
from sims.sims_member mem
where mem.person_id IN
   (
	-- Join to members to remove
	select mem_remove.person_id
	from sims.sims_member mem_remove
	inner join  @temp_table_with_members ttwm
	on mem_remove.base_group_id = ttwm.intake_base_group_id 
	and ttwm.intake_to_keep is null
	-- join to members to keep
	inner join 	sims.sims_member mem_keep
	on mem_keep.person_id = mem_remove.person_id
	inner join  @temp_table_with_members ttwm2
	on mem_keep.base_group_id = ttwm2.intake_base_group_id 
	and ttwm2.intake_to_keep = 1

	where ttwm.pread_base_group_id = ttwm2.pread_base_group_id	-- pre-admission groups are the same.
	)
and mem.base_group_id IN
(select distinct intake_base_group_id 
	from @temp_table_with_members ttwm
	where ttwm.intake_to_keep is null)	-- find all unwanted intake groups

-- update member where they are in the intake group we are removing, but not in the intake group that is being kept
update mem set base_group_id = ttwm_keepint.intake_base_group_id
from sims.sims_member mem
inner join @temp_table_with_members ttwm
on ttwm.intake_base_group_id = mem.base_group_id
and ttwm.intake_to_keep is null 					-- this section gets all memberships where intake_to_keep is null
inner join @temp_table_with_members ttwm_keepint	-- link to through to the group to keep with the pre-admision group
on ttwm_keepint.pread_base_group_id =  ttwm.pread_base_group_id
and ttwm.intake_to_keep = 1
left outer join 						-- left join to correct memberships (see if they already exist)
	(select distinct person_id, base_group_id
	from sims.sims_member
	) mem_keep
on mem_keep.base_group_id = ttwm_keepint.intake_base_group_id
	and mem_keep.person_id = mem.person_id
where mem_keep.person_id is null