Hi Devraj,
Please try below query
SELECT Convert(date,[Date]),Count(Id) FROM [Table]
Where Status = 'Y'
GROUP BY Convert(date,[Date]),Status
Here inside convert first date is keyword date and second [Date] is your field name.
The output will be shown, date wise active user count.
Please let me know if you have any concern or query on this or if I am missing something.
Thanks