Re: SQL question...

Manoj Nanda wrote in message <372F1A4C.BB30FA06@uk.point4.com>... >Hi i am a bit stuck on a particular sql query in informix... >say i have a view with the following data ..... > >Emp Name amountbought date > >mr a 1 3/5/1999 >mr b 2 3/5/1999 >mr c 2 3/5/1999 >Mr b 3 4/5/1999 >Mr c 4 4/5/1999 >mr c 2 5/5/1999 > >An entry is made into a table on a daily basis - the view is derived >from this table > >i want to know who hasnt made an entry on a particular date >prefereably for a range of dates ie last week.. > >so result would be something like... for between 3 and 5 may > > >emp name date not done >mr a, 4/5/1999 >mr a, 5/5/1999 >mr b, 5/5/1999 > >the problem is , when there are no entries at all in the database >eg for date 2/5/1999 how can i get a list of all the people who havent >done anything for the week eg between 1 and 8 th may 1999. >is there a way in informix to cycle through dates using system time >or is there another way. > > >Any help is much appreciated. > >Manoj. Without a separate table of all the people who are supposed to post, then I would assume you would want a list of anyone that has posted at all that has not posted in the date range specified. In that case, you could use the following SQL: select empname from buy_table a where not exists (select * from buy_table b where b.date between '5/3/1999' and '5/8/1999' and b.empname=a.empname) OR try select empname from buy_table where empname not in (select distinct empname from buy_table where date between '5/3/1999' and '5/8/1999'). Doug Agnew DBA Charlotte Pipe & Foundry dagnew@charlottepipe.com