SQL Server: Calculating Date Ranges

SQL Server: calculating date ranges

SELECT  'Anything' as Label
        ,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) as firstdaythismonth
        ,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) as today
        ,DATEADD(year, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) as firstdaythismonth_lastyear
        ,DATEADD(year, -1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) as today_lastyear

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'

Date range calculation for a project revenue in SQL?

One method is a recursive CTE to generate the months:

with months as (
      select @startmonth as mon
      union all
      select dateadd(month, 1, mon)
      from months
      where mon < @endmonth
     )
select months.mon, coalesce(cp.monthlyrevenue, 0) as revenue
from months left join
     clientprofile cp
     on cp.project = @project and
        cp.startdate <= months.mon and
        dateadd(month, cp.contractmonths, cp.startdate) >= months.mon;

If the period can exceed 100 months, you need to add option (maxrecursion 0).

Alternatively, you can build a monthly calendar table into your application and do pretty much he same thing using that table directly.

Calculating the number of dates in specific year, between two dates

You didn't specify your dbms, but in general terms:

• Get the 1st day of the @EndDate year i.e. January 1, 2017
• Then calculate the days difference between @FirstOfYear and @EndDate

For example in MySQL, you could use MAKEDATE() to get the first of the year. Then DATEDIFF() to calculate the number of days

SET @StartDate = '2016-12-30';
SET @EndDate = '2017-01-05';  

SELECT DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1 AS DaysInEndDateYear

Result:

| DaysDiffValue|
| ----------------: |
|                 5 |

If you also need to handle ranges where both @StartDate and @EndDate are in the same year:

SET @StartDate = '2017-01-05';
SET @EndDate = '2017-03-14';  

SELECT CASE WHEN Year(@StartDate) = Year(@EndDate) THEN DateDiff(@EndDate, @StartDate) + 1
            ELSE DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1
       END AS DaysInEndDateYear

Results:

| DaysInEndDateYear |
| ----------------: |
|                69 |

db<>fiddle here

Sql Server Count with date range

Is this what you are looking for?

select d.date, COUNT(r.name) as count
from #Date d left join #Reservation r on r.departure >= d.date and r.arrival <= d.date
group by d.date
order by d.date

SQL Counting Consecutive Days in Date Ranges

This is a gaps-and-islands problem. One option is to use lag() and a window sum() to build groups of adjacent records. You can then aggregate by group and count the number of consecutive days, and finally filter on the greatest streak by name:

select name, max(consecutive_days) consecutive_days
from (
    select name, datediff(day, min(start_date), max(end_date)) + 1 consecutive_days
    from (
        select t.*, 
            sum(case when start_date = dateadd(day, 1, lag_end_date) then 0 else 1 end) over(partition by name order by start_date) grp
        from (
            select t.*, 
                lag(end_date) over(partition by name order by start_date) lag_end_date
            from mytable t
        ) t
    ) t
    group by name, grp
) t
group by name

Demo on DB Fiddle:

name   | consecutive_days
:----- | ---------------:
Jenny  |                3
Johnny |                9

SQL count date range

select CustomerName, ClientID, count(*)
from
(
    select CustomerName, ClientID
    from Orders
    where datediff(mm, DateOrdered, getdate()) <= 1
 )a
group by CustomerName, ClientID

What this does is utilize a subquery that filters the rows by the dates in a given month (that seems to be what you are looking for). Then it groups by the CustomerName and ClientID and gets the sum of their orders.

Calculate period between two dates in months and days

You have to add one day (using DATEADD) to the second date (@date2):

-- first example
DECLARE @date1 DATETIME = '2009-01-01 00:00:00'
DECLARE @date2 DATETIME = '2010-06-30 00:00:00'

SELECT CAST(DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)) AS VARCHAR(6)) + ' Months ' + CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)), @date1), DATEADD(DAY, 1, @date2)) AS VARCHAR(2)) + ' Days'

-- second example
DECLARE @date1 DATETIME = '2020-01-01 00:00:00'
DECLARE @date2 DATETIME = '2020-01-31 00:00:00'

SELECT CAST(DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)) AS VARCHAR(6)) + ' Months ' + CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, @date1, DATEADD(DAY, 1, @date2)), @date1), DATEADD(DAY, 1, @date2)) AS VARCHAR(2)) + ' Days'

demo on dbfiddle.uk

Have a look at the following examples:

• DATEDIFF of 2020-01-01 and 2020-01-01 is 0.
  
  (max. difference between 2020-01-01 00:00:00 and 2020-01-01 23:59:59 is equals to 0 day, 23 hours, 59 minutes and 59 seconds.
• DATEDIFF of 2020-01-01 and 2020-01-02 is 1.
  
  (max. difference between 2020-01-01 00:00:00 and 2020-01-02 23:59:59 is equals to 1 day, 23 hours, 59 minutes and 59 seconds.

So you can't count the last day as day in the difference since the day isn't over.

SQL speed: Return each date in date range and count() for each

Using a recursive cte is one of the worst ways to generate a range of dates. Using a stacked cte is much faster for generating the date range on demand than using a recursive cte.

If you are going to be using it across many rows, or long durations, or you will be running this sort of operation more than once, you would be better off just creating a Dates or Calendar table.

For only 152kb in memory, you can have 30 years of dates in a table, and you could use it like so:

/* dates table */ 
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo 
               cross join n as tenK cross join n as hundredK
order by [Date];

create unique clustered index ix_dbo_Dates_date 
  on dbo.Dates([Date]);

and query it like so:

select
    d.[Date]
  , OrderCount = count(o.OrderID)
from dates d
  left join orders o
    on convert(date,o.OrderDate) = d.[Date]
group by d.[Date]
order by d.[Date] desc

Number and Calendar table reference:

• Generate a set or sequence without loops - 3 - Aaron Bertrand
• The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones

---

If you really do not want a calendar table, you can just use the stacked cte portion:

declare @fromdate date = '20160101';
declare @years    int  = 1;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo 
                /* cross join n as tenK cross join n as hundredK */
   order by [Date]
)
select
    d.[Date]
  , OrderCount = count(o.OrderID)
from dates d
  left join orders o
    on convert(date,o.OrderDate) = d.[Date]
group by d.[Date]
order by d.[Date] desc

What is the best way to sum data over multiple date ranges in SQL?

Thank you @Larnu, this seems to work, where Range is passed in as a table, and Purchases is the table listed in the question:

SELECT r.StartDate,
       r.EndDate,
       SUM(p.Amount) AS Total
FROM Range r
JOIN Purchases p ON p.Date >= r.StartDate
                AND p.Date < r.EndDate
GROUP BY r.StartDate, r.EndDate;

Which results in:

---