Better query?

I have a table from a 3rd party application that stores the time that
events occurred as the number of seconds since Jan 1 1970. I want to
perform an aggregate function on the values stored in each event, grouped
by date and present the sorted results. Here's what I have:
SELECT
DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
Avg(Value)
FROM Data
GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
Is there an easier way to write this without duplicating the DateAdd
calculation three times' I can't use Stored Procedures (I'm not allowed
to modify the database in any way.)
-mdb>> have a table from a 3rd party application that stores the time that even
ts occurred as the number of seconds since 1970-01-01 <<
Oh yeah, that makes sense :) !! I always hated the term "legacy data"
when what we meant was "family curse from Hell!" instead.
Build a table like this:
CREATE TABLE FoobarDates
(foobar_date CHAR (10) NOT NULL,
start_second INTEGER NOT NULL,
finish_second INTEGER NOT NULL
PRIMARY KEY(start_second, finish_second ));
1) Avoid math in SQL-- it is designed to do JOINs. not algebra.
2) An ORDER BY on a SELECT is **meaningless** -- read any basic SQL
book. Indexes matter.
3) Use a spreadsheet to do the computations for the table. Easy and
faster than code.|||you can use a derived table Michael...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>I have a table from a 3rd party application that stores the time that
> events occurred as the number of seconds since Jan 1 1970. I want to
> perform an aggregate function on the values stored in each event, grouped
> by date and present the sorted results. Here's what I have:
> SELECT
> DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
> Avg(Value)
> FROM Data
> GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
> ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
> Is there an easier way to write this without duplicating the DateAdd
> calculation three times' I can't use Stored Procedures (I'm not allowed
> to modify the database in any way.)
> -mdb|||Sorry, that should be...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate = DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl...
> you can use a derived table Michael...
> SELECT PointDate, Avg_Value = Avg( Value )
> FROM (
> SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
> Value
> FROM Data ) AS dt
> GROUP BY PointDate
> ORDER BY PointDate ASC
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
> news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1135732983.997588.94360@.o13g2000cwo.googlegroups.com:
> Oh yeah, that makes sense :) !! I always hated the term "legacy data"
> when what we meant was "family curse from Hell!" instead.
As a matter of fact, this is NOT legacy Data. In fact, it is an
application that would not have existed 10 years ago, because the
technology that is represented by the data in the database didn't exist.
(I'm not going to mention the vendor, but it is an IP Telephony monitoring
system.)
The rest of your post sounds like a troll, so I'm not going to comment,
except for this comment.
-mdb|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl:

> you can use a derived table Michael...
>
Wow... you know I had always tried to do something similar, but I never
could get it to work because I didn't know that I had to include the 'AS
<alias>'. Thanks! Now I get to go back and re-write a bunch of stored
procedures in other databases I've worked on that I used memory tables in!
:)
-mdb