Re: Query Help?
Frank writes:
> I can't seem to find a general SQL newsgroup, so can someone here help?
> I have a table with the following information
> ID #
> at_date
> am_code
> pm_code
> I need to find all ID #'s that have a value (ie is not NULL) for the am
> and pm codes for EVERY day from 06/12/95 to 06/15/95. So the ID # must
> have not null values for ALL 5 days. Using informix for AIX (RS/6000)
> 4.1
> Thanks, Frank
Well, Frank this solution will work ONLY if your table's unique key is (id,
at_date). The following solution works on Ver 5.0 on DG, and I think it should
work on your version and machine too since I'm using primary SQL functions.
select id
from my_table
where my_table.am_code is not null
and my_table.pm_code is not null
and my_table.at_date between <date_1> and <date_2>
group by id
having count(*) = (<date_2> - <date_1> + 1)
And yes, depending upon which tool you are using, substitute the date constants
with proper values. You might have to use date() function in the "having"
condition for proper evaluation of individual date variables. In isql in tested
it using following statement:
select id
from my_table
where my_table.am_code is not null
and my_table.pm_code is not null
and my_table.at_date between "06/15/95" and "06/12/95"
group by id
having count(*) = (date("06/15/95") - date("06/12/95") + 1);
Good luck !!
Girish
--------------------------------------------------------------------------------
Standard disclaimers apply.
--------------------------------------------------------------------------------