join - SQL Query help needed - Multiple rows in 1st table should match to multiple table in 2nd table -

problem illustration trying find magical query generate summary information. have mapped problem fictitious illustration. have 'waterleakage%' table records leakage occurred in hotel rooms on several year.

i have table records waterconsumption in liters each table.

now have find actual water leakage in liters given room number on given date range.

basically have group several rows in 'waterleakage%' table several rows in 'waterconsumption' table. trying figure out magical efficient query find this. unable find it, please help.

declare @start_date_param date = '01/10/2017'; declare @end_date_param date = '01/31/2017'; declare @room_number int = 101;  if (exists (select * information_schema.tables table_name = '#water_consumption'))     drop table #water_consumption;   if (exists (select * information_schema.tables table_name = '#water_leakage_per'))     drop table #water_leakage_per;  --table daily daily water consumption per room create table #water_consumption( room_number int, uday date, water_consumption_liter int )  --table water leakage percent per room date range create table #water_leakage_per ( room_number int, start_date date, end_date date, water_leakage_percent int )  -- raw data insert #water_leakage_per(room_number,start_date,end_date,water_leakage_percent)  values(101,'2017/01/01','2017/01/02',5), (102,'2017/01/01','2017/01/05',10), (101,'2017/01/04','2017/02/06',10);  -- raw data insert #water_consumption  values(101,'2017/01/01',100), (101,'2017/01/02',100), (101,'2017/01/03',100), (101,'2017/01/04',100), (101,'2017/01/05',100), (101,'2017/01/06',100), (102,'2017/01/01',100), (102,'2017/01/02',100), (102,'2017/01/03',100), (102,'2017/01/04',100), (102,'2017/01/05',100);  declare @totalleak real = 0; select * #water_consumption; select * #water_leakage_per;  select * #water_consumption t1 join (select * #water_leakage_per room_number=@room_number) t2 on (t1.room_number=t2.room_number , t1.uday >= t2.start_date , t1.uday <= t2.end_date);  drop table #water_consumption; drop table #water_leakage_per; 

i close solution now. changed thinking. join reverse now.