SQL query to count loans issued during each week

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?

DDL:

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
);

QUERY:

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;

Your query could be simplified.

• use inner join to avoid ambiguity when reading the query
• temporal interval join date between start and end (inclusive end)

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)

 inner join business_calendar as bc
        on d.dl_valutation_date between bc.date_begin and dateadd(day, 6, bc.date_begin)

snippet:

select 
    trd.name as "Региональное подразделение",
    bc.bc_year as "Год",
    bc.week as "Неделя",
    sum(d.dl_sum) as "Сумма выданных займов",
    count(d.dl_id) as "Кол-во займов"
from deal as d
inner join business_calendar as bc
    on d.dl_valutation_date between bc.date_begin and dateadd(day, 6, bc.date_begin)
inner join territory as t on t.id = d.dl_territory_id
inner 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