Wrox Programmer Forums
|
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 29th, 2007, 11:20 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default Getting an average using SQL vs VBScript

Hello - I have an issue I hope someone can shed some light on. The following query:

SELECT DISTINCT count(osirReport.osirId), groupNames.gnName FROM ((((osirReport
LEFT OUTER JOIN originator ON osirReport.osirOrId = originator.orID)
LEFT OUTER JOIN shipClass ON originator.orScId = shipClass.scId)
LEFT OUTER JOIN groups ON shipClass.scId = groups.gScId)
LEFT OUTER JOIN groupNames ON groups.gGnId = groupNames.gnId)
Where (osirStatus=1) AND (osirReport.osirHri >=0) AND (osirReport.osirHri <= 20)
AND (osirReport.osirTypeId IN ( 2)) AND (osirReport.CreationDate >= '01/01/2007')
AND (osirReport.CreationDate <= '10/29/2007') group by groupNames.gnName order by groupNames.gnName;

produces this:

1 NULL
74 AASFEG
2 DIVERS
245 ENTIRE FLEET
20 HSFEG
24 MCDFEG
37 PBFEG
112 SCFEG
18 SUBFEG
112 Surface FEG

Which is how many incidents each of the groups have had for a given criteria. However what I want to be able to do is EG:

For each of the 74 AASFEG incidents there is a creation date. Can I in one query get the difference betweeen todays date and the stored creation date?

I am working on:
SELECT DISTINCT avg(cast( expression AS '10/30/2007' - osirReport.creationdate)... Sould I do this the long way using VBScript and more than one query?

TYIA


Wind is your friend
Matt
__________________
Wind is your friend
Matt
 
Old November 1st, 2007, 06:33 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Solution:

sql = "SELECT avg(datediff(day,creationdate," & amdate(date()) & ")) FROM...

Wind is your friend
Matt





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBScript or SQL solution to problem mat41 Classic ASP Professional 5 October 8th, 2008 05:13 PM
XML from SQL through VBScript raj_alapati XML 10 May 30th, 2007 03:35 AM
vbscript version of SQL 'SELECT DISTINCT' SoC Classic ASP Basics 1 February 8th, 2006 03:16 AM
sql help got Microsoft VBScript runtime error '800 marky1216 Classic ASP Databases 0 December 9th, 2005 12:45 AM
7Day Moving Average Gezza SQL Language 0 November 21st, 2003 02:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.