April 20, 2010 at 10:13 am
Please see the attached picture for the table structure I am dealing with. Essentially people will be tagged to an organisation and tagged within a certain capacity. This system has been in place and is working fine. For quite a few organisations we've introduced a higher level organisation - their group -think umbrella companies all owned by the same management company etc.
Now for each 'group' which has at least one organisation with somebody assigned I would like to assign this person to all other organisations within the group (and within that same capacity).
i.e. Group A has Organisations X, Y and Z. Person 1 is assigned to Organisation Y, I would want them to be assigned to organisation X and Z too.
Now I can easily identify which Groups have an organisation with somebody assigned, I'm just not sure how to insert the additional records in the example above for X and Z.
A colleague suggested a cursor to me, something I have little knowledge of, and something I know is frowned on in virtually every situation!
I know this may not be the best explanation - it's taken me long enough to come up with it, and perhaps not the neatest way to explain it (theoretical Excel diagrams lol) but hopefully somebody can assist...
April 20, 2010 at 10:19 am
It would be better if you provided table DDL (CREATE TABLE statement(s)), sample data (series of INSERT INTO TABLE statements) for the table(s) involved, and expected results based on the sample data.
April 20, 2010 at 10:24 am
I thought somebody might suggest something like that. For this particular problem I've over simplified the tables involved, and some of them don't even exist yet - so think of this more of a conceptual question rather than a here's the tables and fields what code do I need?
Not sure if that just makes it sound vague and rubbish, but hopefully you can see where I'm coming from.
April 20, 2010 at 10:30 am
My suggestion then, throw something together that mirrors the problem you are trying to solve and post that with sample data and expected results.
April 20, 2010 at 11:19 am
This would be for all records in your hypothetical. You can obviously narrow down the action with your WHERE.
insert into OrganisationAssignments (OrganisationID, Initials_Assigned, RoleID)
select
oNot.OrganisationID,
oa.Initials_Assigned,
oa.RoleID
from OrganisationAssignments oa
join OrganisationGroupLookup oYes--the current assignment
on oYes.OrganisationID = oa.OrganisationID
join [Group] g
on gd_execprocs.GroupID = ogl.GroupID
join OrganisationGroupLookup oNot--same group but
on oNot.GroupID = g.GroupID and--not the same org
oNot.OrganisationID <> ao.OrganistionID
where not exists (select sq.RoleID--check to make sure it's
from OrganisationAssignments sq --not already there
where sq.OrganisationID = oNot.OrganisationID and
sq.Initials_Assigned = oa.Initials_Assigned)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply