Ежедневное количество записей на основе распределения статуса

Я вижу два возможных решения.

Динамическое решение

Используйте (рекурсивное) общее табличное выражение для создания списка всех дат, попадающих в запрошенный диапазон.

Используйте два оператора cross apply, каждый из которых выполняет функцию агрегирования count(), чтобы подсчитать количество переводов книг.

-- generate date range
with Dates as
(
  select convert(date, '2022-11-01') as TransferDate
    union all
  select dateadd(day, 1, d.TransferDate)
  from Dates d
  where d.TransferDate < '2022-11-10'
)
select  d.TransferDate,
        c1.CountRented,
        c2.CountReturned
from Dates d
-- count all rented books up till today, that have not been returned before today
cross apply ( select count(1) as CountRented
              from Transfer t1
              where t1.BookStatus = 'Rented'
                and t1.TransferDate <= d.TransferDate
                and not exists ( select 'x'
                                 from Transfer t2
                                 where t2.BookId = t1.BookId
                                   and t2.BookStatus = 'Returned'
                                   and t2.TransferDate > t1.TransferDate
                                   and t2.TransferDate <= d.TransferDate ) ) c1
-- count all returned books for today
cross apply ( select count(1) as CountReturned
              from Transfer t1
              where t1.BookStatus = 'Returned'
                and t1.TransferDate = d.TransferDate ) c2;

Результат:

TransferDate  CountRented  CountReturned
------------  -----------  -------------
2022-11-01    1            0
2022-11-02    1            0
2022-11-03    2            0
2022-11-04    2            0
2022-11-05    1            1
2022-11-06    2            0
2022-11-07    2            0
2022-11-08    2            0
2022-11-09    0            2
2022-11-10    0            0

Этот результат не является сводным результатом, описанным в вопросе. Однако для поворота этого динамического решения требуется динамический SQL, что не так уж и просто!

Статическое решение

Это даст точный результат, как описано в вопросе (включая форматирование даты), но требует, чтобы диапазон дат был полностью введен один раз.

Основные строительные блоки аналогичны динамическому решению выше:

• Рекурсивное общее табличное выражение для генерации диапазона дат.
• Два cross apply для выполнения счетных вычислений, как раньше.

Есть также:

• Дополнительный cross join для дублирования диапазона дат для каждого BookStatus (избегайте значений NULL в результате).

• Некоторые функции replace(), str() и datepart() форматируют даты.

• Выражение case для объединения двух счетчиков в один столбец.

Решение, вероятно, не самое производительное, но оно обеспечивает требуемый результат. Если вы хотите проверить BookID=1, просто раскомментируйте лишние пункты фильтра WHERE.

with Dates as
(
    select convert(date, '2022-11-01') as TransferDate
        union all
    select dateadd(day, 1, d.TransferDate)
    from Dates d
    where d.TransferDate < '2022-11-10'
),
PivotInput as
(
    select  replace(str(datepart(day, d.TransferDate), 2),  space(1), '0') + '.' + replace(str(datepart(month, d.TransferDate), 2),  space(1), '0') as TransferDate,
            s.BookStatus as [Status],
            case when s.BookStatus = 'Rented' then sc1.CountRented else sc2.CountReturned end as BookStatusCount
    from Dates d
    cross join (values('Rented'), ('Returned')) s(BookStatus)
    cross apply (   select count(1) as CountRented
                    from Transfer t1
                    where t1.BookStatus = s.BookStatus
                      and t1.TransferDate <= d.TransferDate
                      --and t1.BookID = 1
                      and not exists (  select 'x'
                                        from Transfer t2
                                        where t2.BookId = t1.BookId
                                          and t2.BookStatus = 'Returned'
                                          and t2.TransferDate > t1.TransferDate
                                          and t2.TransferDate <= d.TransferDate ) ) sc1
    cross apply (   select count(1) as CountReturned
                    from Transfer t3
                    where t3.TransferDate = d.TransferDate
                      --and t3.BookID = 1
                      and t3.BookStatus = 'Returned' ) sc2
)
select  piv.*
from PivotInput pivi
pivot (sum(pivi.BookStatusCount) for pivi.TransferDate in (
[01.11],
[02.11],
[03.11],
[04.11],
[05.11],
[06.11],
[07.11],
[08.11],
[09.11],
[10.11])) piv;

Результат:

Status    01.11  02.11  03.11  04.11  05.11  06.11  07.11  08.11  09.11  10.11
Rented    1      1      2      2      1      2      2      2      0      0
Returned  0      0      0      0      1      0      0      0      2      0

Fiddle, чтобы увидеть вещи в действии.