CODEWITHSUNDEEP
mysqldatetime
Mr Wednesday
Mr Wednesday

Reputation: 552

Count of open orders per week

I'm trying to find the number of orders that were open for each week. An order that is open for multiple weeks should be included in each week's count that it was open. The data looks something like below

id  open_dt               close_dt
1   2014-01-01 07:00:00   2014-01-01 07:00:00 
2   2014-01-01 07:00:00   2014-01-02 07:00:00 
3   2014-01-02 07:00:00   2014-01-09 07:00:00 
4   2014-01-08 07:00:00   NULL

NULL close_dt counts as still open and should appear in each week since it was opened

My query looks like below however it isn't returning the numbers I'm expecting:

SELECT YEAR(open_dt) AS year, WEEK(open_dt) AS week, count(*) 'open'
FROM table
WHERE open_dt >= week(open_dt)
OR 
(
    close_dt > week(open_dt)
    OR close_dt IS NULL
)
GROUP BY YEAR(open_dt), WEEK(open_dt)

I'm trying to get results like below:

year  week  open
2014  1     3
2014  2     2
2014  3     1
...

Appreciate any tips or guidance.

Upvotes: 1

Views: 253

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

This is a case where it helps to have a calendar table or list of weeks. Let me assume that you have at least one open in each week:

select yw.y, yw.w, count(t.open_dt) as "Open"
from (select distinct year(open_dt) as y, week(open_dt) as w,
             year(open_dt) * 100 + week(open_dt) as yw
      from table t
     ) yw left outer join
     table t
     on yw.yw >= year(open_dt)*100 + week(open_dt) and
        (yw.yw <= year(close_dt)*100 + week(close_dt) or close_dt is null)
group by yw.y, yw.w
order by yw.y, yw.w;

Upvotes: 2

Related Questions