complex sql query - merge or replace groups

Question

Im building a school website and got stuck on this problem.

The SQL database is structured as follows ( i do not have permission to modify their database)

**GroupRecords**
Id (int, primary key)
Name (nvarchar)
SchoolYear (datetime)
RecordDate (datetime)
IsUpdate (bit)

**People**
Id (int, primary key)
GroupRecordsId (int, foreign key to GroupRecords.Id)
Name (nvarchar)
Bio (nvarchar)
Location (nvarchar)

The actual paper form looks like this, it gets updated throughout the year Assume no duplicate Participant names (People.Name) in the same form (GroupRecords)

-------------------------
Name: District A
SchoolYear: 2000
RecordDate: 12/30/1999
IsUpdate: no

Participants
Name Location
AA   11
BB   22
CC   33
DD   44
-------------------------
-------------------------
Name: District A
SchoolYear: 2000
RecordDate: 1/2/2000
IsUpdate: no

Participants
Name Location
QQ   33
DD   22
EE   99
FF   66
-------------------------
-------------------------
Name: District A
SchoolYear: 2000
RecordDate: 2/1/2000
IsUpdate: yes

Participants
Name Location
XX   00
-------------------------
-------------------------
Name: District A
SchoolYear: 2000
RecordDate: 2/1/2000
IsUpdate: yes

Participants
Name Location
QQ   44
-------------------------

now here's the tricky part if IsUpdate is yes, then the list of people returned should merge with the previous record's list (so if IsUpdate is no: replace, if IsUpdate is yes: merge)

so if the query is GroupRecords.Name = 'District A' AND GroupRecords.SchoolYear = '1/1/2000', i should get

QQ   44
DD   22
EE   99
FF   66
XX   00

It would probably be better to write a stored procedure for this right? thank you so much

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupRecords](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [SchoolYear] [datetime] NOT NULL,
    [RecordDate] [datetime] NOT NULL,
    [IsUpdate] [bit] NOT NULL,
 CONSTRAINT [PK_GroupRecords] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[People](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GroupRecordsId] [int] NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [Bio] [nvarchar](4000) NOT NULL,
    [Location] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[People]  WITH CHECK ADD  CONSTRAINT [FK_People_GroupRecords] FOREIGN KEY([GroupRecordsId])
REFERENCES [dbo].[GroupRecords] ([Id])
GO
ALTER TABLE [dbo].[People] CHECK CONSTRAINT [FK_People_GroupRecords]

Update let me clearify how IsUpdate works

the forms in the example are already sorted by RecordDate in desending order say only the first form exists, the returned list would be

AA   11
BB   22
CC   33
DD   44

if only the first and form, the returned list would be

QQ   33
DD   22
EE   99
FF   66

since isUpdate in the latest form (the second form) is no, the returned list would just be the content in the 2nd form

first 3 forms

QQ   33
DD   22
EE   99
FF   66
XX   00

because isUpdate in the latest form (the 3rd form) is yes, its content would add/replace the last list.

say there's a fifth form like this

-------------------------
Name: District A
SchoolYear: 2000
RecordDate: 2/10/2000
IsUpdate: no

Participants
Name Location
TT   99
-------------------------

then the returned data would just be

TT   99

Answer 1

I see a trend here ... do you want to learn how to work with databases in the context of this app you're trying to work on, or do you want to post questions and have us tell you the answers? It can be handled either way, but you should post pretty clearly what you want.

This will probably get complex, so you may want to publish a pretty complete schema.

Answer 2

In my opinion, something seems wrong with the desing of the database.

It would seem a lot more logical if there were three tables.

One for the persons
This table should have the following fields

Id (int, primary key)
Name (nvarchar)
Bio (nvarchar)
Location (nvarchar)

One for the groups
this table should have the following fields

Id (int, primary key)
Name (nvarchar) SchoolYear (datetime)

One to associate Persons and groups

Id
GroupId
PersonId
RecordDate

This way, if you need to add a person to a group, you just insert it in the person-group table.

If you need historic, you could simply add a field to the person-group table indicating the status of the record (current, deleted, whatever)

The way the current database is designed, one person may not be in more than one group. Also, any change to a person's group is made in a destructive manner (unless you duplicate your persons, which is BAD).

If you need a paper-trail of the group in which persons were previously, you could need more tables as well to keep an historic.

Instead of using an IsUpdate Field in a database, the data should just be edited. MS Sql is a database server, and databases are meant to be updated.

Answer 3

Can you talk through it again with realistic data values? XX, AA etc. might be "Smith", "Jones". What might each of them have for existing values of Group, Location, etc.?

Then take one IsUpdate form example, and show what the result would be. Then for the same form switch to IsUpdate = false, and show the different result?