Assistance with this query request please .

Question

Hello,
I have gotten a request to capture the count of Orders based on what status they were as of the end of the month but without explicitly giving the end date. If the status is Cancel, it is the end of the life cycle so, it shouldnt be counted for next month onwards, ex: A2345 is counted as Cancel in Feb only and no more. A1234 is counted as Shipped in Jan, Feb, march etc and so on, until the next status change.

For ex:
The raw data is

The Goal is to get the below.

Any recommendation on how to resolve this please via T-sql (preferably) or stored proc?

Answer 1

Hi @sam nick ,

Welcome to Microsoft Q&A!

Please also refer below:

;with cte as (  
select a.id,a.status,EOMONTH(a.date) eom   
from mytable a  
inner join   
(select id,EOMONTH(date) eom,max(date) max from mytable  
group by id,EOMONTH(date)) b   
on a.ID=b.ID and a.Date=b.max and EOMONTH(a.date)=eom)  
,cancel as   
(select a.id,a.status,EOMONTH(a.date) eom   
from mytable a  
inner join   
(select id,max(date) max from mytable  
group by id,EOMONTH(date)) b   
on a.ID=b.ID and a.Date=b.max  
where status='cancel' )  
,cte1 as (  
select c.* from (  
select a.id,a.Status,b.eom from cte a  
cross apply (select distinct eom from cte) b) c  
left join cte d on c.ID=d.ID and c.status=d.status   
where c.eom>=d.eom  and   
not exists (select 1 from cancel e where c.ID=e.id    
and (c.eom>e.eom or (c.status<>'cancel' and c.eom=e.eom) )))  
select count(d.id) Statuscount,c.status,c.EOM  from   
(select distinct a.Status,b.EOM from mytable a  
cross apply (select distinct EOMONTH(date) EOM from mytable) b) c  
left join cte1 d on c.EOM=d.eom and c.Status=d.Status  
group by c.status,c.EOM  
order by c.eom,c.status  

Output:

Statuscount	status	EOM  
0	Cancel	2021-01-31  
0	Complete	2021-01-31  
1	Pending	2021-01-31  
1	Shipped	2021-01-31  
1	Cancel	2021-02-28  
0	Complete	2021-02-28  
0	Pending	2021-02-28  
1	Shipped	2021-02-28  
0	Cancel	2021-03-31  
1	Complete	2021-03-31  
0	Pending	2021-03-31  
1	Shipped	2021-03-31  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 2

Try one of possibilities:

;
with I as
(
    select ID, [Status], datefromparts(year([Date]), month([Date]), 1) as D
    from MyTable
),
Q as
(
    select min(D) D, max(D) maxD
    from I
    union all
    select dateadd(month, 1, D) as D, maxD
    from Q
    where dateadd(month, 1, D) <= maxD
),
C as
(
    select *,
        ( select count(*) from I where [Status] = 'Pending' and D <= Q.D 
            and not exists (select * from I i2 where ID=I.ID and D <= Q.D and [Status] in ('Shipped', 'Complete', 'Cancel')) ) as Pending,
        ( select count(*) from I where [Status] = 'Shipped' and D <= Q.D 
            and not exists (select * from I i2 where ID=I.ID and D <= Q.D and [Status] in ('Complete', 'Cancel')) ) as Shipped,
        ( select count(*) from I where [Status] = 'Complete' and D = Q.D) as Complete,
        ( select count(*) from I where [Status] = 'Cancel' and D = Q.D) as Cancel
    from Q
)
select StatusCount, eomonth(D) as [Date], [Status]
from C
unpivot
(
    StatusCount for [Status] in ([Pending], [Shipped], [Complete], [Cancel])
) u
order by D, charindex([Status], 'Pending Shipped Cancel Complete')
option (maxrecursion 0)