OpenAtlas

Query for actors and birth or first appearance (begin of the timespan):

SELECT DISTINCT ON (name)
  actor.id,
  actor.name, 
  actor.description, 
  timeprimitive.value_timestamp as firstdate
FROM 
  crm.entity actor, 
  crm.link, 
  crm.entity timeprimitive
WHERE 
  actor.id = link.domain_id AND
  link.range_id = timeprimitive.id AND
  link.property_id IN (141,143) AND --birth or first appearance
  actor.class_ID IN (21, 39, 68) -- person, group or legal body
ORDER BY actor.name, timeprimitive.value_timestamp

Query for actors and death or last appearance (end of the timespan):

SELECT DISTINCT ON (name)
  actor.id,
  actor.name, 
  actor.description, 
  timeprimitive.value_timestamp as lastdate
FROM 
  crm.entity actor, 
  crm.link, 
  crm.entity timeprimitive
WHERE 
  actor.id = link.domain_id AND
  link.range_id = timeprimitive.id AND
  link.property_id IN (142,144) AND --birth or first appearance
actor.class_ID IN (21, 39, 68) -- person, group or legal body
ORDER BY actor.name, timeprimitive.value_timestamp desc

This query returns one list that contains one record for each actor with the following information:

id, name, firstdate and lastdate
it shows one record for each actor even if the chronological information is not recorded. If only one (eg. only begin or only end) is known, the other field is left empty. If neither begin nor end are known both are left empty.
For the begin date the first value is shown, for the end date the last value

WITH sortbyname AS(
WITH alldates AS( 
--alldates returns one or more record for each actors with or without begin/end dates with id, name, firstdate and lastdate.
--the number of rows per actor depends on the various possibilites (e.g. no dates at all, only one begin, from-to values for end ...)

WITH datetable AS(
WITH actortimelist AS ( 

SELECT DISTINCT ON (name)  -- this query returns a row for each actor that has a begin/birth date and shows the first value for begin/birth
  actor.id,
  actor.name, 
  actor.description, 
  timeprimitive.value_timestamp, 
  timeprimitive.class_id, 
  link.property_id
FROM 
  crm.entity actor, 
  crm.link, 
  crm.entity timeprimitive
WHERE 
  actor.id = link.domain_id AND
  link.range_id = timeprimitive.id AND
  link.property_id IN (141,143) --birth or first appearance
ORDER BY actor.name, timeprimitive.value_timestamp)

SELECT Distinct on (name) -- this select statement uses the previous query and adds the last value from the end/death date as an additional row
  crm.entity.id,
  crm.entity.name,
  actortimelist.value_timestamp AS firstdate,
  timeprimitive.value_timestamp AS lastdate
FROM 
  crm.entity actor, 
  crm.link, 
  crm.entity timeprimitive,
  actortimelist RIGHT JOIN crm.entity  ON crm.entity.id = actortimelist.id
WHERE 
  actor.id = link.domain_id AND
  link.range_id = timeprimitive.id AND
  link.property_id IN (142,144) AND --death or last appearance
  actortimelist.id = actor.id
  ORDER BY name, lastdate desc)

  select * from datetable  -- datetable now shows all actors that have a begin/birth AND end/death date (first value of begin and last value of end timespan)

UNION ALL

SELECT  -- this query returns the first values for begin/birth. Necessary if for example only begin and no end dates are recorded, such cases would not show up in datetable (see above)
  actor.id,
  actor.name, 
  timeprimitive.value_timestamp as firstdate, 
  NULL AS lastdate
FROM 
  crm.entity actor, 
  crm.link, 
  crm.entity timeprimitive
WHERE 
  actor.id = link.domain_id AND
  link.range_id = timeprimitive.id AND
  link.property_id IN (141,143) --birth or first appearance

UNION ALL

SELECT -- this query returns the last values for end/death. Necessary if for example only end and no begin dates are recorded, such cases would not show up in datetable (see above)
  actor.id,
  actor.name, 
  NULL AS firstdate,
  timeprimitive.value_timestamp as lastdate 
FROM 
  crm.entity actor, 
  crm.link, 
  crm.entity timeprimitive
WHERE 
  actor.id = link.domain_id AND
  link.range_id = timeprimitive.id AND
  link.property_id IN (142,144) --death or last appearance

UNION ALL

SELECT -- this query returns the NULL values for begin/birth and death/end. Necessary if for example neither begin nor end dates are recorded, such cases would not show up in datetable (see above)
  id,
  name,
  NULL AS firstdate,
  NULL AS lastdate
FROM 
  crm.entity
WHERE
 class_id IN (21, 39, 68))

 SELECT DISTINCT ON (name, id) -- select only the record with the most complete information on dates and leave other possibilites aside
  id,
  name,
  firstdate,
  lastdate
FROM 
  alldates
  ORDER BY id, name, firstdate NULLS LAST, lastdate desc NULLS LAST) -- sort the result so the most complete information is in the first row

SELECT * FROM sortbyname
ORDER BY name -- sort results by name or whatever sorting you want (exchange sort expression if desired)

fixed above sql to get different values for first and last, otherwise the result could be misleading