SQL Count of Open Orders Each Day Between Two Dates

Calculate the number of records for each date between 2 dates

No need to reinvent the wheel - there are many examples of recursive CTE calendar tables out there, similar to below.

DECLARE @StartDate date = '01-Nov-2020', @EndDate date = '22-Feb-2021';

WITH Date_Range_T (d_range) AS (
    SELECT @StartDate AS d_range
    UNION ALL
    SELECT DATEADD(DAY, 1, d_range) 
    FROM Date_Range_T 
    WHERE DATEADD(DAY, 1, d_range) < @EndDate
)
SELECT d_range, COUNT(Id) AS Total 
FROM Date_Range_T 
LEFT JOIN tbl_Support_Requests R ON R.CreatedDate = d_range
GROUP BY d_range
ORDER BY d_range ASC
-- Set to the max number of days you require
OPTION (MAXRECURSION 366);

Comments:

• Why use a datetime2 for a date?
• Do you definitely want < the end date or <=?
• Are you familiar with how between works - its not always intuitive.
• Alias all tables for better readability.
• Semi-colon terminate all statements.
• Consistent casing makes the query easier to read.
• Use an unambiguous date format for date strings.

Count work days between two dates

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

If you want to include holidays, you have to work it out a bit...

SQL Server query to calculate daily “carrying” open orders

This should give you the desired output. This query assumes you have a distinct listing of dates to query against.

The portion you had the most trouble with, the carry over, uses a FROM statement that references two datasets. This is different from a JOIN and is done by referencing two tables and separating them with a ,. This will in turn create a query where each date in the dates_table gets assigned the entire dataset from the orders table. In order to determine carry over a WHERE statement is used to filter the copied orders table and a GROUP BY is used count each filtered order. After that a LEFT JOIN is used to pull the results by date to the date_table. Feel free to leave a comment if you want more explanation.

SQL with Dates Table this table is named dates_table in the query below

SELECT 
    dates.date,
    iif(carry_over.count IS NULL, 0, carry_over.count) AS carry_over,
    iif(fulfilled.count IS NULL, 0, fulfilled.count) AS fulfilled,
    iif(not_fulfilled.count IS NULL, 0, not_fulfilled.count) AS not_fulfilled

FROM dates_table AS dates

/* Fulfilled that day */
LEFT JOIN 
(
    SELECT 
        dateadd(d, datediff(d,0, fulfilled_date), 0) AS date, 
        COUNT(dateadd(d, datediff(d,0, fulfilled_date), 0)) AS count
    FROM orders
    GROUP BY dateadd(d, datediff(d,0, fulfilled_date), 0)
) AS fulfilled
    ON dates.date = fulfilled.date

/* Not Fulfilled that day */
LEFT JOIN 
(
    SELECT 
        dateadd(d, datediff(d,0, order_date), 0) AS date, 
        COUNT(dateadd(d, datediff(d,0, order_date), 0)) AS count
    FROM orders
    WHERE (dateadd(d, datediff(d,0, order_date), 0) <> dateadd(d, datediff(d,0, fulfilled_date), 0)) OR fulfilled_date IS NULL
    GROUP BY dateadd(d, datediff(d,0, order_date), 0)
) AS not_fulfilled
    ON dates.date = not_fulfilled.date

/* Carry Over */
LEFT JOIN 
(
    SELECT
        full_data.date,
        COUNT(full_data.order_date) AS count

    FROM
    (
        SELECT 
            dates.date AS date,
            order_dates.order_date AS order_date,
            order_dates.fulfilled_date AS fulfilled_date

        FROM dates_table AS dates,

        /* Allocate all dates to key of dates, filter out what is relevant in where */
        (
            SELECT 
                dateadd(d, datediff(d,0, order_date), 0) AS order_date,
                iif(fulfilled_date IS NULL, NULL, dateadd(d, datediff(d,0, fulfilled_date), 0)) AS fulfilled_date
            FROM orders
        ) AS order_dates

    ) AS full_data

    /* Determines what is carry over */
    WHERE (full_data.date < full_data.fulfilled_date OR full_data.fulfilled_date IS NULL) AND full_data.date >= full_data.order_date
    GROUP BY full_data.date

) AS carry_over
    ON dates.date = carry_over.date

rextester

Edit: Added field names for final query as they were all labeled count before

Edit: Added another query assuming you have a table of dates and # for orders table

Edit: Removed query without date_table, count on carry_over now done on order_date as was not counting nulls, for fulfilled that day numbers now pulls fulfilled_date instead of order_date

SQL Query to Count Items Within Date Range?

Below is a query returning consecutive dates between given date and given date - 5 days. You may use CURRENT DATE instead of this constant of course.

WITH T (DT) AS 
(
VALUES DATE('2020-02-13') - 5 DAYS
  UNION ALL
SELECT DT + 1 DAY
FROM T 
WHERE DT < DATE('2020-02-13')
)
SELECT DT FROM T;

The result is:

|DT        |
|----------|
|2020-02-08|
|2020-02-09|
|2020-02-10|
|2020-02-11|
|2020-02-12|
|2020-02-13|

Further usage of such a "virtual table" is obvious: you may LEFT JOIN it with your base table on B.START_DATE < DEC(TO_CHAR(T.DT, 'YYYYMMDD'), 8) AND DEC(TO_CHAR(T.DT, 'YYYYMMDD'), 8) <= B.END_DATE grouping the result by T.DT.

SQL : given a year and month how can I count the number of orders per WEEK

You can use CASE to get a single week number then group by that number.

Use CROSS APPLY (VALUES to avoid repeating code

SELECT
    v.WeekNumber,
    TotalOrders = COUNT(*)
FROM
    dbo.Orders
CROSS APPLY (VALUES (
    CASE WHEN DateCreated >= DATEFROMPARTS(@year, @month, 1) AND DateCreated < DATEFROMPARTS(@year, @month, 8)
         THEN 1
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 8) AND DateCreated < DATEFROMPARTS(@year, @month, 15)
         THEN 2
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 15)) AND DateCreated < DATEFROMPARTS(@year, @month, 22)
         THEN 3
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 22)) AND DateCreated < DATEFROMPARTS(@year, @month, 29)
         THEN 4
         ELSE 5
    END
)) v(WeekNumber)

WHERE DateCreated >= DATEFROMPARTS(@year, @month, 1)
  AND DateCreated < DATEADD(month, 1, DATEFROMPARTS(@year, @month, 1))
GROUP BY
  v.WeekNumber;

Note the use of >= AND < for a half-open interval. This ensure that the whole of the last day is included.

---

Related Topics