Daily record count based on status allocation
-
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 |
+──────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+- This topic was modified 1 year, 5 months ago by milo1981.
-
Start with a simple aggregation and work from there!
😎
Here is the first part:
SELECT
COUNT(T.BookId)
,T.TransferDate
,T.BookStatus
FROM dbo.Transfer T
GROUP BY T.TransferDate
,T.BookStatus
ORDER BY T.TransferDate ASC;Pivoting the results on dates is elementary, I'll leave that to you to figure out 😉
-
Although the original question wasn't clear, I think what the OP wants to know is the number of books in each status at the end of each day so a simple daily count of the status changes won't work,.
I think you need to conver the statuses to integers and use running totals.
- milo1981 wrote:
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 |
+──────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+Your graphic of the desired results doesn't match the data you provided at all, making it a wee bit difficult to figure out what you actually want. For example, you only have one book with a status of "Rented" on 01.11 in the data and no books have a status of "Returned" on that date. Since 01.11 is the start of your data and of the graphic, the graphic makes no sense as to what you're trying to accomplish.
Please update the graphic with the correct desired output that matches the given data.
It's good that you posted the data creation here because I don't do "Fiddle" because that can go away, which would leave the post "stranded". I'm surprised they allow that on SO with all their "rules".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Taking a SWAG at what's desired, here's the "vertical" rendition using the given data. I don't recommend Pivoting such data anywhere but a spreadsheet and I wouldn't do it there, either. If you're hell bent on doing such a pivot, see the following link for how to convert the results of the code below to a "Dynamic Pivot".
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
Note that this code uses a sequence generation function. You can get the one being used in the follow code at the following URL.
https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
--===== Set the dates for the desired range of dates.
DECLARE @StartDate DATE = '20221101'
,@EndDate DATE = '20221109'
;
--===== Solve the problem "vertically".
WITH
cteDateRange AS
(--==== Create the full range of dates
SELECT TheDate = DATEADD(dd,t.N,@StartDate)
FROM dbo.fnTally(0,DATEDIFF(dd,@StartDate,@EndDate))t
)
,cteDailyTotals AS
(--===== This gets the daily totals for display
SELECT dr.TheDate
,Rented = SUM(IIF(tr.BookStatus = 'Rented' ,1,0)) -- This avoids "Null Ignored" messages
,Returned = SUM(IIF(tr.BookStatus = 'Returned',1,0)) -- This avoids "Null Ignored" messages
,RowType = GROUPING_ID(TheDate)
FROM cteDateRange dr
LEFT JOIN dbo.Transfer tr ON dr.TheDate = tr.TransferDate
GROUP BY dr.TheDate WITH ROLLUP
)
SELECT TheDate = IIF(RowType=0,CONVERT(CHAR(10),TheDate,102),' Total')
,DailyRented = Rented
,DailyReturned = Returned
,TotalOut = SUM(Rented-Returned) OVER (ORDER BY TheDate ROWS UNBOUNDED PRECEDING)
FROM cteDailyTotals
ORDER BY RowType,TheDate
;Using the given data, here are the results that produces...
The code above can return negative numbers if more books that have been "Rented" prior to the start date are "Returned". If you need to account for that, then you'll need to create a "Starting Balance" row for all rows that have occurred prior to the the StartDate. That will also help the "Total Out" reflect the truth on the given dates for all dates rather than just for the given dates.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you all for your replies!
Sorry if I wasn't clear enough. Although the data I populated the Transfer table with spans a whole month, the desired results table I provided spaned only 10 days.
I'll try to elaborate:
As you can see from the results table I added, the daily total for both statuses is always the same, 3 - which is the total number of books.
What I want to achive is a query that returns, based on the Transfer table, the daily statuses sum.
A book remains rented as long as it was not returned, and is counted as 'Returned' every day until it is rented out again.
For 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 stautus 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 the returned data, for this book, should be :
The query I looking for should return a sum of the statuses by day based on the status every books was alocated each day.
Hope I was clear enough.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply