I try to figure out the best approach to report the interval from a dataset when a box got empty. I have a stored procedure which give me the latest inventory (quantity) and after this inventory I got the table named export in which I can see movements on the box later on, at a point the box will be emptied and I want to know the start date and the end date till the box is empty.
CREATE TABLE [dbo].[export]
(
[idbox] [int] NULL,
[actualquantity] [int] NULL,
[actiondate] [datetime2](7) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 0, CAST(N'2022-06-01T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 0, CAST(N'2022-07-01T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, -1, CAST(N'2022-07-19T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, -6, CAST(N'2022-07-17T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, -14, CAST(N'2022-07-24T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 0, CAST(N'2022-08-01T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, -5, CAST(N'2022-08-02T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, -9, CAST(N'2022-08-15T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 34, CAST(N'2022-08-24T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, -34, CAST(N'2022-08-24T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 34, CAST(N'2022-08-24T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 0, CAST(N'2022-09-01T00:00:00.0000000' AS DateTime2))
GO
INSERT [dbo].[export] ([idbox], [actualquantity], [actiondate])
VALUES (52473, 0, CAST(N'2022-10-01T00:00:00.0000000' AS DateTime2))
GO
In this dataset the inventory is 35 pieces and in the attached data the box will be empty between
2022-08-15 00:00:00.0000000
and
2022-08-24 00:00:00.0000000
The results about boxes I am carrying out using CURSOR on the existing boxes (existing boxes are returned from a stored procedure with the initial quantity from the latest inventory).
What approach should I use to loop on the results, should I use another CURSOR with some local variable in which I keep an eye on the box quantity while I am looping on the results?
CodePudding user response：
My initial approach to this would be to have a 'current quantity' column in your table; but if you don't have that, you can approach it via calculations.
The key tools here are
- The SUM function as an analytic function - by using the OVER clause
- The LEAD function which allows you to get a value from the next row of data
Note that in my dataset, I have made two tweaks on what you have above
- Added a PRIMARY KEY, auto-incrementing field called
Auto_ID - Added an initial row (on 1/1/2022) with
actualquantity= 35 for this box (I'm assuming that the box, at some point, had to have 35 items put into it to start)
You can follow the logic at this db_fiddle.
Using SUM to get a running total
If you add an OVER clause with an ORDER BY to a SUM function, you make it have a running total - instead of just reporting the total for all rows.
The key purpose for calculating this running total is to identify when the box is empty - when the running total = 0.
The SQL for this is pretty simple - the PARTITION component says to do the totals within each box only (similar to a GROUP BY) and the ORDER BY is critical to how the running total is carried out.
SELECT *,
SUM(actualquantity) OVER (PARTITION BY idbox ORDER BY actiondate, Auto_ID) AS currentquantity
FROM dbo.export;
Using LEAD to get the next date
This part allows you to get the next actiondate onto the current line. The purpose of this is that you can then do a DATEDIFF on these two dates to get the time between this actiondate and the next.
Note the OVER clause is the same as the SUM above.
SELECT *,
SUM(actualquantity) OVER (PARTITION BY idbox ORDER BY actiondate, Auto_ID) AS currentquantity,
LEAD(actiondate, 1) OVER (PARTITION BY idbox ORDER BY actiondate, Auto_ID) AS next_actiondate
FROM dbo.export;
Using the above, the current results would be
Auto_ID idbox actualquantity actiondate currentquantity next_actiondate
----------- ----------- -------------- --------------------------- --------------- ---------------------------
1 52473 35 2022-01-01 00:00:00.0000000 35 2022-06-01 00:00:00.0000000
2 52473 0 2022-06-01 00:00:00.0000000 35 2022-07-01 00:00:00.0000000
3 52473 0 2022-07-01 00:00:00.0000000 35 2022-07-17 00:00:00.0000000
5 52473 -6 2022-07-17 00:00:00.0000000 29 2022-07-19 00:00:00.0000000
4 52473 -1 2022-07-19 00:00:00.0000000 28 2022-07-24 00:00:00.0000000
6 52473 -14 2022-07-24 00:00:00.0000000 14 2022-08-01 00:00:00.0000000
7 52473 0 2022-08-01 00:00:00.0000000 14 2022-08-02 00:00:00.0000000
8 52473 -5 2022-08-02 00:00:00.0000000 9 2022-08-15 00:00:00.0000000
9 52473 -9 2022-08-15 00:00:00.0000000 0 2022-08-24 00:00:00.0000000
10 52473 34 2022-08-24 00:00:00.0000000 34 2022-08-24 00:00:00.0000000
11 52473 -34 2022-08-24 00:00:00.0000000 0 2022-08-24 00:00:00.0000000
12 52473 34 2022-08-24 00:00:00.0000000 34 2022-09-01 00:00:00.0000000
13 52473 0 2022-09-01 00:00:00.0000000 34 2022-10-01 00:00:00.0000000
14 52473 0 2022-10-01 00:00:00.0000000 34 NULL
Calculating number of days empty
Using the above, we can calculate the number of days empty simply - when the currentquantity is 0, determine the number of days between this and the next actiondate (or today if it's still empty).
I've used a CTE which has the above SQL to get the relevant data, then reported the specific results.
WITH RunningTotals AS
(SELECT *,
SUM(actualquantity) OVER (PARTITION BY idbox ORDER BY actiondate, Auto_ID) AS currentquantity,
LEAD(actiondate, 1) OVER (PARTITION BY idbox ORDER BY actiondate, Auto_ID) AS next_actiondate
FROM dbo.export
)
SELECT idbox, actualquantity, actiondate,
CASE WHEN currentquantity = 0
THEN DATEDIFF(day, actiondate, ISNULL(next_actiondate, getdate()))
ELSE NULL END AS emptydelay
FROM RunningTotals
ORDER BY Auto_id;
Here are the results from the above - with the column 'emptydelay' representing the number of days the box was empty.
idbox actualquantity actiondate emptydelay
----------- -------------- --------------------------- -----------
52473 35 2022-01-01 00:00:00.0000000 NULL
52473 0 2022-06-01 00:00:00.0000000 NULL
52473 0 2022-07-01 00:00:00.0000000 NULL
52473 -1 2022-07-19 00:00:00.0000000 NULL
52473 -6 2022-07-17 00:00:00.0000000 NULL
52473 -14 2022-07-24 00:00:00.0000000 NULL
52473 0 2022-08-01 00:00:00.0000000 NULL
52473 -5 2022-08-02 00:00:00.0000000 NULL
52473 -9 2022-08-15 00:00:00.0000000 9
52473 34 2022-08-24 00:00:00.0000000 NULL
52473 -34 2022-08-24 00:00:00.0000000 0
52473 34 2022-08-24 00:00:00.0000000 NULL
52473 0 2022-09-01 00:00:00.0000000 NULL
52473 0 2022-10-01 00:00:00.0000000 NULL