sql - How do I identify Interaction in a column and take sum based on values in two other columns? -

i have 3 tables-
table 1

customer id | date       | score | score factor ------------+------------+-------+-------------        100  | 2014-10-10 |   15  |         .25        100  | 2014-12-12 |   25  |         .35        100  | 2014-08-08 |   35  |         .65        100  | 2014-09-08 |   45  |         .55        100  | 2014-01-10 |   15  |         .25        100  | 2014-12-12 |   75  |         .85        100  | 2014-08-08 |   85  |         .65        100  | 2015-09-08 |   45  |         .55        200  | 2014-10-10 |   45  |         .25        200  | 2014-12-12 |   55  |         .35        200  | 2014-08-08 |   35  |         .65        200  | 2014-09-08 |   45  |         .55        200  | 2014-01-10 |   55  |         .25 

table 2
score | group# | group label -------+--------+-----------+ 10 | 1 | superior | 15 | 1 | superior | 25 | 1 | superior | 35 | 2 | mediocre | 55 | 2 | mediocre | 65 | 3 | poor | 75 | 3 | poor | 85 | 4 | critical |

table 3

interaction  | group label  | group label  | final score factor -------------+--------------+--------------+--------------     int1     |   superior   |   medicocre  |        .80     int2     |   superior   |   poor       |        .90     int3     |   poor       |   critical   |        .95 

based on these tables, here's need find

identify whether 'score' 'customer id' belongs group particular year

from table 1 , table 2- customer 100 year 2014 has scores of-

• 15, 25 - group label "superior"
• 35 - group label "mediocre"
• 45 - no group

once groups have been identified, table 3, identify if groups have interactions , map corresponding 'final score factor'.

customer 100 has score belong group label "superior" , "mediocre". therefore, int1 exists , corresponding 'score factor' .80.

so expected result table should follows-

customer id |  date      | score| score factor| group#| group label| interaction  | final score factor -------------+------------+------+-------------+--------+-----------+--------------+------------------ 100         | 2014-10-10 | 15   | .25         | 1     | superior   | int1         |   .80 100         | 2014-12-12 | 25   | .35         | 1     | superior   | int1         |   .00 100         | 2014-08-08 | 35   | .65         | 2     | mediocre   | int1         |   .00 100         | 2014-08-08 | 45   | .55         | null  | null       | null         |   .55 100         | 2014-12-12 | 75   | .85         | 3     | poor       | int3         |   .95 100         | 2014-08-08 | 85   | .65         | 4     | critical   | int3         |   .00 

note:

1. the 'final interaction score' accounted once per interaction. repeating values made equal .00
2. if score factor not belong interaction, same score carry forwarded final score factor (row 4) in above example

now, have take sum of 'final score factors' per customer per year. therefore results table(above),

resultant score = .80 + .55 + .95

i don't understand way you're supposed map interactions table. made table variables input follows:

declare @customers table (id int, thedate date, score int, scorefactor varchar(5) ) insert @customers values(100,'2014-10-10',15,'.25') insert @customers values(100,'2014-12-12',25,'.35') insert @customers values(100,'2014-08-08',35,'.65') insert @customers values(100,'2014-09-08',45,'.55') insert @customers values(100,'2014-01-10',15,'.25') insert @customers values(100,'2014-12-12',75,'.85') insert @customers values(100,'2014-08-08',85,'.65') insert @customers values(100,'2015-09-08',45,'.55') insert @customers values(200,'2014-10-10',45,'.25') insert @customers values(200,'2014-12-12',55,'.35') insert @customers values(200,'2014-08-08',35,'.65') insert @customers values(200,'2014-09-08',45,'.55') insert @customers values(200,'2014-01-10',55,'.25')  declare @scores table (score int, groupno int, grouplabel varchar(10)) insert @scores values(10,1,'superior') insert @scores values(15,1,'superior') insert @scores values(25,1,'superior') insert @scores values(35,2,'mediocre') insert @scores values(55,2,'mediocre') insert @scores values(65,3,'poor') insert @scores values(75,3,'poor') insert @scores values(85,4,'critical')  declare @interactions table(interaction varchar(5), grouplabel1 varchar(10), grouplabel2 varchar(10), finalscorefactor varchar(5)) insert @interactions values('int1', 'superior', 'mediocre', '.80') insert @interactions values('int2', 'superior', 'poor', '.90') insert @interactions values('int3', 'poor', 'critical', '.95') 

then, ran simple query match customer scores groups year 2014:

select distinct c.id, s.grouplabel @customers c inner join @scores s on c.score = s.score thedate < '2015-01-01' , thedate >= '2014-01-01' 

the output this:

id  | grouplabel --------------- 100 | critical 100 | mediocre 100 | poor 100 | superior 200 | mediocre 

so i'm getting customer 100 having not superior , mediocre, poor , critical. can explain better how match stuff in table 3?