Fra Harald Grovens wiki

Notes from George Alters lectures at ICPSR summer course in historical demography

[rediger] 01 Make table with births

This query creates a table events1 to store all birth events from the children table. Alongside the birth date and family ID number, three constants are also created:

ced children ever born, is set to 1 in order to count number of children in other queries.
ceb children ever died is set to zero, since this is a birth event.
event, the text "Birth" is added to all records

lb_birth alias for birth date. (What purpose???)

lb_death column with alias for death date of the child (purpose??)

Only single children (multbo is NULL) or one child from a twin birth (multbo is 1) is SELECT ed, since birth of twins should count as one event in the event1 table but are represented as 2 records in the children table.

SELECT  children.famid,
	children.bdate AS evdate,
	"Birth" AS event,
	children.bdate AS lb_birth,
	children.ddate AS lb_death,
	1 AS ceb,
	0 AS ced INTO events1
FROM children
WHERE (((children.multbo) IS NULL OR (children.multbo)=1))
;

[rediger] Bint 02 set ceb=2 for twins

For those events which both have same familyID and birthday, i.e. are twins, should have its children-ever-born (ceb) count set to 2 instead of 1.

UPDATE children 
INNER JOIN events1 ON (children.famid = events1.famid) 
AND (children.bdate = events1.evdate) SET events1.ceb = 2
WHERE (((children.multbo)=2))
;

[rediger] Bint 03 update eventsWHERE the second twin dies later

The eventsWHERE we know (ddate is not null) that the second twin (multbo = 2) dies before the first one, the deathdate in the event record is updated to the death day of the second.

Why? Becuase of breast feeding?

UPDATE events1 
INNER JOIN children ON (events1.famid = children.famid) 
AND (events1.evdate = children.bdate) 
SET events1.lb_death = children.ddate
WHERE (((events1.lb_death)<[children].[ddate]) 
AND ((children.multbo)= 2))   
AND ((children.ddate) IS NULL)
;

[rediger] Bint04 child deaths

For those children records we know the death date (ddate is not NULL) we insert the data from the list of Children table into the events1 table. The Children ever died (ced) to 1 and Children ever born (ceb) to zero.

INSERT INTO events1 
(famid,
	event,
	evdate,
	ced,
	ceb )
<source lang="sql">
SELECT  children.famid,
	"Child_death" AS event,
	children.ddate AS evdate,
	1 AS ced,
	0 AS ceb
FROM children
WHERE (((children.ddate) IS NOT NULL))
;

[rediger] Bint05 infant deaths +9

If there is less than one year between birthdate bdate and death date ddate in the childrens table, an "infant death" event is created. It is not stored as a Child Ever Died ced since this an event is already created for the death.

INSERT INTO events1 
(famid,
	event,
	evdate,
	ced,
	ceb)
<source lang="sql">
SELECT  children.famid,
	"Infant_death" AS event,
	DateAdd("d",274,[children].[ddate]) AS evdate,
	0 AS ced,
	0 AS ceb
FROM children
WHERE (((DateAdd("d",274,[children].[ddate])) IS NOT NULL) 
AND ((DateDiff("y",[children].[bdate],[children].[ddate])/365.25)<1))
;

[rediger] Bint06 marriages

SELECT  ALL marriages WITH marriage date TO the ''event1'' TABLE. 
 
INSERT INTO events1 ( famid,
	evdate,
	event,
	ceb,
	ced )
;
 
SELECT  marriages.famid,
	marriages.mdate AS evdate,
	"Marriage" AS event,
	0 AS ceb,
	0 AS ced
FROM marriages
;

[rediger] Bint07 end of union mother 50

Find the wife's 50th birthday and add it to the event1 table. The DateAdd function with years (yyyy) as time denomination will do this. Since 50 is the definitive end of her reproducive life, this will demographically be equivalent to a divorce... don't tell the pope:-)

INSERT INTO events1 ( famid,
	event,
	evdate,
	ceb,
	ced )
;	
 
