CODEWITHSUNDEEP
sql-serverdate
nnt
nnt

Reputation: 97

How to get first 3 months of date column in SQL Server?

I'd like to know how to get the first 3 month of data in table in sql server.
The table has value and created_date columns:

╔═══════════════════════════════════╗
║  value      created_date          ║
╠═══════════════════════════════════╣
║  13         2013-08-20 11:30:00   ║
║   7         2013-08-20 04:30:00   ║
║   9         2013-08-21 11:30:00   ║
║  11         2013-08-22 11:30:00   ║
║  .                   .            ║
║  .                   .            ║
║  .                   .            ║
║  18         2016-10-03 11:30:00   ║
╚═══════════════════════════════════╝

When I use DateADD(month, 3, getDate()), it only return the next 3 monthss of the current date. So, I have no idea how to get them.

My actual problem is that I want to calculate the average value of the first 3 months. My plan is to get the minimum date from table and after that, get the next 3 months and calculate the average value.

Upvotes: 1

Views: 2108

Answers (4)

Tywan Terrell
Tywan Terrell

Reputation: 39

If you are using MS SQL Server 2012 or higher you can use Windowing Functions to achieve this. You can use rows unbounded preceding and 2 following. Your code will look something like:

SELECT value,
       AVG(value) OVER (PARTITION BY value ORDER BY CREATED_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
       AS '3_month_avg'
FROM <yourtable>

Upvotes: 2

Cartoondog
Cartoondog

Reputation: 105

This should give you your starting point of reference to get the minimum date, that you can date add too or use as the lowerbound of a compound condition:

SELECT MIN(created_date) FROM yourtable

Upvotes: 0

Ahmed Saeed
Ahmed Saeed

Reputation: 851

Try This:

Select * from yourTable 
Where CreatedDate <= (Select DateAdd(Month, +3, Min(CreatedDate)) from YourTable)

Upvotes: 2

Chaos Legion
Chaos Legion

Reputation: 2970

Try this:

DECLARE @minDate DATETIME;

SELECT @minDate = MIN(created_date) FROM YourTable;

SELECT * 
FROM YourTable
WHERE created_date <= DATEADD(MONTH, 3, @minDate);

Upvotes: 0

Related Questions