SQL query to count loans issued during each week

Question

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;

• dfhwze: To improve readability, you should format your sql by some tool. That being said, does your sql work as intended?
• 200_success: I think that this question is allowable, if you believe that the code is correct, to the best of your knowledge. Please copy the CREATE TABLE statements into the question, though, so that the post still makes sense even without the SQLfiddle link.
• dfhwze: @PavelKononenko Please don't change the query after an answer was made.
• Pavel Kononenko: @Graipher Thanks, it was my fault, sorry.
• Graipher: @PavelKononenko No worries, we are all here to learn. That includes learning how each site of the SE network works (they all have their quirks).

Answer 1

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