SELECT  marriages.famid,
	"End_union" AS event,
	DateAdd("yyyy",50,marriages.wbdate) AS evdate,
	0 AS ceb,
	0 AS ced
FROM marriages
;

[rediger] Bint08 husband dies first

Find "End of union" eventsWHERE the husbands death date, hddate, is before the date of the end of union date. This will remove duplicate end of union events.

UPDATE marriages 
INNER JOIN events1 ON marriages.famid = events1.famid 
SET events1.evdate = marriages.hddate
WHERE (((events1.event)="End_union") 
AND ((marriages.hddate)<[events1].[evdate]))
;

[rediger] Bint09 wife dies first

For those marriage recordsWHERE the End of union date in the corresponding events1 table is before the wife's death date (wddate), the date should be updated to the the wife's death date.

UPDATE marriages 
INNER JOIN events1 ON marriages.famid = events1.famid 
SET events1.evdate = marriages.wddate
WHERE (((events1.event)="End_union") 
AND ((marriages.wddate) < [events1].[evdate]))
;

Create an empty table named events2 by copying the events1 table.

[rediger] Bint10 empty events2 table

Add the columns offset and priority

Why do this step??

DELETE events2.*
FROM events2
;

[rediger] Bint11 append to event2

The table event_types must be created manually with 4 fields

event text
priority number
offset number
seq autonumber. This will get a value assigned automatically when data is enterd. Since the data in the following query is sorted first by familyID and then by date, the seq will be sequental and ordinal that may be used for recursive self-joins.

This query is large as a nightmare!

It pulls most of the data from events1, sorts it according to priority and then pushes it into events2. This is necessary because the event records created in the 10 previous queries are not in the right sort order.

Why does it do a self join two times (events1_1, events1_2) ? Since there are several event records for each family, one alias is created in order to filter. Because of the self-join, several duplicates are created, but ...

INSERT INTO events2 ( famid,
	evdate,
	priority,
	offset,
	event,
	lb_birth,
	lb_death,
	ceb,
	ced )
;
 
SELECT  events1.famid,
	events1.evdate,
	event_types.priority,
	event_types.offset,
	events1.event,
	events1.lb_birth,
	events1.lb_death,
	events1.ceb,
	events1.ced
FROM ((events1 
INNER JOIN events1 AS events1_1 ON events1.famid = events1_1.famid) 
INNER JOIN events1 AS events1_2 ON events1.famid = events1_2.famid) 
INNER JOIN event_types ON events1.event = event_types.event
WHERE (((events1_1.event)="Marriage") 
AND ((events1_1.evdate)<=[events1].[evdate]) 
AND ((events1_2.event)="End_union") 
AND ((events1_2.evdate)>=[events1].[evdate]))
ORDER BY events1.famid,
	events1.evdate,
	event_types.priority
;

[rediger] Bint12 fix offset for twins dying on same day

Cox regressions don't work when there are "ties", that means multiple events on the exately same time. In order to fix this, an artificial small amont of time are added to one of two concurrent events. An example of a such event is the rare caseWHERE two twins die the very same day. Here 0.05 days is added to each such event.

UPDATE events2 
INNER JOIN events2 AS events2_1 ON (events2.evdate = events2_1.evdate) 
AND (events2.event = events2_1.event) 
AND (events2.famid = events2_1.famid) 
SET events2_1.offset = events2_1.offset+0.05
WHERE (((events2.event)="Child_death") 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] Bint13 accumulate ceb

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.famid = events2_1.famid SET events2_1.ceb = events2_1.ceb+events2.ceb
WHERE (((events2_1.seq)=[events2].[seq]+1))
;

[rediger] Bint14 accumulate ced

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.famid = events2_1.famid SET events2_1.ced = [events2_1].[ced]+[events2].[ced]
WHERE (((events2_1.seq)=[events2].[seq]+1))
;

[rediger] Bint15 copy forward lb birth

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.famid = events2_1.famid SET events2_1.lb_birth = [events2].[lb_birth]
WHERE (((events2_1.lb_birth) IS NULL) 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] Bint16 copy forward lb death

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.famid = events2_1.famid SET events2_1.lb_death = [events2].[lb_death]
WHERE (((events2_1.lb_death) IS NULL) 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] Bint17 age first birth

