Re: Brief query puzzle

In article <1993Dec20.020805.13750_at_beau.atlanta.dg.com> klm_at_atlanta.dg.com (Kim Medlin) writes:
>From: klm_at_atlanta.dg.com (Kim Medlin)
>Subject: Brief query puzzle
>Date: Mon, 20 Dec 93 02:08:05 GMT
 

>I'd like to write a query that returns a status code value as it is
>stored in the database UNLESS the current system date falls outside
>of two date attributes that are in the same table, in which case
>return an "X" for the status code value.
 

>The table looks like this:
 

>CUSTOMER table
>--------------
>customer_number decimal
>status_code char(1)
>effective_date date
>expiration_date date
 

>The data looks like this:
 

>customer_number status_code effective_date expiration_date
>--------------- ----------- -------------- ---------------
>1 A 1-jan-93 31-dec-93
>2 A 1-jan-93 2-jan-93
>3 R 1-dec-93 31-dec-93
 

>The current system date value is 19-dec-93.
 

>The results of the query should be:
 

>CUSTOMER_NUMBER DERIVED_STATUS_CODE
>--------------- -------------------
>1 A
>2 X
>3 R
 

>Thanks in advance for your creativity. I will post all valid solutions.
 

>--
>-------------------------------------------------------------------------
>Kim Medlin Email: klm_at_atlanta.dg.com
>Atlanta Systems Integration Services CEO: Kim Medlin:DGC
>Data General Corporation Phone: 404 705-2653
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>It's nice to be important, but more important to be nice.
>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Try this one:

select A.customer_number customer_number, A.status_code derived_status_code from customer A
where sysdate between A.effective_date and A.expiration_date

union

select B.customer_number, 'X'
from customer B
where sysdate not between B.effective_date and B.expiration_date

Hope this will help

JOANNEUM RESEARCH                   Tel:                  +43 (0)316 8020 196
c/o Reinhard Brantner               Fax:                  +43 (0)316 8020 191
Steyrergasse 17                   X.400:   <C=at;A=ada;P=joanneum;S=brantner>
A-8010 Graz / AUSTRIA          Internet:         brantner_at_pbox.joanneum.ac.at
-----------------------------------------------------------------------------