title: Help with SQL to get Hits per day for today and 1 month prior
body: <p>I have this SQL now:</p>
  
  <pre><code>CREATE PROCEDURE dbo.sel_Track_HitsLast30Days(
  @projectID int
  )
  AS
  BEGIN
   DECLARE @FirstDay smalldatetime, @NumberOfMonths int, @priorMonth smalldatetime
  set @priorMonth = (SELECT CAST(
  (
  STR( YEAR( dateadd(m,-1, getDate()) ) ) + '/' +
  STR( MONTH( dateadd(m,-1, getDate()) ) ) + '/' +
  STR( DAY( dateadd(m,-1, getDate()) ) )
  )
  AS DateTime 
  ))
  Select @FirstDay = @priorMonth, @NumberOfMonths = 1
  ;WITH Days AS (
  SELECT @FirstDay as CalendarDay
  UNION ALL
  SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
  FROM Days
  WHERE DATEADD(d, 1, CalendarDay) &lt; DATEADD(m, @NumberOfMonths, @FirstDay+1)
  )
  SELECT calendarday,foundDate.TotalbyDate,foundDate.date FROM Days
  
  LEFT OUTER JOIN (
  
  SELECT
  COUNT(LEFT(visitDateTime, 11)) AS TotalbyDate,substring(convert( char(10), CONVERT( char(10), visitDateTime, 121 ) ), 1, 11) AS date
  
  FROM
    dbo.TrackingData
  WHERE
    visitDateTime &gt;= dateadd(d, datediff(d, 0, getdate()), -30) and projectID = @projectID
    GROUP BY substring(convert( char(10), CONVERT( char(10), visitDateTime, 121 ) ), 1, 11)
  
  
  ) foundDate on foundDate.date = CalendarDay
  
  order by 
  CalendarDay Desc
  END
  </code></pre>
  
  <p>This works ok, but It is not taking into account months with 31 days and I am not getting back today's date for some reason.</p>