BULK UPDATING between dates

Transact-SQL
1

I am busy rewriting our school administration system.
when recording attendance in the OLD database, it did NOT record the classname (FormID) against the attendance record.
We need this information for reporting purposes.

One handy table is the STUDENTA (Student Audit) table which shows me the date/time that ANY student info was changed.
From this, I am able to extract where the FORMID changed as follows.

select cstudid, formid, max(chgdate) as Dt, 0 as ID_FormID, '01/01/1999' as ChgDt
into #A
from QUAD.DBO.EBIZDW11_STUDENTA 
group by Cstudid, formid
order by cstudid, Dt;

The following is the typical output of the AUDIT file.
NOTE: I have modified the data so that I don't have to upload hundreds of records.

cstudid..formid..Dt.........ID_FormID..ChgDt
564......8.4.....20130312...16.........2013-03-12
564......9.1.....20130315...2..........2013-03-15
564......GO......20130317...4..........2013-03-19
564......KINTO...20130322...3..........2013-03-28

The NEW attendance history table now contains a formid field (TYPE INT with a default value of 1) that I would like to update retrospectively.
The the relative fields look like this.

ID_Att_History...ID_Student...FormID...ATT_Date
1................564..........1........2013-03-11
2................564..........1........2013-03-12
3................564..........1........2013-03-13
4................564..........1........2013-03-14
5................564..........1........2013-03-19
6................564..........1........2013-03-20
7................564..........1........2013-03-21
8................564..........1........2013-03-22
9................564..........1........2013-03-23

Now I could update SOME of the records in the NEW table by matching on the ID_Student and CHGDAT using

Update #A
Set ID_Formid = F.ID_FORMID
from #A
Inner join SCH_FORMID F
on #A.formID = F.Fmname;

but this only changes records where the dates match so now the data looks like this.
ID_Att_History...ID_Student...FormID...ATT_Date
1................564..........1........2013-03-11
2................564..........16.......2013-03-12 FormID changes here
3................564..........1........2013-03-13
4................564..........1........2013-03-14
5................564..........1........2013-03-16
6................564..........4........2013-03-19 FormID changes here
7................564..........1........2013-03-21
8................564..........1........2013-03-22
9................564..........1........2013-03-23

So, bearing in mind that the AUDIT file reflects a change in the student record which may not correlate with a date in the ATTENDANCE record.
For example, there is no ATTENDANCE record for line 2 of the AUDIT file.
564......9.1.....20130315...2..........2013-03-15

So what I am actually trying to achieve is
Update all records where attendance exists BETWEEN the ChgDts (from the AUDIT table).

IE...the data should be changed as follows.
ID_Att_History...ID_Student...FormID...ATT_Date
1................564..........1........2013-03-11
2................564..........16.......2013-03-12 FormID changes here
3................564..........16.......2013-03-13
4................564..........16.......2013-03-14
5................564..........2........2013-03-16..FormID changes here
6................564..........3........2013-03-19 FormID changes here
7................564..........3........2013-03-21
8................564..........3........2013-03-22
9................564..........3........2013-03-23

I hope this makes sense?

Any assistance on this process would help

2

so why can't use BETWEEN date1 and date2 instead of = in your join?

3

Hi Gbritton
That's what i am struggling with. How do I determine the top and bottom limits of the BETWEEN statement?

This sample data (AUDIT table) all relates to the same student.
cstudid..formid..Dt.........ID_FormID..ChgDt
564......8.4........20130312....16.........2013-03-12
564......9.1........20130315.....2..........2013-03-15
564......GO........20130317....4..........2013-03-19
564......KINTO...20130322....3..........2013-03-28

So, to update ATT_History (my target table, which also relates to the same student)...
ID_Att_History...ID_Student...FormID...ATT_Date
1....................564...............1........2013-03-11
2....................564...............1........2013-03-12
3....................564...............1........2013-03-13
4....................564...............1........2013-03-14
5....................564...............1........2013-03-19
6....................564...............1........2013-03-20
7....................564...............1........2013-03-21
8....................564...............1........2013-03-22
9....................564...............1........2013-03-23

what would my BETWEEN query look like?

i realise that my query would need to say
UPDATE H
SET FormID = A.FormID
From Att_History H
INNER JOIN AUDIT A
ON H.ID_Student = A.CSTUDID
Where H.ATT_Date BETWEEN
( How do I select the start and end dates here?)

4

Ahhh....I have a solution as follows.

-- Create a temporary storage table.

Create table TMP_Audit(

[ID] [INT] IDENTITY (1,1) Primary Key,

[cstudid] [INT] NULL,

[formid] [varchar](10) NULL,

[QDate] [INT] NULL,

[SDt] [datetime] NULL,

[EDt] [datetime] NULL,

[ID_FormID] [INT] NULL DEFAULT 0);

-- Populate the TMP_AUDIT Table

INSERT into TMP_AUDIT(

CSTUDID,

FORMID,

QDate

)

select

cstudid,

formid,

max(chgdate)

from QUAD.DBO.EBIZDW11_STUDENTA

group by Cstudid, formid, CHGDATE

order by cstudid, CHGDATE;

-- convert the QDate to a date format as the START date

UPDATE TMP_Audit

Set SDt = convert(date,cast(Qdate as varchar(8)),112);

-- Assign an END date

UPDATE A

Set EDt = convert(date,cast(B.Qdate as varchar(8)),112)

from TMP_Audit A

	INNER JOIN TMP_AUDIT B

	ON A.cstudid=b.cstudid

	where a.ID = b.id-1;

-- Correct the FORMID in the TEMP table

Update A

Set ID_Formid = F.ID_FORMID

from TMP_Audit A

	Inner join SCH_FORMID F

	on A.formID = F.Fmname;

-- update the att_history table accordingly

UPDATE H

Set FormID = A.ID_FormID

from ATT_History H

	INNER JOIN TMP_Audit A

	on A.cstudid = H.ID_Student

	WHERE h.ATT_Date BETWEEN a.SDt AND a.EDt

--select * from TMP_AUDIT;

--select * from ATT_History;

drop table TMP_AUDIT;

Thanks for the tip