November 14, 2005 at 4:32 pm
I'm designing a database and am having a problem modeling the duty assignments part
A person can only have one duty assignment
A duty assignment is made up of a squadron, a division, a workcenter and possibly a team
Squadron names are unique
There can be multiple uses of division, workcenter and team names
Once a duty assignment has been built and used it never gets deleted
(they get deactivated - saved for history).
PS: I know this is an issue if they want to rename a division or workcenter - I'll just script it)
There will be an employee table with a DutyAssignmentID column
I think understand the idea of many-to-many relationships with junction tables:
table
junction table
table
I want to list the org structure - but when I try this with 3 levels
(squadrons, divisions and workcenters) my query doesn't work.
Workcenters assigned to a squadron/division appear under the other squadrons with
that same division name.
Query 1 - 2 levels - works:
SELECT S.SquadronID, S.Squadron, D.DivisionID, D.Division
From dbo.A_Squadrons S
Left Join dbo.A_SquadronDivision SD ON S.SquadronID = SD.SquadronID
Left Join dbo.A_Divisions D ON SD.DivisionID = D.DivisionID
Query 2 - 3 levels - DOESNT work:
SELECT S.SquadronID, S.Squadron, D.DivisionID, D.Division, W.WorkcenterID, W.Workcenter
From dbo.A_Squadrons S
Left Join dbo.A_SquadronDivision SD ON S.SquadronID = SD.SquadronID
Left Join dbo.A_Divisions D ON SD.DivisionID = D.DivisionID
Left Join dbo.A_DivisionWorkcenter DW ON D.DivisionID = DW.DivisionID
Left Join dbo.A_Workcenters W ON DW.WorkcenterID = W.WorkcenterID
The struc is below (I know I have more constraints and indexes to build etc...)
My original indea was to not use junctions tables, just have the admin build the
duty assignments from the squadron, division, workcenter and team tables
using a DutyAssignments table with columns for each ID, but was told that was a bad model because I'd have a huge number of rows...
While the size issue is not a factor because the org is small (7 squadrons, 30ish divisions and 100-ish workcenters) I still want to use best practices...
As I've written this, it seems like a possible solution would be for me to use 3 ID colums in my
workcenter junction table.
I hope I've written enough to get the idea across here.
I really don't see why my original solution was wrong and I don't see how the proposed solution is correct.
-- DutyAssignments
CREATE TABLE dbo.A_DutyAssignments (
DutyAssignmentID int NOT NULL PRIMARY KEY,
OrganizationID int NOT NULL,
SquadronID int NOT NULL REFERENCES dbo.A_Squadrons(SquadronID),
DivisionID int NOT NULL,
WorkcenterID int,
TeamID int,
Active bit NOT NULL )
GO
CREATE UNIQUE INDEX PK_A_DutyAssignments ON dbo.A_DutyAssignments(DutyAssignmentID)
GO
-- Squadrons
CREATE TABLE dbo.A_Squadrons (
SquadronID int identity NOT NULL PRIMARY KEY,
Squadron varchar(10) NOT NULL,
Longname varchar(50) NOT NULL,
SEQ smallint NOT NULL,
Active bit NOT NULL )
GO
CREATE UNIQUE INDEX PK__A_Squadrons__15261146 ON dbo.A_Squadrons(SquadronID)
GO
-- SquadronDivision
CREATE TABLE dbo.A_SquadronDivision (
SquadronDivisionID int identity NOT NULL,
SquadronID int NOT NULL REFERENCES dbo.A_Squadrons(SquadronID),
DivisionID int NOT NULL REFERENCES dbo.A_Divisions(DivisionID) )
GO
ALTER TABLE dbo.A_SquadronDivision ADD PRIMARY KEY (SquadronID, DivisionID)
GO
CREATE UNIQUE INDEX PK_A_SquadronDivision ON dbo.A_SquadronDivision(SquadronID, DivisionID)
GO
-- Divisions
CREATE TABLE dbo.Divisions2 (
DivisionID int identity NOT NULL PRIMARY KEY,
Division varchar(10) NOT NULL,
Longname varchar(50) NOT NULL,
SEQ smallint NOT NULL,
Active bit NOT NULL )
GO
CREATE UNIQUE INDEX PK_Divisions2 ON dbo.Divisions2(DivisionID)
GO
-- DivisionsWorkcenter
CREATE TABLE dbo.DivisionWorkcenter2 (
DivisionWorkcenterID int identity NOT NULL,
DivisionID int NOT NULL REFERENCES dbo.Divisions2(DivisionID),
WorkcenterID int NOT NULL REFERENCES dbo.Workcenters2(WorkcenterID) )
GO
ALTER TABLE dbo.DivisionWorkcenter2 ADD PRIMARY KEY (DivisionID, WorkcenterID)
GO
CREATE UNIQUE INDEX PK_DivisionWorkcenter2 ON dbo.DivisionWorkcenter2(DivisionID, WorkcenterID)
GO
-- Workcenters
CREATE TABLE dbo.Workcenters2 (
WorkcenterID int identity NOT NULL PRIMARY KEY,
Workcenter varchar(10) NOT NULL,
Longname varchar(50) NOT NULL,
SEQ smallint NOT NULL,
Active bit NOT NULL )
GO
CREATE UNIQUE INDEX PK_Workcenters2 ON dbo.Workcenters2(WorkcenterID)
GO
-- WorkcenterTeam
-- Teams
Thanks - Marc
November 15, 2005 at 11:33 am
Table structure looks fine.
Can you rescript your table generation? It gives some errors on tablenames.
If you want to see the strict org structure, choose inner joins instead of left-joins.
November 15, 2005 at 11:35 pm
Jo, thanks for your reply,
1. Structure (sorry about that - that's what happens when you hurry and don't have a great tool - I just downloaded ApexSQL Script - that's much better!). I've rebuilt this thing so many times, I probably don't have the all of the indexs (uniques etc) but this should be enough to get it going...
/* ----------------------------------------------
DESCRIPTION: Structure Definition Script for Object(s)
TABLEs:
[dbo].[A_SquadronDivision],
[dbo].[A_Squadrons],
[dbo].[A_DutyAssignments]
[dbo].[A_Workcenters],
[dbo].[A_DivisionWorkcenter],
[dbo].[A_WorkcenterTeam]
[dbo].[A_Divisions],
[dbo].[A_Teams]
DATABASE: Marc-Home.JPS
AUTHOR:
DATE: 11/16/2005 12:21:21 AM
------------------------------------------------------------ */
-- =======================================================
-- SCRIPT HEADER
-- =======================================================
SET NOEXEC OFF
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
SET XACT_ABORT ON
GO
-- BEGINNING TRANSACTION a
BEGIN TRANSACTION _a_
GO
-- =======================================================
-- SCRIPT NON-ORDERED OBJECTS
-- =======================================================
CREATE TABLE [dbo].[A_SquadronDivision] (
[SquadronDivisionID] int IDENTITY(1,1) NOT NULL,
[SquadronID] int NOT NULL,
[DivisionID] int NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_Squadrons] (
[SquadronID] int IDENTITY(1,1) NOT NULL,
[Squadron] varchar(10) NOT NULL,
[Longname] varchar(50) NOT NULL,
[SEQ] smallint NOT NULL,
[Active] bit NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_DutyAssignments] (
[DutyAssignmentID] int NOT NULL,
[OrganizationID] int NOT NULL,
[SquadronID] int NOT NULL,
[DivisionID] int NOT NULL,
[WorkcenterID] int NULL,
[TeamID] int NULL,
[Active] bit NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_Workcenters] (
[WorkcenterID] int IDENTITY(1,1) NOT NULL,
[Workcenter] varchar(10) NOT NULL,
[Longname] varchar(50) NOT NULL,
[SEQ] smallint NOT NULL,
[Active] bit NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_DivisionWorkcenter] (
[DivisionWorkcenterID] int IDENTITY(1,1) NOT NULL,
[DivisionID] int NOT NULL,
[WorkcenterID] int NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_WorkcenterTeam] (
[WorkcenterTeamID] int IDENTITY(1,1) NOT NULL,
[WorkcenterID] int NOT NULL,
[TeamID] int NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_Divisions] (
[DivisionID] int IDENTITY(1,1) NOT NULL,
[Division] varchar(10) NOT NULL,
[Longname] varchar(50) NOT NULL,
[SEQ] smallint NOT NULL,
[Active] bit NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
CREATE TABLE [dbo].[A_Teams] (
[TeamID] int IDENTITY(1,1) NOT NULL,
[Team] varchar(10) NOT NULL,
[SEQ] smallint NOT NULL,
[Active] bit NOT NULL
) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- =======================================================
-- SCRIPT ORDERED OBJECTS AND TABLE FOREIGN KEYS
-- =======================================================
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_Divisions]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_Divisions] '
GO
ALTER TABLE [dbo].[A_Divisions] ADD CONSTRAINT [PK_A_Divisions] PRIMARY KEY ([DivisionID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_Divisions] ADD CONSTRAINT [DF_A_Divisions_SEQ] DEFAULT (0) FOR [SEQ]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_Divisions] ADD CONSTRAINT [DF_A_Divisions_Active] DEFAULT (1) FOR [Active]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_Squadrons]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_Squadrons] '
GO
ALTER TABLE [dbo].[A_Squadrons] ADD CONSTRAINT [PK_A_Squadrons] PRIMARY KEY ([SquadronID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_Squadrons] ADD CONSTRAINT [DF_A_Squadrons_Active] DEFAULT (1) FOR [Active]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_Squadrons] ADD CONSTRAINT [DF_A_Squadrons_SEQ] DEFAULT (0) FOR [SEQ]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_Teams]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_Teams] '
GO
ALTER TABLE [dbo].[A_Teams] ADD CONSTRAINT [PK_A_Teams] PRIMARY KEY ([TeamID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_Workcenters]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_Workcenters] '
GO
ALTER TABLE [dbo].[A_Workcenters] ADD CONSTRAINT [PK_A_Workcenters] PRIMARY KEY ([WorkcenterID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_Workcenters] ADD CONSTRAINT [DF_A_Workcenters_SEQ] DEFAULT (0) FOR [SEQ]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_Workcenters] ADD CONSTRAINT [DF_A_Workcenters_Active] DEFAULT (1) FOR [Active]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_DivisionWorkcenter]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_DivisionWorkcenter] '
GO
ALTER TABLE [dbo].[A_DivisionWorkcenter] ADD CONSTRAINT [PK_A_DivisionWorkcenter] PRIMARY KEY ([DivisionID], [WorkcenterID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_DivisionWorkcenter] ADD CONSTRAINT [FK_A_DivisionWorkcenter_A_Divisions] FOREIGN KEY ([DivisionID]) REFERENCES [dbo].[A_Divisions] ([DivisionID])
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_DivisionWorkcenter] ADD CONSTRAINT [FK_A_DivisionWorkcenter_A_Workcenters] FOREIGN KEY ([WorkcenterID]) REFERENCES [dbo].[A_Workcenters] ([WorkcenterID])
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_DutyAssignments]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_DutyAssignments] '
GO
ALTER TABLE [dbo].[A_DutyAssignments] ADD CONSTRAINT [PK_A_DutyAssignments] PRIMARY KEY ([DutyAssignmentID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_DutyAssignments] ADD CONSTRAINT [FK_A_DutyAssignments_A_Squadrons] FOREIGN KEY ([SquadronID]) REFERENCES [dbo].[A_Squadrons] ([SquadronID])
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_SquadronDivision]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_SquadronDivision] '
GO
ALTER TABLE [dbo].[A_SquadronDivision] ADD CONSTRAINT [PK_A_SquadronDivision] PRIMARY KEY ([SquadronID], [DivisionID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_SquadronDivision] ADD CONSTRAINT [FK_A_SquadronDivision_A_Divisions] FOREIGN KEY ([DivisionID]) REFERENCES [dbo].[A_Divisions] ([DivisionID])
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_SquadronDivision] ADD CONSTRAINT [FK_A_SquadronDivision_A_Squadrons] FOREIGN KEY ([SquadronID]) REFERENCES [dbo].[A_Squadrons] ([SquadronID])
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- -------------------------------------------------------
-- Script for Table: [dbo].[A_WorkcenterTeam]
-- -------------------------------------------------------
Print 'Script for Table: [dbo].[A_WorkcenterTeam] '
GO
ALTER TABLE [dbo].[A_WorkcenterTeam] ADD CONSTRAINT [PK_A_WorkcenterTeam] PRIMARY KEY ([WorkcenterID], [TeamID]) ON [PRIMARY]
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
ALTER TABLE [dbo].[A_WorkcenterTeam] ADD CONSTRAINT [FK_A_WorkcenterTeam_A_Workcenters] FOREIGN KEY ([WorkcenterID]) REFERENCES [dbo].[A_Workcenters] ([WorkcenterID])
GO
-- TRANSACTION HANDLING
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- =======================================================
-- SCRIPT FOOTER
-- =======================================================
-- COMMITTING TRANSACTION a
PRINT 'Script successfully completed'
COMMIT TRANSACTION _a_
GO
SET NOEXEC OFF
GO
GO
INSERT INTO "dbo"."A_Squadrons"("SquadronID", "Squadron", "Longname", "SEQ", "Active")
VALUES (1, 'HSS', 'HSS', 1, 1)
GO
INSERT INTO "dbo"."A_Squadrons"("SquadronID", "Squadron", "Longname", "SEQ", "Active")
VALUES (2, '1ST JCS', '1ST JCS', 2, 1)
GO
INSERT INTO "dbo"."A_Squadrons"("SquadronID", "Squadron", "Longname", "SEQ", "Active")
VALUES (5, '2ND JCS', '2ND JCS', 3, 1)
GO
INSERT INTO "dbo"."A_Squadrons"("SquadronID", "Squadron", "Longname", "SEQ", "Active")
VALUES (6, '3RD JCS', '3RD JCS', 4, 1)
GO
SET IDENTITY_INSERT A_Squadrons OFF
SET IDENTITY_INSERT A_Divisions ON
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (1, 'Command', 'Command', 1, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (2, 'HQ', 'HQ', 5, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (4, 'J1', 'J1', 10, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (5, 'J2', 'J2', 15, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (6, 'J3', 'J3', 20, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (7, 'J4', 'J4', 25, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (8, '1st Pltn', '1st Pltn', 30, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (9, '2nd Pltn', '2nd Pltn', 35, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (10, '3rd Pltn', '3rd Pltn', 40, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (11, 'MAINT', 'MAINT', 45, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (12, 'Transport', 'Transport', 50, 1)
GO
INSERT INTO "dbo"."A_Divisions"("DivisionID", "Division", "Longname", "SEQ", "Active")
VALUES (14, 'Engineers', 'Engineers', 55, 1)
GO
SET IDENTITY_INSERT A_Divisions OFF
GO
SET IDENTITY_INSERT A_SquadronDivision ON
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (1, 1, 1)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (2, 1, 2)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (3, 1, 4)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (4, 1, 5)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (5, 2, 2)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (6, 2, 8)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (7, 2, 9)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (8, 2, 10)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (9, 5, 2)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (10, 5, 8)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (11, 5, 9)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (12, 5, 10)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (13, 6, 2)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (14, 6, 8)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (15, 6, 9)
GO
INSERT INTO "dbo"."A_SquadronDivision"("SquadronDivisionID", "SquadronID", "DivisionID")
VALUES (16, 6, 10)
GO
SET IDENTITY_INSERT A_SquadronDivision OFF
GO
SET IDENTITY_INSERT A_Workcenters ON
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (1, 'CC', 'CC', 1, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (2, 'DC', 'DC', 5, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (3, 'CSM', 'CSM', 10, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (4, 'J1P', 'J1P', 20, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (5, 'J1M', 'J1M', 25, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (6, 'J1G', 'J1G', 30, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (7, 'CCS', 'CCS', 15, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (8, 'Admin', 'Admin', 17, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (9, 'Electrical', 'Electrical', 40, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (10, 'Machine', 'Machine', 45, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (11, 'Vehicle', 'Vehicle', 50, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (12, 'Data', 'Data', 55, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (13, 'Tech', 'Technical Control', 60, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (14, 'SCAMPI', 'SCAMPI', 65, 1)
GO
INSERT INTO "dbo"."A_Workcenters"("WorkcenterID", "Workcenter", "Longname", "SEQ", "Active")
VALUES (15, 'SSupT', 'Special Support', 70, 1)
GO
SET IDENTITY_INSERT A_Workcenters OFF
GO
SET IDENTITY_INSERT A_DivisionWorkcenter ON
GO
INSERT INTO "dbo"."A_DivisionWorkcenter"("DivisionWorkcenterID", "DivisionID", "WorkcenterID")
VALUES (1, 1, 1)
GO
INSERT INTO "dbo"."A_DivisionWorkcenter"("DivisionWorkcenterID", "DivisionID", "WorkcenterID")
VALUES (2, 1, 2)
GO
INSERT INTO "dbo"."A_DivisionWorkcenter"("DivisionWorkcenterID", "DivisionID", "WorkcenterID")
VALUES (3, 1, 3)
GO
INSERT INTO "dbo"."A_DivisionWorkcenter"("DivisionWorkcenterID", "DivisionID", "WorkcenterID")
VALUES (4, 1, 7)
GO
INSERT INTO "dbo"."A_DivisionWorkcenter"("DivisionWorkcenterID", "DivisionID", "WorkcenterID")
VALUES (5, 2, 8)
GO
INSERT INTO "dbo"."A_DivisionWorkcenter"("DivisionWorkcenterID", "DivisionID", "WorkcenterID")
VALUES (6, 2, 15)
GO
SET IDENTITY_INSERT A_DivisionWorkcenter OFF
GO
From dbo.A_Squadrons S
Left Join dbo.A_SquadronDivision SD ON S.SquadronID = SD.SquadronID
Left Join dbo.A_Divisions D ON SD.DivisionID = D.DivisionID
From dbo.A_Squadrons S
Left Join dbo.A_SquadronDivision SD ON S.SquadronID = SD.SquadronID
Left Join dbo.A_Divisions D ON SD.DivisionID = D.DivisionID
Left Join dbo.A_DivisionWorkcenter DW ON D.DivisionID = DW.DivisionID
Left Join dbo.A_Workcenters W ON DW.WorkcenterID = W.WorkcenterID
November 16, 2005 at 12:50 pm
Excuse me, my mind is a bit troubled.
The scripts was well done
Can you post an example of wanted output? Do you mean like a roster?
Squadrons
list of divisions
list of all workcenters (for that division?)
November 16, 2005 at 4:42 pm
Exactly! Something like this:
| Org | Squadron | Division | Workcenter | Team |
| Org1 | Sq1 | Div1 | HQ | |
| Org1 | Sq1 | Div1 | Admin | |
| Org1 | Sq1 | Div1 | Admin | |
| Org1 | Sq1 | Div2 | ||
| Org1 | Sq1 | Div2 | Maint | |
| Org1 | Sq2 | Div5 | HQ | |
| Org1 | Sq2 | Div5 | Admin | Team 1 |
| Org1 | Sq2 | Div5 | Admin | Team 2 |
| Org1 | Sq3 | Div5 | HQ | |
| Org1 | Sq3 | Div5 | Admin |
As you see Sq2 and Sq3 are duplicates groups, but Sq2 decided to devide the Admin workcenter into 2 teams... ETC...
Thanks - Marc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply