SQL用于计算过去24个月及以上的总和

Question

I want to calculate the sum and average of my data in the past 24 months how can I achieve that? In my database I have 1000 records with the field (ID, Store_ID, Date, Sales) I want to calculate the average of all my data from THIS month up to the last 24 months.

Answer 1

I believe you are looking for the DATEADD function. Here is the documentation.

Essentially, you will use a where condition to check if a date is older than a certain date.

By using the DATEADD function with the current time, you can create a date X months in the past.

SELECT columns FROM table WHERE date < DATEADD(month, numberOfMonths, GETDATE());

For SQL querys that use aggregate functions (such as SUM), depending on how you use the function you may require the HAVING clause; instead of, the WHERE clause. I don't think your case will require this; but, I may be wrong.

You can read more on HAVING vs WHERE at this article.

Here is an example:

SELECT columns FROM table HAVING date < DATEADD(month, numberOfMonths, GETDATE());

The code samples are untested; however, the general structure is there. We are also using the GETDATE() function to get the current database date.