Výsledný report vypadá takto:
SQL kód:
select
CONCAT('<a href = \"familygroup.php?familyID=', F1.familyID, '&tree=', F1.gedcom, '\" target=\"_blank\" class=\"fam\">', F1.familyID, '</a>') as FamilyID
,F1.living
,CONCAT(P1.lastname, ', ', P1.firstname) as HusbandName
,CONCAT(P2.lastname, ', ', P2.firstname) as WifeName
,case when YEAR(F1.divdatetr) != 0
then YEAR(F1.divdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) <= YEAR(P2.deathdatetr)
then YEAR(P1.deathdatetr)-YEAR(F1.marrdatetr)
when YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0 and YEAR(P1.deathdatetr) > YEAR(P2.deathdatetr)
then YEAR(P2.deathdatetr)-YEAR(F1.marrdatetr)
else 0
end as YearsMarried
,F1.marrdate
,F1.divdate
,P1.deathdate as HusbandDeathDate
,P2.deathdate as WifeDeathDate
,case when YEAR(F1.divdatetr) != 0 or (YEAR(P1.deathdatetr) != 0 and YEAR(P2.deathdatetr) != 0) then ' '
when YEAR(P1.deathdatetr) = 0 then 'Husband death date not known'
when YEAR(P2.deathdatetr) = 0 then 'Wife death date not known'
end as Comment
from
tng_families F1
inner join
tng_people P1
on F1.gedcom = P1.gedcom
and F1.husband = P1.personID
inner join
tng_people P2
on F1.gedcom = P2.gedcom
and F1.wife = P2.personID
where F1.marrdate != ''
and UPPER(P1.deathdate) not like 'ABT%'
and UPPER(P1.deathdate) not like 'AFT%'
and UPPER(P1.deathdate) not like 'BEF%'
and UPPER(P1.deathdate) not like 'BET%'
and UPPER(P1.deathdate) not like 'CAL%'
and UPPER(P1.deathdate) not like 'EST%'
and UPPER(P2.deathdate) not like 'ABT%'
and UPPER(P2.deathdate) not like 'AFT%'
and UPPER(P2.deathdate) not like 'BEF%'
and UPPER(P2.deathdate) not like 'BET%'
and UPPER(P2.deathdate) not like 'CAL%'
and UPPER(P2.deathdate) not like 'EST%'
and UPPER(F1.marrdate) not like 'ABT%'
and UPPER(F1.marrdate) not like 'AFT%'
and UPPER(F1.marrdate) not like 'BEF%'
and UPPER(F1.marrdate) not like 'BET%'
and UPPER(F1.marrdate) not like 'CAL%'
and UPPER(F1.marrdate) not like 'EST%'
and UPPER(F1.marrdate) != 'Y'
and UPPER(F1.divdate) not like 'ABT%'
and UPPER(F1.divdate) not like 'AFT%'
and UPPER(F1.divdate) not like 'BEF%'
and UPPER(F1.divdate) not like 'BET%'
and UPPER(F1.divdate) not like 'CAL%'
and UPPER(F1.divdate) not like 'EST%'
and UPPER(F1.divdate) != 'Y'
and (F1.divdate != '' or P1.deathdate != '' or P2.deathdate != '')
and not (YEAR(F1.divdatetr) = 0 and YEAR(P1.deathdatetr) = 0 and YEAR(P2.deathdatetr) = 0) having Comment = ''
order by YearsMarried desc, P1.lastname, F1.marrdatetr desc
;
Ať se daří!
![Úsměv :)](./images/smilies/icon_e_smile.gif)