How to retrieve report figure between two dates

Question

For implicity, I am going to say we have 5 tables for SQL

1) Client Accounts 2) Leads 3) Calls 4) Report

Below is a SQL example I made up to get an idea how it works. My question is, how can I retrieve records that received a business report from date of the phone calls and 5 days onwards.

Example

Today is 8.06.2017, I made a phone call to a Lead. This lead is ABC. ABC is interested in XYZ. I sent a report to XYZ on 12.06.17. This is within 5 days. So this record should be retrieved and counted as I potentially find more than 100 records within 5 days.

SELECT Id, Name, DateOfCall
FROM Lead, Call, ClientAccounts
ON Lead.id = Call.id AND Lead.id = ClientAccounts.id
WHERE DATEDIFF (DateOfReport, StartOfCall, getDate()) < 5

If I execute this Sql statement, it should retrieve the above report, the name of ClientAccount and the Lead associated with that ClientAccount.

I hope this make sense and I am very beginner in SQL, but I am aware my syntaxes for SQL is wrong but the idea is understanding this and worry about syntax a little later as I do not have SQL server to execute this.

Answer 1

You aren't far off from your goal.

You need to use proper JOIN syntax; what you have is a bit fractured.

SELECT ... some columns ...
  FROM Lead L
  JOIN Call C  ON L.id = C.id
  JOIN ClientAccounts CA ON L.id = CA.id

That assigns the alias names L, C, and CA to your three tables.

You can try running this with SELECT * to see what you get. But using * in a finished query is a rookie mistake.

Next you need to give the names, with table aliases, of the columns you wish to SELECT. I have to guess which table each column comes from, because you didn't show us your table definitions. So, I guess...

 SELECT L.id, L.Name, C.DateOfCall
  FROM Lead L
  JOIN Call C  ON L.id = C.id
  JOIN ClientAccounts CA ON L.id = CA.id 

Run that. See what you get. If my example has the table or column names wrong, fix them. (Please don't ask us to fix them here on SO; we don't know your table layouts.)

Finally you need to select the date range. Try

WHERE C.StartOfCall >= CURDATE() - INTERVAL 5 DAY

This will get all rows where the call started on or after midnight five days ago.