April 15, 2010 at 2:34 pm
I have 3 tables
Table 1 (SCOUTS)
This table has cub scout names and other information
Table 2 (PARENT_GUARDIAN)
This table has parent information.
Table 3 (xref_PG_SCT)
This Table Joins Parents with scouts using the unique id from both SCOUTS and PARENT_GUARDIAN, more of a cross refernce table. There could ultimately be a many to many relationship
Here are the create table scripts:
CREATE TABLE [dbo].[SCOUTS] (
[SCT_ID] int IDENTITY(1,1) NOT NULL,
[SCT_FNAME] nvarchar(25) NOT NULL,
[SCT_LNAME] nvarchar(25) NOT NULL,
[SCT_DOB] datetime NULL,
[REF_SCT_RANK_ID]int NOT NULL,
[REF_DEN_ID] int NULL,
[SCT_STATUS] varchar(8) NULL CONSTRAINT [DF_SCOUTS_SCT_STATUS] DEFAULT ('ACTIVE'),
CONSTRAINT [PK_SCOUTS] PRIMARY KEY([SCT_ID])
)
GO
CREATE TABLE [dbo].[xref_PG_SCT] (
[PG_ID] int NOT NULL,
[SCT_ID]int NOT NULL
)
GO
ALTER TABLE [dbo].[xref_PG_SCT]
ADD CONSTRAINT [FK_xref_PG_SCT_SCOUTS]
FOREIGN KEY([SCT_ID])
REFERENCES [dbo].[SCOUTS]([SCT_ID])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[xref_PG_SCT]
ADD CONSTRAINT [FK_xref_PG_SCT_PARENT_GUARDIAN]
FOREIGN KEY([PG_ID])
REFERENCES [dbo].[PARENT_GUARDIAN]([PG_ID])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
GO
CREATE TABLE [dbo].[PARENT_GUARDIAN] (
[PG_ID] int IDENTITY(150,1) NOT NULL,
[REF_PG_RELAT_ID]int NULL,
[PG_FNAME] nvarchar(25) NOT NULL,
[PG_LNAME] nvarchar(25) NOT NULL,
[PG_ADDRESS] nvarchar(50) NULL,
[PG_CITY] nvarchar(25) NULL,
[PG_STATE] varchar(2) NULL,
[PG_ZIP] nvarchar(11) NULL,
[PG_PPHONE] nvarchar(12) NULL,
[PG_WPHONE] nvarchar(12) NULL,
[PG_EPHONE] nvarchar(12) NULL,
[PG_EMAILHOME] nvarchar(75) NULL,
[PG_EMAILWORK] nvarchar(75) NULL,
[PG_USERNAME] nvarchar(25) NULL,
[PG_PASSWORD] nvarchar(25) NULL,
[PG_STATUS] varchar(8) NOT NULL CONSTRAINT [DF__PARENT_GU__PG_ST__76619304] DEFAULT ('ACTIVE'),
[PG_INSERTDATE] datetime NOT NULL CONSTRAINT [DF__PARENT_GU__PG_IN__7755B73D] DEFAULT (convert(varchar,getdate(),101)),
[PG_LASTVISIT] nvarchar(25) NULL,
[PG_PRIMARY] varchar(1) NULL,
CONSTRAINT [PK_PARENT_GUARDIAN] PRIMARY KEY([PG_ID])
)
GO
Here is some code for populating these tables:
-- FIRST LETS ADD SOME NEW SCOUNTS INTO THE TABLE
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(1, N'Joey', N'Smith', '', 5, 1, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(2, N'Billy', N'Corells', '', 5, 1, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(3, N'Dean', N'Corells', '', 1, 2, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(4, N'Frank', N'Galley', '', 5, 3, 'ACTIVE')
GO
-- NEXT LETS CREATE SOME PARENTS FOR EACH OF THE SCOUTS
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(1, 0, N'Julie', N'Smith', N'345 East Main Street', N'Boomfield', 'NY', N'13245','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(2, 0, N'Dan', N'Smith', N'345 East Main Street', N'Boomfield', 'NY', N'13245','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(3, 0, N'David', N'Corells', N'4612 Blueberry Drive', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(4, 0, N'Sandra', N'Corells', N'4612 Blueberry Drive', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(5, 0, N'Bob', N'Galley', N'89 South Church Street', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(6, 0, N'Barbara', N'Galley', N'89 South Church Street', N'Gallant', 'NY', N'23412','ACTIVE')
GO
-- NOW WE ARE GOOING TO JOIN EACH SCOUT TO EACH PARENT USING OUR CROSS REFERENCE TABLE
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(1, 1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(2, 1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(3, 2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(4, 2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(3, 3)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(4, 3)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(5, 4)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(6, 4)
GO
Now heres what I need to update.
1) Set all scouts SCT_STATUS to 'INACTIVE' WHERE REF_SCT_RANK_ID = 5
2) Now I need to update the [PG_STATUS] to 'INACTIVE' only if there are no other scouts associated to those Parents where the scouts status was just updated based on step one
So for example in our data
All Parents other than David and Sandra Corells will have a PG_STATUS of INACTIVE. This is because they still have a scout where REF_SCT_RANK_ID was not = to 5 so there PG_STATUS must still remain active
April 15, 2010 at 3:12 pm
Can you take the PG_STATUS out of the database and just calculate it at runtime?
Join the three tables together, where the scout status is not equal to 5, and select the parent/guardian data from that. Don't store the value, just calculate it when you need it. Will that work?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2010 at 12:19 pm
This really not an option since I am doing a bulk update. And because the status column has been used in so many procedures and other runtime components the rework would take way to much time. I have created a simple update query however it does not take into account the most critical aspect of the update which is to NOT change the status to INACTIVE for those parents where they have another child still in scouts. I only want to change the status if they have one scout associated to them.
April 16, 2010 at 3:43 pm
This is, I am sure NOT the best way, but since CTEs
can not be used in SQL 2000 the best I could come up
with is a 3 step approach.
/*This sets your parents status with
multiple children one of which is still ACTIVE and
one of which is INACTIVE , to INACTIVE not what
is wanted but */
UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'INACTIVE'
WHERE EXISTS(SELECT p.PG_ID,s.SCT_STATUS,s.SCT_ID
FROM [PARENT_GUARDIAN] p
JOIN [dbo].[xref_PG_SCT] x
ON p.PG_ID = x.PG_ID
JOIN SCOUTS s ON
s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'INACTIVE')
/* We then find and place into a temporarty table
those scouts that are still ACTIVE */
SELECT p.PG_ID
INTO #T
FROM [PARENT_GUARDIAN] p
JOIN [dbo].[xref_PG_SCT] x
ON p.PG_ID = x.PG_ID
JOIN SCOUTS s ON
s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'ACTIVE'
/* Next we go back and set those parents to ACTIVE */
UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'ACTIVE'
FROM #T WHERE #T.PG_ID = PARENT_GUARDIAN.PG_ID
/* Check did we obtain the desired results
PG_LNamePG_STATUS
Smith INACTIVE
Smith INACTIVE
Corells ACTIVE
Corells ACTIVE
Galley INACTIVE
Galley INACTIVE */
DROP TABLE #T
A brute force approach - maybe some one can refine it ..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply