I have two parameters:

@startDate date = N'2022-01-17'
@endDate date = N'2022-02-28'

And a table with data (not related to parameters)

The task is to calculate how many days of my parameters happens in every month between table dates, and group them by months of parameters ((jan,february))

Output should be something like this:

| JobNr  | Month      | How many days  |
|:-------|:----------:| --------------:|
| nr324  | January    |3               |              
| nr324  | February   |0               |
| nr326  | January    |0               |             
| nr326  | February   |0               |

** 3 is because 17.01,18.01,19.01 are in selected period for nr324 job others are zero, because in selected period of @startDate date = N'2022-01-17' @endDate date = N'2022-02-28' nothing happened.**

I can't quite get it around my head. I understand that somewhere in there should be I think time diff and maybe window function.

CodePudding user response：

declare @a table (
   jobnr     VARCHAR(6) NOT NULL 
  ,startdate DATE  NOT NULL
  ,duedate   DATE  NOT NULL
);
INSERT INTO @a(jobnr,startdate,duedate) VALUES 
('nr324','2022-01-09','2022-01-19'),
('nr326','2022-04-09','2022-05-13');

use join and union and format as follows

DECLARE @startDate DATE = N'2022-01-17' --yourvariable
DECLARE @endDate DATE = N'2022-02-28' -- yourvariable

SELECT a.month1,
       Count(b.month1) AS 'How many days'
FROM   (SELECT Format(@startDate, 'MMMM') month1---month as name
        UNION
        SELECT Format(@endDate, 'MMMM') month1) a
       LEFT JOIN (SELECT jobnr,
                         startdate,
                         Format(startdate, 'MMMM') month1
                  FROM   @a --your table
                  UNION
                  SELECT jobnr,
                         duedate,
                         Format(duedate, 'MMMM') month1
                  FROM   @a) b
              ON a.month1 = b.month1
GROUP  BY b.month1,
          a.month1  

CodePudding user response：

You are on the right track with the months CTE, but you need to be careful of end conditions such as for the range 2022-01-31 to 2022-02-01. From there you can CROSS JOIN the calendar with your job data to calculate the number of overlapping dates between the job, month, and overall range.

SQL Server doesn't have a LEAST() or GREATEST() function to operate on discrete values, so another method is needed, For two values, a simple CASE statement may be used, but for comparing 3 or more values, that logic gets increasingly complex. A trick to keep things relatively simple is to use the MIN() and MAX() aggregate functions operating on a set of data defined using VALUES syntax.

From there, you can calculate the number of days, with care to avoid negative ranges.

The end result is something like:

;WITH Months (Date) AS (
    SELECT DATEADD(DAY, 1 - DAY(@startdate), @startdate) -- First-of-month
    UNION ALL
    SELECT DATEADD(month, 1, Date)
    from months
    where DATEADD(month, 1, Date) <= @enddate -- Inclusive
)
SELECT
    JobNr = D.jobnr,
    Month = DATENAME(month, M.Date),
    [How Many Days] = CASE
        WHEN R.RangeStart <= R.RangeEnd
        THEN 1   DATEDIFF(DAY, R.RangeStart, R.RangeEnd)
        ELSE 0
        END
FROM Months M
CROSS JOIN @Data D
OUTER APPLY(
    SELECT RangeStart = MAX(StartDate), RangeEnd = MIN(EndDate)
    FROM (
        VALUES
            (@startDate, @endDate),
            (M.Date, EOMONTH(m.Date)),
            (D.startdate, D.duedate)
    ) A(StartDate, EndDate)
) R
ORDER BY D.jobnr, M.Date

End dates (@endDate and duedate) are assumed to all be inclusive. The above logic will also work for ranges spanning multiple years, you would likely want to add YEAR(M.Date) to the results.

See this db<>fiddle for a demo.