Sql-server – Queries for unique names from a site table using multiple criteria

sql serversql-server-2005t-sql

So I'm just learning SQL but have wound up as a database manager. The software they use is overall easy but is very restrictive in the reports it can create, though it does allow custom SQL reports.

As such, I need to find a way to do one (or all) of several things, #4 now being the most important:

  1. Select unique site names which have been referred to at some point.
  2. Select unique site names and how many times they have been referred to.
  3. Select unique site names and the last date they were referred to.
  4. Select unique site names which have never been referred to.

And do each of these without including sites which have been deleted.
Being new to SQL, I haven't even figured out #1 yet.

Relevant tables and columns:

╔══════════════╦═════════════════╦════════════════════════╗
║    Table     ║    Columns      ║    Settings            ║
╠══════════════╬═════════════════╬════════════════════════╣
║ site         ║ site_id         ║ PK, int, not null      ║
║ 207 columns  ║ name            ║ varchar(100), not null ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ site_service ║ site_service_id ║ PK, int, not null      ║
║ 192 columns  ║ site_id         ║ in, not null           ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ calls        ║ call_id         ║ PK, int, not null      ║
║ 183 columns  ║ call_num        ║ char(10), not null     ║
║              ║ call_date       ║ datetime, null         ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ referrals    ║ referral_id     ║ PK, int, not null      ║
║ 5 columns    ║ call_id         ║ int, not null          ║
║              ║ site_id         ║ int, null              ║
║              ║ site_service_id ║ int, null              ║
╚══════════════╩═════════════════╩════════════════════════╝

I just found this working query in the custom reports of the program. It is labeled "Referral statistics-Existing sites".

SELECT rl_ss_s.name, COUNT(cl.call_num) AS [NumberOfCalls]  
FROM calls cl  
LEFT JOIN referral rl ON cl.call_id=rl.call_id  
LEFT JOIN site_service rl_ss ON rl.site_service_id=rl_ss.site_service_id  
LEFT JOIN site rl_ss_s ON rl_ss.site_id=rl_ss_s.site_id  
WHERE (cl.call_date BETWEEN 'ASK LATER: DATETIME' AND 'ASK LATER: DATETIME')  
AND cl.deleted IS NULL  
AND rl_ss_s.deleted IS NULL  
AND cl.client_group_id='1'  
GROUP BY rl_ss_s.name

I removed the WHERE filter for cl.client_group_id (as I'm not sure how it helps), then ran it. It does appear to meet goals 1 & 2, but has a glitch. Among the records contained in the report is a single blank-name site with roughly 4 times as many calls as the most referred to named site. As such, I'm not sure if it's combining all the deleted sites or something else. I can easily accommodate for it in the generated Excel file by deleting that line if it is combining deleted sites, I just have to figure out what it's doing.

I know what most of the functions in that example do, I just haven't figured out how to put it together to do what I need.

If you need any more information I have not included or clarification on anything, please let me know.
Any ideas how I put this together to achieve one (or all) of these goals?

Best Answer

1 Select unique site names which have been referred to at some point.

Get rows from site where site_id exists in referrals. This assumes site name is unique in site. If not you can add a distinct.

select s.name
from dbo.site as s
where s.deleted = 0 and
      exists (
             select *
             from dbo.referrals as r
             where s.site_id = r.site_id
             )

2 Select unique site names and how many times they have been referred to.

Join site to referrals and count the number of rows grouped by site name.

select s.name, 
       count(*) as times_referred
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
where s.deleted = 0
group by s.name

3 Select unique site names and the last date they were referred to.

Add a join to calls and get the max call_date for each site name.

select s.name, 
       count(*) as times_referred,
       max(c.call_date) as last_call_date
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
  inner join dbo.calls as c
    on r.call_id = c.call_id
where s.deleted = 0 and
      c.deleted = 0
group by s.name

4 Select unique site names which have never been referred to.

Same as first query only you get the sites that does not exist in referrals.

select s.name
from dbo.site as s
where s.deleted = 0 and
      not exists (
                 select *
                 from dbo.referrals as r
                 where s.site_id = r.site_id
                 )
Related Question