How to get rolling counts with missing data in few months

Question

Hello,
Below is a sample data set i have...

My goal is to get the rolling count of shipped per month. The issue is with those where there are no shipments in a month. I have tried using partition clause, but no luck. I have to display the data for every single month irrespective on shipment or not. and the end result should be
164119-image.png

Please can you help me on how to achieve this.

Below is the code and explanation

My apologies. Below is the code.

create table sampletable1 (keyid int not null,  
datefield date,  
OrderiD nvarchar(10) ,  
Ord_Status nvarchar(20)  
)  

insert into Sampletable1 values ('1','01/01/2021','ORD_123','Pending');  
insert into Sampletable1 values ('2','01/15/2021','ORD_123','Shipped');  
insert into Sampletable1 values ('3','01/20/2021','ORD_123','Completed');  
insert into Sampletable1 values ('4','02/04/2021','ORD_234','Shipped');  
insert into Sampletable1 values ('5','03/04/2021','ORD_345','Shipped');  
insert into Sampletable1 values ('6','03/10/2021','ORD_345','Completed');  
insert into Sampletable1 values ('7','04/01/2021','ORD_345','Returned');  
insert into Sampletable1 values ('8','04/10/2021','ORD_345','Cancelled');  
insert into Sampletable1 values ('9','01/05/2021','ORD_456','Pending');  
insert into Sampletable1 values ('10','01/10/2021','ORD_456','Shipped');  
insert into Sampletable1 values ('11','01/20/2021','ORD_456','Completed');  
insert into Sampletable1 values ('12','04/05/2021','ORD_567','Shipped');  
insert into Sampletable1 values ('13','04/10/2021','ORD_678','Cancelled');  
insert into Sampletable1 values ('14','03/01/2021','ORD_789','Completed');  
insert into Sampletable1 values ('15','07/01/2021','ORD_890','Pending');  
insert into Sampletable1 values ('16','07/04/2021','ORD_890','Shipped');  
insert into Sampletable1 values ('17','07/25/2021','ORD_890','Completed');  

So for the final result,

• By end of Jan : 2 were shipped
• By end of Feb - 1 was shipped but NET 3 were shipped (including previous month)
• By end of Mar - 1 was shipped but NET 4 were shipped (including previous month)
• By end of Apr - 1 was shipped but NET 5 were shipped (including previous month)
• By end of May - none were shipped but NET 5 were shipped (including previous month)
• By end of Jun- none were shipped but NET 5 were shipped (including previous month)
• By end of Jul- 1 was shipped but NET 6 were shipped (including previous month)

Answer 1

Try a not quite elegant query:

;
with 
M as ( select * from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12) ) M(m) ),
L as ( select year(datefield) as y, min(datefield) as d1, max(datefield) as d2 from sampletable1 group by year(datefield))
select *,
    eomonth(datefromparts(L.y, M.m, 1)) as EOM,
    ( select count(*) from sampletable1 where Ord_Status = 'Shipped' and year(datefield) = L.y and month(datefield) <= M.m) as ShipCount
from L, M
where M.m between month(L.d1) and month(L.d2)
order by EOM

Answer 2

Hello @sam nick

--=======================================================================================
create table sampletable1 (keyid int not null,
datefield date,
OrderiD nvarchar(10) ,
Ord_Status nvarchar(20)
)
insert into Sampletable1 values ('1','01/01/2021','ORD_123','Pending');
insert into Sampletable1 values ('2','01/15/2021','ORD_123','Shipped');
insert into Sampletable1 values ('3','01/20/2021','ORD_123','Completed');
insert into Sampletable1 values ('4','02/04/2021','ORD_234','Shipped');
insert into Sampletable1 values ('5','03/04/2021','ORD_345','Shipped');
insert into Sampletable1 values ('6','03/10/2021','ORD_345','Completed');
insert into Sampletable1 values ('7','04/01/2021','ORD_345','Returned');
insert into Sampletable1 values ('8','04/10/2021','ORD_345','Cancelled');
insert into Sampletable1 values ('9','01/05/2021','ORD_456','Pending');
insert into Sampletable1 values ('10','01/10/2021','ORD_456','Shipped');
insert into Sampletable1 values ('11','01/20/2021','ORD_456','Completed');
insert into Sampletable1 values ('12','04/05/2021','ORD_567','Shipped');
insert into Sampletable1 values ('13','04/10/2021','ORD_678','Cancelled');
insert into Sampletable1 values ('14','03/01/2021','ORD_789','Completed');
insert into Sampletable1 values ('15','07/01/2021','ORD_890','Pending');
insert into Sampletable1 values ('16','07/04/2021','ORD_890','Shipped');
insert into Sampletable1 values ('17','07/25/2021','ORD_890','Completed');
--=======================================================================================

