How to compose a MSSQL query?

Question

SQL

Greetings.
You must choose to create a query that will count the number of dates by the range of dates + group it. If nothing was found in the range for some date, you need to display it and set it to 0.
Here's what I got:

SELECT COUNT(DISTINCT(session_id))         AS count1,
       CONVERT(varchar(10),event_time,120) AS event_time 
FROM dbo.logs_ft 
WHERE CONVERT(varchar(10),event_time,120) BETWEEN '2017-11-01'
                                              AND '2017-11-09'
      AND event = 'recognition_result_interpretation' 
      AND value = '1'
GROUP BY CONVERT(varchar(10),event_time,120)

Conclusion

"count1" "event_time"
"1" "2017-11-01"
"28" "2017-11-02"
"48" "2017-11-03"
"20" "2017-11-04"
"15" " 2017-11-06"
"35" "2017-11-07"
"46" "2017-11-09"

As you can see, there is nothing for 5 and 8, but it is necessary that the date be present and the value of count be equal to 0.

Answer 1

Miracles, Cinderella, do not happen. If this is a selection, then you can only select something that exists in the database. If the record does not exist, then you can do CASE sessionid is null then 0 else sessionid end
case write instead of Null - 0, it's real.
But if there is no date, then it is unrealistic to magically put the correct date value instead of Null.
Means? You will have to do this date by hand. You make a variable. Throw in it the minimum value of the range, 01. Then make this variable DateAdd + 1 day, if the number of records is strictly equal to the range.
If not, then fill in the temporary table. Cursor/loop. The number of days is. You give it some id and put the same id in any way in your first request. Further join between them. Everything.