SELECT  events2.famid,
	events2.evdate,
	events2.ceb,
	marriages.wbdate,
	DateDiff("y",marriages.wbdate,events2.evdate)/365.25 AS agefb INTO agefirstbirth
FROM events2 
INNER JOIN marriages ON events2.famid = marriages.famid
WHERE (((events2.ceb)=1) 
AND ((events2.event)="Birth"))
;

[rediger] Bint18 episodes

SELECT  events2.famid,
	events2.evdate AS evdate1,
	events2.event AS event1,
	events2.lb_birth,
	events2.lb_death,
	events2.ceb,
	events2.ced,
	events2.offset AS offset1,
	events2_1.evdate AS evdate2,
	events2_1.event AS event2,
	events2_1.offset AS offset2,
	agefirstbirth.wbdate,
	agefirstbirth.agefb
FROM (events2 
INNER JOIN events2 AS events2_1 ON events2.famid = events2_1.famid) 
LEFT JOIN agefirstbirth ON events2.famid = agefirstbirth.famid
WHERE (((events2_1.seq)=[events2].[seq]+1))
;

[rediger] Same queries for Henry data

[rediger] 1

SELECT  [children].PARID,
	[children].KIDID,
	[children].VILLAGE,
	[children].N0_FICH,
	[children].TYP_FICH,
	[children].kid_DOB AS evdate,
	"birth" AS event,
	[children].kid_DOB AS lb_birth,
	[children].kid_DDie AS lb_death,
	1 AS ceb,
	0 AS ced,
	[children].TWIN,
	[children].TRIPLET INTO events
FROM [children]
WHERE ((([children].TWIN)>=0))
;

[rediger] 2 Twins

 
UPDATE events 
INNER JOIN [children] ON (events.PARID = [children].PARID) 
AND (events.evdate = [children].kid_DOB) 
SET events.ceb = 2
WHERE ((([children].TWIN)=1) 
AND (([children].TRIPLET) IS NULL))
;

[rediger] 3 Triplets

 
UPDATE events 
INNER JOIN [children] ON (events.evdate = [children].kid_DOB) 
AND (events.PARID = [children].PARID) 
SET events.ceb = 3
WHERE ((([children].TWIN)=1) 
AND (([children].TRIPLET)=1))
;

[rediger] 4 Death of twins

 
UPDATE events 
INNER JOIN [children] ON (events.PARID = [children].PARID) 
AND (events.evdate = [children].kid_DOB) 
SET events.lb_death = [children].kid_DDie
WHERE (((events.lb_death)<[children].[kid_DDie]))
;

[rediger] 5 death of triplets

 
UPDATE events 
INNER JOIN [children] ON (events.PARID = [children].PARID) 
AND (events.evdate = [children].kid_DOB) 
SET events.lb_death = [children].kid_DDie
WHERE (((events.lb_death)<[children].[kid_DDie]))
;

[rediger] 6 death

 
INSERT INTO events 
(PARID,
	KIDID,
	VILLAGE,
	event,
	evdate,
	ced,
	ceb )
<source lang="sql">
SELECT  [children].PARID,
	[children].KIDID,
	[children].VILLAGE,
	"child_death" AS event,
	[children].kid_DDie AS evdate,
	1 AS ced,
	0 AS ceb
FROM [children]
WHERE ((([children].kid_DDie) IS NOT NULL))
;

[rediger] 7 infant deaths

 
INSERT INTO events 
(PARID,
	KIDID,
	VILLAGE,
	event,
	evdate,
	ced,
	ceb )
 
 
;
 
 
SELECT  [children].PARID,
	[children].KIDID,
	[children].VILLAGE,
	"infant_death" AS event,
	DateAdd("d", 274, [children].kid_DDie) AS evdate,
	0 AS ced,
	0 AS ceb
FROM [children]
WHERE (((DateAdd("d", 274, [children].[kid_DDie])) IS NOT NULL) 
AND ((DateDiff("y", [children].[kid_DOB],
	[children].[kid_DDie])/365.25)<1))
