Daily record count based on status allocation

Question

I have a table named Books and a table named Transfer with the following structure:

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');

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');

See fiddle.

I want to do a query for a date interval (in this case 01.11 - 09.11) that returns the book count for each day based on BookStatus from Transfer, like so:

+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| Status     | 01.11  | 02.11  | 03.11  | 04.11  | 05.11  | 06.11  | 07.11  | 08.11  | 09.11  |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| Rented     | 2      | 1      | 2      | 2      | 0      | 2      | 3      | 3      | 1      |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| Returned   | 1      | 2      | 1      | 1      | 3      | 1      | 0      | 0      | 2      |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+

A book remains rented as long as it was not returned, and is counted as 'Returned' every day until it is rented out again.

This is what the query result would look like for one book (BookID 1):

Have you tried anything? Or are you asking us to do it all for you? — Dale K, Nov 18 '22 at 07:50
@DaleK I don't know where to start, this is why I'm asking here. — milo2011, Nov 18 '22 at 07:58
T-SQL gives you the [PIVOT](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16) but there is a catch. The columns that will be expanded should be known in before. To expand to an arbitrary number of columns you should construct the query and execute it with [EXEC](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-2017) or [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017) — Stavros Zotalis, Nov 18 '22 at 08:09
Also see https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Stavros Zotalis, Nov 18 '22 at 08:13
Its takes time to learn SQL and you have to start from the basics and work up. There are many tutorials out there. — Dale K, Nov 18 '22 at 08:54

Sander · Accepted Answer · 2022-11-25T12:52:29.750

I see two possible solutions.

Dynamic solution

Use a (recursive) common table expression to generate a list of all the dates that fall within the requested range.

Use two cross apply statements that each perform a count() aggregation function to count the amount of book transfers.

-- 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;

Result:

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

This result is not the pivoted outcome described in the question. However, pivoting this dynamic solution requires dynamic sql, which is not trivial!

Static solution

This will delivery the exact outcome as described in the question (including the date formatting), but requires the date range to be fully typed out once.

The essential building blocks are similar to the dynamic solution above:

A recursive common table expression to generate a date range.
Two cross apply's to perform the counting calculations like before.

There is also:

An extra cross join to duplicate the date range for each BookStatus (avoid NULL values in the result).
Some replace(), str() and datepart() functions to format the dates.
A case expression to merge the two counts to a single column.

The solution is probably not the most performant, but it does deliver the requested result. If you want to validate for BookID=1 then just uncomment the extra WHERE filter clauses.

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;

Result:

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 to see things in action.

Sorry if I wasn't clear enough. I'll try to elaborate with an example: The book with the BookID 1, entered in status 'Rented' on 2022-11-01 and it stayed in that status until 2022-11-05 when it changed status to 'Returned' in which is stayed until 2022-11-06 when it changed status to 'Rented' and stayed in that status until 2022-11-09 when it changed status to 'Returned'. So, this book should show up as '1' every day it was in status 'Rented' and '1' when it was in status 'Returned'. — milo2011, Nov 21 '22 at 10:24
The query I looking for should return a sum of the statuses by day based on the status every books was alocated each day. — milo2011, Nov 21 '22 at 10:24
I understand what you mean: a book remains rented as long as it was not returned, but what bout the 'Returned' status? Does a book remain counted as 'Returned' every day until it is rented out again? Please [edit the question](https://stackoverflow.com/posts/74486276/edit) so that the expected result matches the sample data (might want to include Nov 10th for extra clarity). — Sander, Nov 22 '22 at 11:31
Yes, a book remain counted as 'Returned' every day until it is rented out again. — milo2011, Nov 23 '22 at 07:25
Thanks, @Sander! One more question. What if I have other statuses besides Rented and Returned ? Other status example: Inactive, Reserved Do I add it to the cross join? — milo2011, Nov 29 '22 at 08:12
Add the extra values to the `cross join` and provide an extra `cross apply` to perform the count aggregation with any required restrictions (if any). [Example fiddle](https://dbfiddle.uk/HLRjNipb?hide=6) — Sander, Nov 30 '22 at 09:53

Daily record count based on status allocation

1 Answers1

Dynamic solution

Static solution