Calculate the Number of Records for Each Date Between 2 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.

Get all dates between two dates in SQL Server

My first suggestion would be use your calendar table, if you don't have one, then create one. They are very useful. Your query is then as simple as:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @MinDate
AND     Date < @MaxDate;

If you don't want to, or can't create a calendar table you can still do this on the fly without a recursive CTE:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

For further reading on this see:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

With regard to then using this sequence of dates in a cursor, I would really recommend you find another way. There is usually a set based alternative that will perform much better.

So with your data:

  date   | it_cd | qty 
24-04-14 |  i-1  | 10 
26-04-14 |  i-1  | 20

To get the quantity on 28-04-2014 (which I gather is your requirement), you don't actually need any of the above, you can simply use:

SELECT  TOP 1 date, it_cd, qty 
FROM    T
WHERE   it_cd = 'i-1'
AND     Date <= '20140428'
ORDER BY Date DESC;

If you don't want it for a particular item:

SELECT  date, it_cd, qty 
FROM    (   SELECT  date, 
                    it_cd, 
                    qty, 
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY ic_id 
                                                    ORDER BY date DESC)
            FROM    T
            WHERE   Date  <= '20140428'
        ) T
WHERE   RowNumber = 1;

SQL query to select dates between two dates

you should put those two dates between single quotes like..

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date >= '2011/02/25' and Date <= '2011/02/27'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

mysql: get record count between two date-time

May be with:

SELECT count(*) FROM `table` 
where 
    created_at>='2011-03-17 06:42:10' and created_at<='2011-03-17 07:42:50';

or use between:

SELECT count(*) FROM `table` 
where 
    created_at between '2011-03-17 06:42:10' and '2011-03-17 07:42:50';

You can change the datetime as per your need. May be use curdate() or now() to get the desired dates.

Python generating a list of dates between two dates

You can use pandas.date_range() for this:

import pandas
pandas.date_range(sdate,edate-timedelta(days=1),freq='d')

---

DatetimeIndex(['2019-03-22', '2019-03-23', '2019-03-24', '2019-03-25',
           '2019-03-26', '2019-03-27', '2019-03-28', '2019-03-29',
           '2019-03-30', '2019-03-31', '2019-04-01', '2019-04-02',
           '2019-04-03', '2019-04-04', '2019-04-05', '2019-04-06',
           '2019-04-07', '2019-04-08'],
          dtype='datetime64[ns]', freq='D')

Checking if a given date fits between a range of dates

With SQL Server it's actually as simple as:

SELECT startDate, endDate
FROM YourTable
WHERE '2012-10-25' between startDate and endDate

For each day between two dates, add a row with the same info but only that day in the start/end columns

May be you need a Recursive CTE.

CREATE TABLE #dates(NAME  VARCHAR(50),START DATETIME,[END] DATETIME)

INSERT INTO #dates
VALUES      ('Bob','2014-10-30','2014-11-02')

DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM   #dates);

WITH cte
     AS (SELECT NAME,
                START,
                [END]
         FROM   #dates
         UNION ALL
         SELECT NAME,
                Dateadd(day, 1, start),
                Dateadd(day, 1, start)
         FROM   cte
         WHERE  start < @maxdate)
SELECT *
FROM   cte 

OUTPUT :

name    START       END
----    ----------  ----------
Bob     2014-10-30  2014-10-30
Bob     2014-10-31  2014-10-31
Bob     2014-11-01  2014-11-01
Bob     2014-11-02  2014-11-02

How to find the number of days between two dates

You would use DATEDIFF:

declare @start datetime
declare @end datetime

set @start = '2011-01-01'
set @end = '2012-01-01'

select DATEDIFF(d, @start, @end)

results = 365

so for your query:

SELECT dtCreated
    , bActive
    , dtLastPaymentAttempt
    , dtLastUpdated
    , dtLastVisit
    , DATEDIFF(d, dtCreated, dtLastUpdated) as Difference
FROM Customers
WHERE (bActive = 'true') 
    AND (dtLastUpdated > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

---

Related Topics