;

[rediger] 8 marriages

 
INSERT INTO events 
(PARID,
	event,
	ceb,
	ced,
	evdate,
	VILLAGE )
<source lang="sql">
SELECT  parent_newdates.PARID,
	"marriage" AS event,
	0 AS ceb,
	0 AS ced,
	parent_newdates.marriage AS evdate,
	parent_newdates.VillageID AS VILLAGE
FROM parent_newdates
;

[rediger] 9 End of union when woman is 50

INSERT INTO events 
(PARID,
	event,
	evdate,
	ceb,
	ced,
	VILLAGE )
<source lang="sql">
SELECT  parent_newdates.PARID,
	"end_union" AS event,
	DateAdd("yyyy", 50, parent_newdates.DOB_W) AS evdate,
	0 AS ceb,
	0 AS ced,
	parent_newdates.VillageID AS VILLAGE
FROM parent_newdates
;

[rediger] 10 Death of husband

 
UPDATE events 
INNER JOIN parent_newdates ON events.PARID = parent_newdates.PARID 
SET events.evdate = parent_newdates.dead_H
WHERE (((events.event)="end_union") 
AND ((parent_newdates.dead_H)<[events].[evdate])) 
OR (((events.evdate) IS NULL) 
AND ((events.event)="end_union"))
;

[rediger] 11 death of wife

UPDATE events 
INNER JOIN parent_newdates ON events.PARID = parent_newdates.PARID 
SET events.evdate = parent_newdates.dead_W
WHERE (((events.event)="end_union") 
AND ((parent_newdates.dead_W)<[events].[evdate])) 
OR (((events.evdate) IS NULL) 
AND ((events.event)="end_union"))
;

[rediger] 12

INSERT INTO events2 
(PARID,
	VILLAGE,
	evdate,
	event,
	lb_birth,
	lb_death,
	ceb,
	ced,
	priority,
	offset )
;

SELECT  events.PARID,
	events.VILLAGE,
	events.evdate,
	events.event,
	events.lb_birth,
	events.lb_death,
	events.ceb,
	events.ced,
	event_types.priority,
	event_types.offset
FROM ((events 
INNER JOIN events AS events_1 ON events.PARID = events_1.PARID) 
INNER JOIN events AS events_2 ON events_1.PARID = events_2.PARID) 
INNER JOIN event_types ON events.event = event_types.event
WHERE (((events_1.event)="marriage") 
AND ((events_1.evdate)<=[events].[evdate]) 
AND ((events_2.event)="end_union") 
AND ((events_2.evdate)>=[events].[evdate]))
ORDER BY events.PARID,
	events.VILLAGE,
	events.evdate,
	event_types.priority
;

[rediger] 13

