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:
- Select unique site names which have been referred to at some point.
- Select unique site names and how many times they have been referred to.
- Select unique site names and the last date they were referred to.
- 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?