# SQL query to count loans issued during each week

Posted on

Problem

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

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

Solution

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
``````