[thelist] SQL Chaos!!

Martyn Haigh martyn.haigh at virgincosmetics.com
Tue Jan 28 10:48:43 CST 2003


Joshua,

This is looking good - you are right in thinking that my DateCreated has a
date and time portion.  I was thinking that I could somehow group by
dateCreated using dateFormat and just separating the contents that way.
Does this sound plausible?

Thank you

Martyn


----------- original message
Martyn,

The toughest part of your query is going to be doing the daily summaries.
If the DateCreated field had 12am as the time portion for all the records
(or only included the actual date portion) then something like this would
work:

SELECT
  [table2].DateCreated
  Sum(FieldA),
  Sum(FieldB),
  Sub(FieldC)
FROM [table]
INNER JOIN [table2] ON [table].ID = [table2].ID
GROUP BY DateCreated

That would return data like this:

DateCreated, FieldA, FieldB, FieldC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1/1,12,5,6
1/2,7,9,9
... etc ...

It would be up to you to then work with the data for presentation.


If the DateCreated has a time portion that is scattered throughout the day,
or the relationship is one-to-many, you've got a somewhat different beast.
If that is the case, please let us know.

-joshua




DISCLAIMER: The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this email by
anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, distribution or any action taken or omitted to be taken
in reliance on it, is prohibited and may be unlawful.





More information about the thelist mailing list