Please use the below query:

DECLARE @Ship _start DATETIME = '2021-01-31',
@Ship _end DATETIME = '2021-08-01';

WITH cteMonth AS
( SELECT @Ship _start AS dt
UNION ALL
SELECT DATEADD(m, 1, dt) FROM cteMonth WHERE DATEADD(m, 1, dt) < DATEADD(m, 1, @Ship _end)
),
cteAllEOMonth AS (SELECT EOMONTH(dt) AS EOM FROM cteMonth WHERE dt <= EOMONTH(@Ship _end)),
cteShipped AS (SELECT EOMONTH(datefield) AS EOM FROM Sampletable1 WHERE Ord_Status = 'Shipped')
SELECT DISTINCT M.EOM, COUNT (S.EOM) OVER (ORDER BY M.EOM) AS RunningTotal
FROM cteAllEOMonth M LEFT JOIN cteShipped S ON M.EOM = S.EOM
ORDER BY 1

----------

As per the desired result-set in the image the first record presentation seems incorrect as you have mentioned that on 01-Jan-2021 (Start Of Month) you have 2 shipment, where-as for rest of the record you consider the End Of Month.

So i consider it is a typing mistake, hence the above query will give you result for End Of Month for the first record also. In case if if this is not a typing mistake, please let me know.

Answer 3

Hi,

For the sake of the solution I will create an accessory table with all dates. This should improve performance when you need big amount of rows.

-- Accessory table : calendar table
CREATE TABLE Dates (DT DATE)
GO
INSERT Dates(DT) 
    SELECT TOP 1000 EOMONTH(DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), CONVERT(DATE,'2010-01-31',20)))
    FROM sys.all_objects t1 CROSS JOIN sys.all_objects t2
GO
CREATE CLUSTERED INDEX IX_Dates_DT
    ON dbo.Dates (DT);   
GO 

Using the accessory table here is a simple and flexible solution

DECLARE @Start DATE, @End DATE
SELECT @Start = CONVERT(DATE,'2021-01-31',20), @End = CONVERT(DATE,'2021-08-31',20) -- fill this according to the range you need
;With MyCTE01 AS (
    SELECT DT FROM Dates
    WHERE DT BETWEEN @Start AND @End
),
MyCTE02 AS (
    select EOM = EOMONTH(datefield), ShipCount = COUNT(*)
    from Sampletable1
    where Ord_Status = 'Shipped'
    GROUP BY EOMONTH(datefield)
)
SELECT t1.DT,SUM(t2.ShipCount) OVER(ORDER BY t1.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM MyCTE01 t1
LEFT JOIN MyCTE02 t2 ON t1.DT = t2.EOM
ORDER BY DT
GO

Answer 4

Hi,@sam nick
The key point of this issue is to fill in the EOM of missing months.
You can get last day of consecutive months using recursive CTE,which is shown in Govind's answer.
Or as pituach suggested,create an accessory table with all dates,and then left join the data table.
Check this:

;WITH CTE1 as  
(SELECT EOMONTH(datefield) AS EOM,1 AS qty   
 FROM Sampletable1  
 WHERE Ord_Status = 'Shipped'  
),CTE2 AS  
(SELECT A.EndDate EOM, SUM(B.QTY) tocount  
 FROM Dates A LEFT JOIN CTE1 B ON  B.EOM >= A.EndDate  AND B.EOM < Dateadd(m,1,A.EndDate )  
 WHERE A.EndDate >= '20210101' AND A.EndDate <= '20210831'  
 GROUP BY A.EndDate  
)  
SELECT EOM,SUM(tocount)OVER(ORDER BY EOM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS shipcount  
FROM CTE2  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.