UPDATE events2 
INNER JOIN events2 AS events2_1 ON (events2.PARID = events2_1.PARID) 
AND (events2.evdate = events2_1.evdate) 
AND (events2.event = events2_1.event) 
AND (events2.offset = events2_1.offset) 
SET events2_1.offset = events2_1.offset+0.03
WHERE (((events2.event)="child_death") 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 14

UPDATE events2 
INNER JOIN events2 AS events2_1 ON (events2.event = events2_1.event) 
AND (events2.evdate = events2_1.evdate) 
AND (events2.PARID = events2_1.PARID) 
AND (events2.offset = events2_1.offset) 
SET events2_1.offset = events2_1.offset+0.03
WHERE (((events2.event)="child_death") 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 15

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.PARID = events2_1.PARID 
SET events2_1.ceb = events2_1.ceb+events2.ceb
WHERE (((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 16

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.PARID = events2_1.PARID 
SET events2_1.ced = events2_1.ced+events2.ced
WHERE (((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 17

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.PARID = events2_1.PARID 
SET events2_1.lb_birth = events2.lb_birth
WHERE (((events2_1.lb_birth) IS NULL) 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 18

UPDATE events2 
INNER JOIN events2 AS events2_1 ON events2.PARID = events2_1.PARID 
SET events2_1.lb_death = events2.lb_death
WHERE (((events2_1.lb_death) IS NULL) 
AND ((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 19

SELECT  
	events2.PARID,
	events2.evdate AS evdate1,
	events2.event AS event1,
	events2.lb_birth,
	events2.lb_death,
	events2.ceb,
	events2.ced,
	events2.offset AS offset1,
	events2_1.evdate AS evdate2,
	events2_1.event AS event2,
	events2_1.offset AS offset2,
	villages.Region,
	villages.DistanceParis,
	events2_1.seq,
	events2.VILLAGE INTO [events2distance]
FROM (events2 
INNER JOIN events2 AS events2_1 ON (events2.PARID = events2_1.PARID) 
AND (events2.VILLAGE = events2_1.VILLAGE)) 
INNER JOIN villages ON events2_1.VILLAGE = villages.Village_ID
WHERE (((events2_1.seq)=[events2].[seq]+1))
;

[rediger] 20

SELECT  
	[events2distance].PARID,
	[events2distance].evdate1,
	[events2distance].event1,
	[events2distance].lb_birth,
	[events2distance].lb_death,
	[events2distance].ceb,
	[events2distance].ced,
	[events2distance].offset1,
	[events2distance].evdate2,
	[events2distance].event2,
	[events2distance].offset2,
	[events2distance].Region,
	[events2distance].DistanceParis,
	[events2distance].seq,
	[events2distance].VILLAGE,
	[occupation codes].Code INTO [events2u2HouseOccup]
FROM [events2distance] 
INNER JOIN [occupation codes] ON [events2distance].PARID = [occupation codes].PARID
;

[rediger] 21

SELECT  
	[events2u2HouseOccup].PARID,
	[events2u2HouseOccup].evdate1,
	[events2u2HouseOccup].event1,
	[events2u2HouseOccup].lb_birth,
	[events2u2HouseOccup].lb_death,
	[events2u2HouseOccup].ceb,
	[events2u2HouseOccup].ced,
	[events2u2HouseOccup].offset1,
	[events2u2HouseOccup].evdate2,
	[events2u2HouseOccup].event2,
	[events2u2HouseOccup].offset2,
	[events2u2HouseOccup].Region,
	[events2u2HouseOccup].DistanceParis,
	[events2u2HouseOccup].seq,
	[events2u2HouseOccup].VILLAGE,
	[events2u2HouseOccup].Code,
	parents.AgeMarriageWoman INTO [events2u3wifeAgeAtMarr]
FROM [events2u2HouseOccup] 
INNER JOIN parents ON [events2u2HouseOccup].PARID = parents.PARID
;

[rediger] 22

SELECT  
	[events2u3wifeAgeAtMarr].PARID,
	[events2u3wifeAgeAtMarr].evdate1,
	[events2u3wifeAgeAtMarr].event1,
	[events2u3wifeAgeAtMarr].lb_birth,
	[events2u3wifeAgeAtMarr].lb_death,
	[events2u3wifeAgeAtMarr].ceb,
	[events2u3wifeAgeAtMarr].ced,
	[events2u3wifeAgeAtMarr].offset1,
	[events2u3wifeAgeAtMarr].evdate2,
	[events2u3wifeAgeAtMarr].event2,
	[events2u3wifeAgeAtMarr].offset2,
	[events2u3wifeAgeAtMarr].Region,
	[events2u3wifeAgeAtMarr].DistanceParis,
	[events2u3wifeAgeAtMarr].seq,
	[events2u3wifeAgeAtMarr].VILLAGE,
	[events2u3wifeAgeAtMarr].OccupCode,
	[events2u3wifeAgeAtMarr].AgeMarriageWoman,
	parents.TYP_FICH INTO [events2 UPDATE 3 (TYP_FICH)]
FROM [events2u3wifeAgeAtMarr] 
INNER JOIN parents ON [events2u3wifeAgeAtMarr].PARID = parents.PARID
WHERE (((parents.TYP_FICH)<15))
;

Create event table from family reconstitution db