[thelist] further info (d'oh! was RE: SQL aggregation ignorance)

[Brian Cummiskey]

Canfield, Joel wrote:
> so for DATEPART I should be looking at DAY(date), MONTH(date), etc.
>   
Joel, I'd suggest doing this all in the query rather than parsing it out 
in the ASP code.  It will be much more efficient.

declare @date datetime
set @date = getdate()
select convert(varchar(12), @date, 101)


That little snipit will give you just the date.  (email me off list if 
you want a user defined function based off this for ms sql 2000 that 
will return the date only in a datetime field.)



> question still stands, though: does this make sense? any pointers?
>   

Without seeing the table schema, i can't offer any suggestion that will 
help.  Is this all one table or multiple tables?


>> I can't fathom how to do this without multiple trips to the database,
>> which irks me.
>>     
It can be done, but again, it depends on how the tables are related, if 
at all.
>> http://www.spinhead.com/GetRowsSample.html
>>
>> I can't fathom how to get the results aggregated (counts, sums, and
>> averages) by date.
>>     

average might throw some problem, but sum and count should be fairly 
simple to come up with a query.