how to perform an agregate on two non uniform tables

[techzone12]

I have a table called "Daily" (Daily data) that looks like this:
DateStamp KWH
11/6/2005 1867
11/7/2005 2001
11/8/2005 2023
.
.

A second table called "15_min" (15 minute data) looks like this:
[DateTime] KW
11/8/2005 00:00 am 201
11/8/2005 00:15 am 198
.
.
11/8/2005 11:30 pm 211
11/8/2005 11:45 pm 197


I want to do an insert into a third table called "totals" that looks like this:
DateStamp KWH
11/6/2005 2340
11/7/2005 2456
11/8/2005 2345

Where as an example:
totals.kWH for 11/8/2005 = sum(15_min.KW)/4 for the entire day of '11/8/2005' + daily.KWH for '11/8/2005'

i.e, The query will add all 15-minutes data from "15_min" table for the day of '11/8/2005' and then divide by 4. It will then do a straight addition of the resulting number to the KWH from "Daily" table.

How can I do this?

 

[BSman]

Where do you want to perform this calculation? Is it an Access (or VB) front end, or are you looking to perform this directly in SQL (which would require a stored procedure)?

Bob S.

 

[techzone12]

Directly in SQL with a Stored procedure

 

[bborissov]

What Type is DateStamp?

-- Maybe you need conversion for JOINing two tables
-- but not sure
INSERT INTO Totals (DateStamp, KWH)
SELECT DateTime, ISNULL(Daily.KWH,0) + SUM(15_min.KW)/4
       FROM 15_min;
       LEFT JOIN Daily ON 15_min.DateTime = Daily.DateStamp

not tested

Borislav Borissov

 

[techzone12]

Here is what I have and I think it works:

insert totals (date_stamp, KWH )

select dateadd( day, datediff( day, 0, getdate() ), -1 ),
(
(select
KWH
from
Daily
where
D1.Date_Stamp = dateadd( day, datediff( day, 0, getdate() ), -1 ))
+
(select
SUM(KW)/4
from
15_min
where
dateadd(day,datediff(day,0,15_min.[DateTime]),0) = dateadd( day, datediff( day, 0, getdate() ), -1 ))
)
go