SQL Count open orders every day between two dates

Question

I tried searching, but I probably use the wrong keywords, because I can not find the answer.

I am trying to find the number of orders open between two dates and an employee. I have one table that shows a list of employees, another that shows a list of orders that contains an opening and closing date, and also a date table if that helps.

The linked employee and order tables return something like:

employee    order ref   opened          closed
a           123         01/01/2012      04/01/2012
b           124         02/01/2012      03/01/2012
a           125         02/01/2012      03/01/2012

And I need to convert this data to:

Date            employee    Count
01/01/2012      a           1
02/01/2012      a           2
02/01/2012      b           1
03/01/2012      a           2
03/01/2012      b           1
04/01/2012      a           1

I am retrieving data from an SQL server.

Any ideas?

thank

Nick

Answer 1

Dates Employees Orders, , , :

SELECT
  d.Date,
  o.Employee,
  COUNT(*) AS count
FROM Employees e
  INNER JOIN Orders o ON e.ID = o.Employee
  INNER JOIN Dates d ON d.Date BETWEEN o.Opened AND o.Closed
GROUP BY
  d.Date,
  o.Employee

Answer 2

SELECT opened,employee,count(*)
FROM employee LEFT JOIN orders
WHERE opened < firstDate and opened > secondDate
GROUP BY opened,employee

WHERE opened BETWEEN firstDate and secondDate

Answer 3

, , -, . , ROW_NUMBER.

, . UNION .

WITH cte 
     AS (SELECT Row_number() OVER ( PARTITION BY employee  
                                    ORDER BY order_ref) count, 
                employee, 
                opened, 
                closed 
         FROM   orders) 
SELECT employee,  opened date,  count 
FROM   cte 
UNION ALL 
SELECT employee,  closed date,  count 
FROM   cte 
ORDER  BY Date, 
          employee 

DEMO

Answer 4

.

with cumopens as
    (select employee, opened as thedate,
            row_number() over (partition by employee order by opened) as cumopens,
            0 as cumcloses
     from eo
    ),
     cumcloses as
    (select employee, closed as thedate, 0 as cumopens,
            row_number() over (partition by employee order by closed ) as cumcloses
     from eo
    )
select employee, c.thedate, max(cumopens), max(cumcloses),
       max(cumopens) - max(cumcloses) as stillopened
from ((select *
       from cumopens
      ) union all
      (select *
       from cumcloses
      )
     ) c
group by employee, thedate

The only problem with this approach is that only the dates when there is employee activity are reported. This works in your case.

For a more general solution, serial numbers are required to create dates. To do this, I often create one from an existing table with enough rows:

with nums as
    (select row_number() over (partition by null order by null) as seqnum
     from employees
    )
select employee, dateadd(day, opened, seqnum) as thedate, count(*)
from eo join
     nums
     on datediff(day, opened, closed) < seqnum
group by employee, dateadd(day, opened, seqnum)
order by 1, 2