[thelist] SQL Chaos!!

Joshua Olson joshua at waetech.com
Tue Jan 28 10:26:13 CST 2003


----- Original Message -----
From: "Martyn Haigh" <martyn.haigh at virgincosmetics.com>
Sent: Tuesday, January 28, 2003 11:06 AM


> Hello,
>
> I - like most people - hate SQL, often because I spend hours trying to do
> something unsuccessfully before pulling all my hair out!
>
> This is what I'm trying to do.
> I have a database with 4 fields lets call them ID, fieldA, fieldB and
fieldC
> and another database with an ID that is the same as the previous ID and
> DateCreated (plus some other stuff that doesn't matter at the moment).
>
> FieldA through C are all Bits.
> ID is an int.
> DateCreated is a DateTime.
> I'm trying to create a stats package that will will count up the totals of
> each FieldA through C for each day and then give a total of fields A
through
> C for days between two given date inputs.
>
> Much like this
> 01-01-03 02-02-03 03-02-03 ....etc
> Field A 3 5    12 ....etc
> Field B 7 12       0 ....etc
> Field C 12 24    15 ....etc
> Total 22 41    27 ....etc

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




More information about the thelist mailing list