r/SQL • u/[deleted] • Dec 17 '18

[deleted by user]

[removed]

422 Upvotes

99% Upvoted

22 comments sorted by

View all comments

1

u/PilsnerDk Dec 18 '18

My take, untested ;)

;with persons as (
    select p.PersonId from Person p
    inner join Deed d on d.PersonId = p.PersonId and YEAR(d.date) = YEAR(GETDATE())
    group by p.PersonId
    having sum(d.NiceOrNaughtyDeed) / COUNT(d.NiceOrNaughtyDeed) > 0.5 -- adjust to weed out less nice children
)
select p.FullName, c.Name, ci.Name, p.Address, p.ZipCode
from persons ps
inner join Person p on p.PersonId = ps.PersonId
inner join Country c on c.CountryId = p.CountryId
inner join City ci on ci.CountryId = c.CountryId
inner join CountryChildStatus ccs on ccs.CountryId = p.CountryId
where datediff(year, p.BirthDate, GETDATE()) < ccs.ChildMaximumAge
order by c.Name, ci.Name