CODEWITHSUNDEEP
sql
DB95
DB95

Reputation: 1

Average columns between date range from a different table

so I'm sure there is a simple solution here but I can't get it to work. I currently have two tables structured as so:

Table 1                                                                  Table 2
Name    Date    x      y     z                                         Name      Date

John    03/11   91     15    3                                         Mary      05/26
Mary    05/25   95     10    1                                         John      08/15
John    08/14   89     13    5 
John    08/15   85     11    4

I want to be able to get the averages for each of the columns (x,y,z) for each person where the date in table 1 is within 7 days prior of the date in table 2. ie it should average the 2nd and 3rd entries for John and the entry for Mary. I have tried:

SELECT avg(x), avg(y), avg(z)
FROM table1
Where table1.Date
in(SELECT * FROM table2 WHERE table1.date BETWEEN table2.date AND table2.date - DATEADD(day, 7, table2.date)
GROUP BY table1.Name = table2.Name

Any help would be greatly appreciated.

Upvotes: 0

Views: 105

Answers (3)

Jorge Lopez
Jorge Lopez

Reputation: 467

select t1.Name, t1.Date
into #temp1
from t1 join t2 where
t1.name = t2.name and t1.date >= DATEDADD(day,-7,t2.date) and t1.date <= t2.date
group by t1.Name, t1.Date

select
t1. Name, t1.Date, avg(x) as avgX, avg(y) as avg (y), avg(z) as avgZ
from #temp1 join t1 on #temp1.Date = t1.Date and #temp1.Name = t2.Name
group by t1. Name, t1.Date

Upvotes: 0

PPJN
PPJN

Reputation: 352

You wouldn't want to use an IN statement. You want to compare the data in each table but you only need the output from one of the tables. In this case, I would recommend using an EXISTS statement. Please note I assume you are using SQL Server.

-- utilizing an exists statement because the rows from tbl2 aren't needed for the output, only the comparison with tbl1 data
select      a.Name  Name
            ,avg(X) Avg_X
            ,avg(Y) Avg_Y
            ,avg(Z) Avg_Z
from        tbl1    a
where       exists(select * from tbl2 b where b.Name = a.Name and a.Dt between dateadd(day,-7,b.Dt) and b.Dt)
group by    a.Name

Upvotes: 0

Avi
Avi

Reputation: 1845

I think this should give the expected results. I assume it is SQL server so it will work in SQL server 

SELECT t.name,avg(x), avg(y), avg(z)
FROM table1 t 
Where exists 
(select 1 from  table2 t2 where t2.name = t.name  and t.date between dateadd(day, -7, t2.date) and t2.date )
GROUP BY t.name

Upvotes: 1

Related Questions