Drew Wutka
DWUTKA at Marlow.com
Thu Jan 17 11:22:22 CST 2008
WOW, talk about a time difference there, I really am surprised there was that much of a difference. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Thursday, January 17, 2008 10:50 AM To: Access Developers discussion and problem solving Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI BusinessSensitve material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.