CODEWITHSUNDEEP
sqlsql-server-2008
Dean MacGregor
Dean MacGregor

Reputation: 18700

SELECT returning aggregate answer from one table based on weightings in another

I have a table with data that looks like this. The col1 and col2 are values and the index is based off the nodeID, Date, and Hour

nodeID    col1    col2    Date      Hour
  1       8       4      1/1/2010     1
  2       2       7      1/1/2010     1
  3       1       2      1/1/2010     1
  4       6       3      1/1/2010     1     
 ...     ...     ...      ...        ...
  1       2       2      1/1/2013     1
  2       3       4      1/1/2013     1
  3       7       1      1/1/2013     1
  4       5       1      1/1/2013     1

I have another table that has different weightings based on the day (not the hour) and it will look like this except there will be many names in addition to 'foo'. Each name will be in more than one row and each of those rows will have a weighting factor of nodeIDs.

Each name for a particular day will have a weighting that adds up to one. 

name       nodeID    weighting        Date
foo          1         .6            1/1/2010
foo          2         .4            1/1/2010
foo          1         .6            1/1/2013
foo          2         .3            1/1/2013
foo          3         .1            1/1/2013
...         ...        ...           ...

I need to query for the data from all the names with weighted averages of the parent data in table 1.

For example

name    col1     col2     Datecol    Hour
foo      5.6      5.2      1/1/2010    1
foo      2.8      2.5      1/1/2013    1

Note that the weightings changed from 1/1/2010 to 1/1/2013 and even included an extra nodeID.

Upvotes: 0

Views: 58

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

select 
    name, 
    sum(col1*weighting) as weightedCol1, 
    sum(col2*weighting) as weightedCol2,
    n.date, 
    [hour]
from
    nodes n
    inner join weights w
        on w.nodeid = n.nodeid
        and w.date = n.date
group by 
    name, n.date, [hour]

Upvotes: 1

Related Questions