Sum date between two date and group by month I have a table as below:
Name  Start Date  End Date
Joe   20/04/2021  20/05/2021
John  01/05/2021  28/05/2021

I am using a SQL table-valued function to return a table that has 2 columns: Month and Count total Date of this month.
Example:

Joe: 10 days in Apr, 20 days in May
John: 28 days in May

Finally I will return a new table




Month
Count




4
10


5
48




I tried to use datediff and datepart to group by month, but don't know how to sum after group. Is there any way to do this?
Besides, I want to add filter from date and to date.
Another approach:
CREATE TABLE dbo.DateRanges
(
    [Name] varchar(20) NOT NULL,
    [Start Date] date NOT NULL,
    [End Date] date NOT NULL,

    PRIMARY KEY ([Name], [Start Date]),
    CHECK ([End Date] &gt;= [Start Date])
);

INSERT dbo.DateRanges
    ([Name], [Start Date], [End Date])
VALUES
    (CONVERT(varchar(20), 'Joe'), CONVERT(date, '20/04/2021', 103), CONVERT(date, '20/05/2021', 103)),
    (CONVERT(varchar(20), 'John'), CONVERT(date, '01/05/2021', 103), CONVERT(date, '28/05/2021', 103));

Solution:
SELECT
    [Month] = Months.m,
    [Count] =
        SUM
        (
            -- Number of days in the current month
            1 + DATEDIFF
            (
                DAY, 
                -- Latest of [Start Date] and current month start date
                IIF(DR.[Start Date] &lt;= MonthRange.StartDate, MonthRange.StartDate, DR.[Start Date]),
                -- Earliest of [End Date] and current month end date 
                IIF(DR.[End Date] &gt;= MonthRange.EndDate, MonthRange.EndDate, DR.[End Date])
            )
        )
FROM dbo.DateRanges AS DR
JOIN
(
    -- Month numbers
    VALUES
        (01), (02), (03), (04), (05), (06),
        (07), (08), (09), (10), (11), (12)
) AS Months (m)
    -- Months that overlap the [Start Date], [End Date] range
    ON Months.m BETWEEN MONTH(DR.[Start Date]) AND MONTH(DR.[End Date])
    OR Months.m BETWEEN MONTH(DR.[End Date]) AND MONTH(DR.[Start Date])
CROSS APPLY
(
    -- Start and end day of each month
    VALUES
        (
            DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1),
            EOMONTH(DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1))
        )
) AS MonthRange (StartDate, EndDate)
GROUP BY
    Months.m
ORDER BY
    Months.m;

Output:




Month
Count




4
11


5
48




Online demo:
db&lt;&gt;fiddle demo