根据日期SQL的愤怒来计算

I have below mentioned sql query which gives me simple count as on that date or if i use I have below mentioned sql query which gives me