SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Books
(
  BookID int,
  Title varchar(150),
  PurchaseDate date,
  Bookstore varchar(150),
  City varchar(150)
);

INSERT INTO Books VALUES (1, 'Cujo', '2022-02-01', 'CentralPark1', 'New York');
INSERT INTO Books VALUES (2, 'The Hotel New Hampshire', '2022-01-08', 'TheStrip1', 'Las Vegas');
INSERT INTO Books VALUES (3, 'Gorky Park', '2022-05-19', 'CentralPark2', 'New York');

select * from Books; -- table not required for solution

CREATE TABLE Transfer
    (
        BookID int,
        BookStatus varchar(50),
        TransferDate date
    );

INSERT INTO Transfer VALUES (1, 'Rented', '2022-11-01');
INSERT INTO Transfer VALUES (1, 'Returned', '2022-11-05');
INSERT INTO Transfer VALUES (1, 'Rented', '2022-11-06');
INSERT INTO Transfer VALUES (1, 'Returned', '2022-11-09');
INSERT INTO Transfer VALUES (2, 'Rented', '2022-11-03');
INSERT INTO Transfer VALUES (2, 'Returned', '2022-11-09');
INSERT INTO Transfer VALUES (2, 'Rented', '2022-11-15');
INSERT INTO Transfer VALUES (2, 'Returned', '2022-11-23');
INSERT INTO Transfer VALUES (3, 'Rented', '2022-11-14');
INSERT INTO Transfer VALUES (3, 'Returned', '2022-11-21');
INSERT INTO Transfer VALUES (3, 'Rented', '2022-11-25');
INSERT INTO Transfer VALUES (3, 'Returned', '2022-11-29');

select * from Transfer


BookID
Title
PurchaseDate
Bookstore
City


1
Cujo
2022-02-01
CentralPark1
New York

2
The Hotel New Hampshire
2022-01-08
TheStrip1
Las Vegas

3
Gorky Park
2022-05-19
CentralPark2
New York




BookID
BookStatus
TransferDate


1
Rented
2022-11-01

1
Returned
2022-11-05

1
Rented
2022-11-06

1
Returned
2022-11-09

2
Rented
2022-11-03

2
Returned
2022-11-09

2
Rented
2022-11-15

2
Returned
2022-11-23

3
Rented
2022-11-14

3
Returned
2022-11-21

3
Rented
2022-11-25

3
Returned
2022-11-29


-- (dynamic)
-- 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


-- (static)
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

BookID	Title	PurchaseDate	Bookstore	City
1	Cujo	2022-02-01	CentralPark1	New York
2	The Hotel New Hampshire	2022-01-08	TheStrip1	Las Vegas
3	Gorky Park	2022-05-19	CentralPark2	New York

BookID	BookStatus	TransferDate
1	Rented	2022-11-01
1	Returned	2022-11-05
1	Rented	2022-11-06
1	Returned	2022-11-09
2	Rented	2022-11-03
2	Returned	2022-11-09
2	Rented	2022-11-15
2	Returned	2022-11-23
3	Rented	2022-11-14
3	Returned	2022-11-21
3	Rented	2022-11-25
3	Returned	2022-11-29

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