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

Solution:

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

Wind is your friend
Matt