How to calculate number of Orders for given date range

Question

Need some help in writing sql query to capture number of active Orders between date range on month wise grouping. i.e if the user selected 2018-01-01 to 2019-12-31, I have to show number of active orders on a month basis i.e total 12 records.

I'm querying against Order Table whose schema looks like below

OrderID    CustomerFirstName   PurchaseDate   OrderEndDate  
1           XYZ                2018-01-01     9999-12-31
2           ABC                2018-02-02     2018-06-30
3           PQR                2018-06-01     2018-06-30
4           GHI                2018-01-01     2018-02-28

Order EndDate 9999-12-31 is never ending order.All considers has existing order in all date ranges.

From My UX, if I select Jan to Dec... Results should

JAN ==> 2 orders Feb ==> 3 Orders => Order ID are 1,2,4.

Reason for Month FEB Order ID : 1,2,4 are consider as Active orders because their end dates are falling in FEB.

For example : ORDER ID : 1 having End date has 9999-12-31 which is never ending. Always Active orders in all the date range

Order ID : 2 having End Date has 2018-06-30 so till June he should be considered has Active order for every Month

Order ID : 4 having end date has 2018-02-28 for Feb month OrderID is active Orders

Expected Output

Month NoOfOrders

Jan 2

Feb 3

Month  NoOfOrder  
Jan      2   
Feb      3     

Answer 1

Create a year-month table (inspired from this answer) and join the Order table against it

DECLARE @DateFrom datetime, @DateTo Datetime
SET @DateFrom = ' 2018-01-01'
SET @DateTo = '2018-12-31'

SELECT YearMonth, COUNT(*)
FROM (SELECT CONVERT(CHAR(4),DATEADD(MONTH, x.number, @DateFrom),120) + '-' + CONVERT(CHAR(2),DATEADD(MONTH, x.number, @DateFrom),110) As YearMonth,
             CONVERT(DATE, CONVERT(CHAR(4),DATEADD(MONTH, x.number, @DateFrom),120) + '-' + Convert(CHAR(2),DATEADD(MONTH, x.number, @DateFrom),110) + '-01', 23) fulldate
      FROM master.dbo.spt_values x
      WHERE x.type = 'P'        
        AND x.number <= DATEDIFF(MONTH, @DateFrom, @DateTo)) YearMonthTbl
LEFT JOIN  Orders o ON YEAR(fulldate) >= YEAR(purchaseDate) AND  MONTH(fulldate) >= MONTH(purchaseDate) and fulldate < = enddate
GROUP BY YearMonth

I decided to include also year in output if the input range crosses into a new year

Here is the output for completeness

2018-01 2
2018-02 3
2018-03 2
2018-04 2
2018-05 2
2018-06 3
2018-07 1
2018-08 1
2018-09 1
2018-10 1
2018-11 1
2018-12 1

Answer 2

First Part - Handling records with orderenddate= '9999-12-31'

You can try like following. By putting a OR condition for orderenddate = '9999-12-31' will make sure that never ending records will eppear in all the searchs if the strat date is within the boundary.

SELECT * 
FROM   [order] 
WHERE  purchasedate >= @startdate 
       AND ( orderenddate <= @enddate 
              OR orderenddate = '9999-12-31' ) 

Second Part :

sql query to capture number of active Orders between date range on month wise grouping.

For month wise grouping you can try like following.

  ;WITH numbersequence( number ) 
     AS (SELECT 1 AS Number 
         UNION ALL 
         SELECT number + 1 
         FROM   numbersequence 
         WHERE  number < 12) 

SELECT Sum(ct) ActiveOrderCount, 
       number  AS [month] 
FROM   (SELECT number, 
               CASE 
                 WHEN c.number >= Month(purchasedate) 
                      AND c.number <= Month(orderenddate) THEN 1 
                 ELSE 0 
               END ct 
        FROM   @order 
               CROSS JOIN numbersequence c 
        WHERE  purchasedate >= @startdate 
               AND ( orderenddate <= @enddate 
                      OR orderenddate = '9999-12-31' )) t 
GROUP  BY number 

Online Demo

Output

+------------------+-------+
| ActiveOrderCount | Month |
+------------------+-------+
| 2                | 1     |
+------------------+-------+
| 3                | 2     |
+------------------+-------+
| 2                | 3     |
+------------------+-------+
| 2                | 4     |
+------------------+-------+
| 2                | 5     |
+------------------+-------+
| 3                | 6     |
+------------------+-------+
| 1                | 7     |
+------------------+-------+
| 1                | 8     |
+------------------+-------+
| 1                | 9     |
+------------------+-------+
| 1                | 10    |
+------------------+-------+
| 1                | 11    |
+------------------+-------+
| 1                | 12    |
+------------------+-------+

Assumption : Start Date and End Date falls under same year. Otherwise you need to put year condition also.