0

I am trying to write an SQL Server formula which give the number of days in each month between two specified dates. E.g:

enter image description here

  • 2
    What if the start date is in November and the end date is in January? What if the range is longer than 12 months? What is the longest range in number of days? What if two people have the same vacation start/end? Don't you want some other identifier here? – Aaron Bertrand Sep 11 at 19:49
  • What if the start date is in November and the end date is in January ? Answer: It will count for next year in 2020. What is the longest range in number of days? Answer: Any – boxi Sep 11 at 20:00
  • Also under July you have a value of 4, I think this is a mistake, since none of your OOW-RTW ranges affect July. – Aaron Bertrand Sep 11 at 20:36
6

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 < DATEADD(DAY,-1,@end) 
    -- and except any days from the next year
      AND d < DATEFROMPARTS(YEAR(@start)+1, 1, 1)
      -- on < 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 < 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 >= '20190101' AND OOW < '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:

enter image description here

  • Is the screen shot from SSMS? I've never seen a customization of the results pane like that. – scsimon Sep 11 at 20:41
  • @scsimon No, Azure Data Studio. – Aaron Bertrand Sep 11 at 20:43
5

Probably easiest to generate the dates between start and stop:

with t (d) as ( 
    select cast('2017-02-10' as date) 
    union all 
    select dateadd(day, 1, d) from t where d < '2017-03-15' 
) 
select month(d), count(1) from t group by month(d);

I think pivoting is better to do in the application layer, but you can do that if you insist:

with t (d) as ( 
    select cast('2017-02-10' as date) 
    union all 
    select dateadd(day, 1, d) from t where d < '2017-03-15' 
) 
select count(case when month(d) = 1 then 1 end)
     , count(case when month(d) = 2 then 1 end) 
     , count(case when month(d) = 3 then 1 end) 
 ...
from t;

EDIT: In the edited post there appear to be several intervals. You may consider creating a function for the date range. Example:

CREATE FUNCTION date_range(@start date, @stop date)
RETURNS TABLE AS
  RETURN
  with t (d) as ( 
    select cast(@start as date) 
    union all 
    select dateadd(day, 1, d) from t where d < @stop 
  ) 
  select d from t;

with t (x,y) as ( 
  select '2017-02-10', '2017-03-15'
  union all
  select '2017-05-01', '2017-06-21'
)
select month(dr.d), count(1) 
from t 
cross apply date_range(t.x, t.y) dr
group by month(dr.d); 

If you want to count within each interval:

with t (x,y) as ( 
  select '2017-02-10', '2017-03-15'
  union all
  select '2017-05-01', '2017-06-21'
)
select t.x, t.y, month(dr.d) as month, count(1) as cnt
from t 
cross apply date_range(t.x, t.y) dr
group by t.x, t.y, month(dr.d); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.