[AccessD] I need a better way to do this - looping

Finally got a chance (boss had other projects to do first) to try this
query and time it. Run time was 37259.37 seconds. I also did it with 3
queries. One to select the wells with pressure difference over 100, a
second to find the last date these selected wells were over a 100 and a
last one to calculate the days between today's date and the date
selected in the prior query. Total time to run was approximately 11
seconds. I think I will stick with the 3 query method. Thanks for your
efforts.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Thursday, January 03, 2008 3:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] I need a better way to do this - looping

SELECT T1.IT_Well, IIf(((SELECT IIF(IsNull(Max(IT_Date)),0,Max(IT_Date))
FROM dbo_Inj_Tests WHERE (Lin_Pres-Tbg_Pres)>=100 AND
IT_Well=T1.IT_Well)-(SELECT IIF(IsNull(Max(IT_Date)),(SELECT
Min(IT_Date) FROM dbo_Inj_Tests WHERE
IT_Well=T1.IT_Well)-1,Max(IT_Date)) FROM dbo_Inj_Tests WHERE
(Lin_Pres-Tbg_Pres)<100 AND IT_Well=T1.IT_Well))<0,0,(SELECT
IIF(IsNull(Max(IT_Date)),0,Max(IT_Date)) FROM dbo_Inj_Tests WHERE
(Lin_Pres-Tbg_Pres)>=100 AND IT_Well=T1.IT_Well)-(SELECT
IIF(IsNull(Max(IT_Date)),(SELECT Min(IT_Date) FROM dbo_Inj_Tests WHERE
IT_Well=T1.IT_Well)-1,Max(IT_Date)) FROM dbo_Inj_Tests WHERE
(Lin_Pres-Tbg_Pres)<100 AND IT_Well=T1.IT_Well)) AS NumberOfDaysOver100
FROM dbo_Inj_Tests AS T1
GROUP BY T1.IT_Well;

Ok, this is plain text, so you shouldn't get word wrap issues, but who
knows.  To pull off what you are really looking for, the subquery gets a
little more complex.

I went off the four fields you gave me, with the table name, so this
query should just work by cutting and pasting the SQL into a new query.

Here's the results:

For each IT_Well, if the latest record is less then 100, then it returns
0 for 'NumberOfDaysOver100'.  If the latest record is equal or greater
then 100, then it returns the number of DAYS that the well has been over
100.  Thus, if there IS a date where it was less then 100, then you get
the difference between that date and the existing date.  Ie:

12/29/2007 - 300
12/27/2007 - 95

This set (whether there is a record for the 28th or not (unless of
course there was one under 100) will return 2, which would be the number
of days over 100.  

If there are NO records where it's under 100, then it returns the number
of days between the first and last records, INCLUDING the last day, ie:

12/29/2007 - 300
12/27/2007 - 195

This would return 3, since it was over 100 on the 27th, it includes that
day.

Hope this helps.

Drew

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com