Скрипт T-SQL для подсчета дней

Я создал таблицу, которая имеет эту строку данных:

WorkerID  StartDate  EndDate
========  =========  ==========
0001      2019/11/1  2019/11/2

Когда я пишу другой день для работника 0001 в ноябре / 2019, это довольно легко

SELECT WorkerID, [WorkingDay]= DATEDIFF(day, [EndDate], [StartDate]) 
where [StartDate ] >= 񟭓-11-1' and EndDate <=񟭓-11-30'

однако, если запись о том, что

WorkerID  StartDate   EndDate
========  =========   ==========
0001      2019/11/29  2019/12/3

Я хочу, чтобы таблица результатов, как показано ниже

WorkerID  Workday     WorkFirstDayOfMonth
========  =========   ===================
0001      2            2019/11/1
0001      3            2019/12/1

Могу ли я узнать, как написать запрос T-SQL? Соответствующее условие where

where [StartDate ] >= 񟭓-11-1' and EndDate <=񟭓-12-31'

sql-server tsql

Всего 1 ответ

Я думаю, что вы, вероятно, хотели бы фактическую таблицу календаря, чтобы вы могли учитывать выходные и праздничные дни. Но это вернет результаты, которые вы ищете. Я сделал это достаточно гибким, чтобы работать в течение нескольких месяцев. Я использую cte в качестве таблицы подсчета, которая делает недолгой работу такого рода вещей. Обратите внимание, что я добавил вторую строку для WorkerID xxxx с несколькими месяцами.

declare @Something table
(
    WorkerID char(4)
    , StartDate date
    , EndDate date
)

insert @Something values
(񟍱', �', �')
, ('xxxx', �', �')
;

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a cross join E1 b), 
    InlineTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    )

select WorkerID
    , StartDate
    , EndDate
    , WorkDay = case when t.N = 1 --the first month
                    then datediff(day, StartDate, dateadd(month, datediff(month, 0, StartDate) + 1, 0)) 
                when datepart(month, dateadd(month, t.N - 1, StartDate)) < datepart(month, EndDate) --not the first OR the last month
                    then datediff(day, dateadd(month, datediff(month, t.N - 1, dateadd(month, t.N - 1, StartDate)), 0), dateadd(month, datediff(month, 0, dateadd(month, t.N - 1, StartDate)) + 1, 0))
                else --the last month
                    datediff(day, dateadd(month, datediff(month, 0, EndDate), 0), EndDate) + 1  
                end
    , FirstDayOfMonth = convert(date, dateadd(month, datediff(month, t.N - 1, dateadd(month, t.N - 1, StartDate)), 0))
from @Something s
join InlineTally t on t.N <= datediff(month, StartDate, EndDate) + 1
order by s.WorkerID
    , t.N

Возвращает:

WorkerID | StartDate  | EndDate    | WorkDay | FirstDayOfMonth
0001     | 2019-11-29 | 2019-12-03 |2        | 2019-11-01
0001     | 2019-11-29 | 2019-12-03 |3        | 2019-12-01
xxxx     | 2019-09-29 | 2019-12-03 |2        | 2019-09-01
xxxx     | 2019-09-29 | 2019-12-03 |31       | 2019-10-01
xxxx     | 2019-09-29 | 2019-12-03 |30       | 2019-11-01
xxxx     | 2019-09-29 | 2019-12-03 |3        | 2019-12-01