Calculate the number of loans issued by the Regional offices for each business week in the period from 38 to 40 business week of 2011 inclusive. Sort by name of the regional unit, business week.
dataset and snippet here - http://www.sqlfiddle.com/#!18/53151/4
Are the joins correct in this query?
create table territory_type( id int primary key identity (1,1), name nvarchar(50) not null, ); create table territory( id int primary key identity (1,1), parent_id int null, name nvarchar(50) not null, territory_type_id int not null, foreign key(territory_type_id) references territory_type(id), constraint fk_tr_parent foreign key (parent_id) references territory(id) on delete no action ); create table deal( dl_id int primary key identity (1,1), dl_valutation_date datetime not null, dl_sum decimal not null, dl_territory_id int not null, foreign key(dl_territory_id) references territory(id) ); create table business_calendar( id int primary key identity (1,1), bc_year int not null, week int not null, date_begin date not null );
select trd.name as "Regional office", bc.bc_year as "Year", bc.week as "Week", sum(d.dl_sum) as "Sum of credits", count(d.dl_id) as "Count of credits" from deal as d join business_calendar as bc on (bc.date_begin < d.dl_valutation_date or bc.date_begin = d.dl_valutation_date) and (dateadd(day, 6, bc.date_begin) > d.dl_valutation_date or dateadd(day, 6, bc.date_begin) = d.dl_valutation_date) join territory as t on t.id = d.dl_territory_id join territory as trd on t.parent_id = trd.id where bc.week in (38,39,40) group by trd.name,bc.bc_year, bc.week order by trd.name, bc.week asc;