Re: Sql muddle

On 02-Apr-2012 08:56 , Dave wrote:

I'm getting in a real twist with this one :

Given a table with a client and his children with their dates of
birth, I want to select those clients having at least one child aged
over 20 AND at least one child under 20.

For lack of DDL to describe the problem, I will make-up a TABLE with a solution both reflecting that TABLE DDL as a given and very specific to the one-over and one-under; as not very extensible for answering more diverse questions per use of MIN and MAX:

<code>

create table qtemp/cbd (c dec, bd date) /* C:client, BD:BirthDate */
; -- each BD is a separate row for each child of the client C
insert into qtemp/cbd values
(3, current date-22 years), (3, current date-18 years) /* 3 Slt */
, (1, current date-22 years) /* just 1, so too few 1 Omt */
, (2, current date-22 years), (2, current date-20 years)
, (2, current date-18 years), (2, current date-18 years) /* 2 Slt */
, (4, current date- 5 years), (4, current date- 2 years)
, (4, current date) /* 3, but all too young 4 Omt */
, (5, current date-22 years), (5, current date-22 years)
, (5, current date-22 years) /* 3, triplets, all too old 5 Omt */
, (6, current date-22 years)
, (6, current date-20 years) /* 2, one equals 20 years so? 6 ??? */
; -- clients 3 and 2 meet the given selection criteria
select c
/* , min(int((current date-bd)/10000)) */
/* , max(int((current date-bd)/10000)) */
from qtemp/cbd
group by c
having count(*)>1
and min(int((current date-bd)/10000)) < 20
and max(int((current date-bd)/10000)) > 20
; -- clients 3 and 2 are selected for output, without ordering

</code>

Regards, Chuck