COUNT(*) without Temp Table?

[tqeonline]

Can I Get the count of this:

SELECT cast(convert(varchar,max(AU_TIME),101)as DateTime) AS CLOSED_DATE
FROM BUG, AUDIT_LOG, AUDIT_PROPERTIES WITH (NOLOCK)
WHERE AU_ENTITY_TYPE = 'BUG'
AND AP_FIELD_NAME = 'BG_STATUS'
AND AU_ACTION_ID = AP_ACTION_ID
AND AU_ENTITY_ID = BG_BUG_ID
AND AP_NEW_VALUE = 'Closed'
AND BG_STATUS = 'Closed'
AND BG_DETECTED_IN_RCYC = 1118
AND cast(convert(varchar,AU_TIME,101)as DateTime) = DATEADD(DD,@COUNT,@START_DATE) 
GROUP BY AU_ENTITY_ID

Without having to make a temp table?

If I try

Count(cast(convert(varchar,max(AU_TIME),101)as DateTime))

and it throws an error about using aggregate in a sub query.

REason I have to do MAX(AU_TIME) is because there are multiple AU_TIMES for an AU_ENTITY_ID and I have to get the max.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[simian336]

It might help if you could show some data examples and what you are trying to achieve.

Simi