SQL query to count loans issued during each week
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 &lt; d.dl_valutation_date 
        or bc.date_begin = d.dl_valutation_date)
        and (dateadd(day, 6, bc.date_begin) &gt; 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