How to get all dates in the current month with download count using SQL Query

 

Description:

In this example we explain that how to get all date of the current month like 1 oct, 2 oct… 31 oct etc.. Using SQL Query. Or how to get download count per day of the current month day wise using SQL Query.

We have requirement like display day wise download application count in website. So achieve this below is the SQL Query that will helpful for you to get daily date wise download count.

 

Query:

;WITH theDates AS

     (SELECT DATEADD(day,-30,DATEADD(hh, +05.30, getdate()))  as theDate

      UNION ALL

      SELECT DATEADD(day, 1, theDate)

        FROM theDates

       WHERE DATEADD(day, 1, theDate) <= DATEADD(day,0,DATEADD(hh, +05.30, getdate()))

     )

 

   SELECT CONVERT(DATE, theDate)as DownloadDate,COUNT(TotalCount) As TotalDownLoad

              FROM theDates t Left join  AppCounts ac on CONVERT(DATE, theDate)=ac.DownloadDate

      left join Application ap on ac.ApplicationId = ap.ApplicationId

      GROUP BY  CONVERT(DATE, theDate)