Count the number of days in each month between two specified dates I am trying to write an SQL Server formula which give the number of days in each month between two specified dates. E.g:
This looks like a lot of code but I think it steps through and explains how it meets the requirements of counting days in the range, including leaving out days from the next year (in case someone forgets to filter for it first, which the sample usage also handles):

CREATE FUNCTION dbo.ReturnDateRangeByMonth
(
    @start date, @end date
)
RETURNS TABLE WITH SCHEMABINDING 
AS
RETURN 
( 
  WITH d(d) AS -- recursive to get all the days in the range
  (
    SELECT @start UNION ALL SELECT DATEADD(DAY, 1, d) FROM d
    -- except the last day 
    WHERE d &lt; DATEADD(DAY,-1,@end) 
    -- and except any days from the next year
      AND d &lt; DATEFROMPARTS(YEAR(@start)+1, 1, 1)
      -- on &lt; 2012 use AND YEAR(d) = YEAR(@start)
  ),
  m(m) AS -- grab all 12 months to pivot on
  (
    SELECT 1 UNION ALL SELECT m + 1 FROM m WHERE m &lt; 12
  )
  SELECT m.m, c = COALESCE(COUNT(d.d),0) FROM m 
  LEFT OUTER JOIN d ON m.m = MONTH(d.d) GROUP BY m.m
);


Sample usage:

DECLARE @vaca TABLE (OOW date, RTW date);

INSERT @vaca(OOW,RTW) VALUES('20191222','20200107'),('20190326','20190528'),
('20190328','20190522'),('20190612','20190617'),('20190404','20190418');

;WITH v AS 
(
  SELECT OOW, RTW FROM @vaca WHERE OOW &gt;= '20190101' AND OOW &lt; '20200101'
)
SELECT * FROM v CROSS APPLY dbo.ReturnDateRangeByMonth(v.OOW, v.RTW) AS f
  PIVOT (MAX(c) FOR m IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS p
  OPTION (MAXRECURSION 366);


Results: