Sql-server – Count the number of days in each month between two specified dates

Here's my attempt. Still think there are unanswered questions in your sample data, though.

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20120302',  @e = '20120605';

;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER 
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS 
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld) 
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

Results:

Month  Days
-----  ----
March    29
April    30
May      31
June      5

You don't want to use BETWEEN to satisfy this query for the reasons I outline in this blog post:

• https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

Instead you want an open-ended date range. Begins on the first of the month in question, and is less than the next month. To determine the first of the month given any date, you can do this:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

And to get first of the the following month, just add one:

SELECT DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, GETDATE()), 0);

Of course if you want last month only on the first of the current month, and this month after the first, then you can say:

SELECT DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())), 0);

So for your query, if you always want it relative to the current month:

...
FROM SupportTracker.dbo.ViewIssueListwBugTypeNDevice AS v
WHERE NOT EXISTS 
(
  SELECT 1 FROM SupportTracker.dbo.DashboardRecords
    WHERE bg_id = v.bg_id
)
AND v.bg_reported_date >= DATEADD(MONTH, 0, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE()))
AND v.bg_reported_date < DATEADD(MONTH, 1, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE()));

Or if you want to take a date as an optional parameter, you don't need to subtract a day, just pass in any date within the month you actually want (and if you pass in NULL, it will still fall back to the above formula):

  @Month DATE = NULL
...

  SET @Month = DATEADD(MONTH, 0, DATEDIFF(MONTH, 0, COALESCE(@Month, 
    DATEADD(DAY, -1, GETDATE())));  

...
FROM SupportTracker.dbo.ViewIssueListwBugTypeNDevice AS v
WHERE NOT EXISTS 
(
  SELECT 1 FROM SupportTracker.dbo.DashboardRecords
    WHERE bg_id = v.bg_id
)
AND v.bg_reported_date >= @Month
AND v.bg_reported_date < DATEADD(MONTH, 1, @Month);

Generally, though, the following is better to determine the given month, because datediff can cause issues:

SELECT FirstOfThisMonth = DATEADD(DAY, 1-DAY(GETDATE()), CONVERT(date, GETDATE()));