T-SQL query Choose name of clients, concluded more than one loan agreement in 2010

Question

QUESTION: Choose the name of clients, concluded more than one loan agreement in 2010, as the numbers and dates of issue of these contracts. Sort by name of the client and date of issue of the loan.

My question: Is it the correct way of solving this task? Sqlfiddle: http://www.sqlfiddle.com/#!18/c140c/1

DDL:

create table client(
    cl_id int primary key identity (1,1),
    cl_full_name nvarchar(100) not null,
);

create table deal(
    dl_id int primary key identity (1,1),
    dl_client_id int not null,
    dl_code nvarchar(100) not null,
    dl_valutation_date datetime not null,
    foreign key(dl_client_id) references client(cl_id)
);

QUERY:

SELECT  c.cl_full_name                      AS "ФИО Клиента", 
        d2.dl_code                          AS "Номер договора", 
        Cast(d2.dl_valutation_date AS DATE) AS "Дата выдачи" 
FROM (
        SELECT d.dl_client_id 
        FROM   deal AS d 
        WHERE  Year(d.dl_valutation_date) = 2010 
        GROUP  BY d.dl_client_id 
        HAVING Count(*) > 1
) AS t1 
INNER JOIN deal AS d2 
        ON d2.dl_client_id = t1.dl_client_id 
INNER JOIN client AS c 
        ON c.cl_id = d2.dl_client_id 
ORDER  BY c.cl_full_name, 
        d2.dl_valutation_date 

Answer 1

I believe your code will work based on what you provided. However, I would recommend a couple of changes.

• T1 does not need to be part of the result set as you do not retrieve anything from it. Since it is only used to filter, I've moved it to an exists clause.

• Avoid expressions on the data field when comparing to a static or known value. It takes a bit more typing, but can provide performance benefits in your code.

  SELECT  c.cl_full_name AS "ФИО Клиента",
          d2.dl_code AS "Номер договора",
          CAST(d2.dl_valutation_date AS DATE) AS "Дата выдачи"
  FROM  deal AS d2
    INNER JOIN client AS c ON c.cl_id = d2.dl_client_id
  WHERE EXISTS ( SELECT 1
         FROM deal AS d
         WHERE d.dl_valutation_date >= '20100101'
           AND d.dl_valutation_date < '20110101'
           AND c.cl_id = d.dl_client_id
         GROUP BY d.dl_client_id
         HAVING COUNT(*) > 1)
  ORDER BY c.cl_full_name,
   d2.dl_valutation_date ;
  

---

UPDATE to respond to comment

---

Leveraging good performance practices builds a foundation that makes good performance possible. If there is another part of your query that has a greater impact to performance, the first recommendation may or may not affect performance by itself. However, it can help to reveal other performance bottlenecks.