Re: SQL question...

You don't state what platform you have ... however, if you have dbaccess you can call it via a shell script and control the loop from within that: days=7 while [ $days -ge 1 ] do let "days = $days - 1" emp_script $days done ### Name: emp_script dbaccess emp_db<<! select emp_name from emp_tab a where bought_date between today - 7 units day and today and not exists (select emp_name from emp_tab b where a.emp_name = b.emp_name and bought_date = today - ${3} units day)! This will check out the last 7 days. I've not tested this nor am I convinced if it is economical, but I hope it's a start. Chris Manoj Nanda <manoj@uk.point4.com> on 04/05/99 17:03:24 Please respond to Manoj Nanda <manoj@uk.point4.com> To: informix-list@iiug.org cc: (bcc: Chris West/Finance/MEDAS) Subject: SQL question... 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. -- ________________________________________________________________________ Manoj Nanda Point4 Consulting Email:manoj@point4.com Kingston-Upon-Thames Http://www.point4.com United Kingdom Tel :+44 (0)181 255 4004 Europe's Premier Internet technologists Fax :+44 (0)181 255 4044 _______________________________________________________________________