4
\$\begingroup\$

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;
\$\endgroup\$
  • \$\begingroup\$ To improve readability, you should format your sql by some tool. That being said, does your sql work as intended? \$\endgroup\$ – dfhwze Jun 6 at 10:43
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – 200_success Jun 6 at 10:58
  • 1
    \$\begingroup\$ @PavelKononenko Please don't change the query after an answer was made. \$\endgroup\$ – dfhwze Jun 6 at 12:29
  • 1
    \$\begingroup\$ @Graipher Thanks, it was my fault, sorry. \$\endgroup\$ – Pavel Kononenko Jun 6 at 15:25
  • 1
    \$\begingroup\$ @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). \$\endgroup\$ – Graipher Jun 6 at 15:27
5
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.