1
\$\begingroup\$

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 
\$\endgroup\$
  • 2
    \$\begingroup\$ "Is it the correct way of solving this task?" Did you test it? Does it work the way you expect it to? How did you test it? Your query has hardcoded values. \$\endgroup\$ – Mast Jun 7 at 11:32
2
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Your first suggestion, do you think it would also increase performance? \$\endgroup\$ – dfhwze Jul 7 at 15:39
  • 1
    \$\begingroup\$ I've responded as part of my answer. \$\endgroup\$ – Wes H Jul 8 at 16:11

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.