sum for X months in sql

dateadd sql
datediff sql
how to get month and year from date in sql server 2008
sql rolling sum by date
datepart sql
date subtract sql
eomonth sql
sql getdate

I have a formula to get the revenue for 2 months ago. I need to get the sum of revenue for the past three months. I am very new at it and will appreciate any help! Thank you.

(CASE when MONTh(`Date`) = (MONTH(CURRENT_DATE())-2)

 then

 CASE when YEAR(`Date`) = YEAR(CURRENT_DATE())

 then `Revenue`

 end

 end

)

Your tests won't work when 2 months ago is in the previous year.

Put the condition in the WHERE clause, and use date arithmetic to get 2 months ago.

SELECT ...
FROM ...
WHERE MONTH(Date) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)
AND   YEAR(Date) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)

SQL Server DATEADD() Function, SELECT DATEADD(month, 2, '2017/08/25') AS DateAdd;. Try it Yourself �. Example. Subtract two months from a date, then return the date: SELECT� Essentially, I want one row to sum up all sales for that item for those 4 months (May to August, inclusive of every day in the month). Ignore other columns (price & margin) if necessary. Any help would be greatly appreciated, I need to have this done by Friday!


If we are dealing with calendar months,

we could use DATE_FORMAT function to lop off the day portion, an expression like this:

 DATE_FORMAT(t.`date`,'%Y-%m-01')

and then we can compare that expression to other date values.

It's not entirely clear what result we are attempting to achieve. If the goal is to get a total revenue for 3 months ago and 2 months ago, we could do conditional aggregation.

Something like this:

SELECT SUM( CASE DATE_FORMAT(t.`date`,'%Y-%m-01')
              WHEN DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL -3 MONTH
              THEN t.`revenue`
              ELSE NULL
            END
       ) AS revenue_3_months_ago
     , SUM( CASE DATE_FORMAT(t.`date`,'%Y-%m-01')
              WHEN DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL -2 MONTH
              THEN t.`revenue`
              ELSE NULL
            END
       ) AS revenue_2_months_ago
     , SUM( CASE
              WHEN DATE_FORMAT(t.`date`,'%Y-%m-01')
                   >= DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL -3 MONTH
               AND DATE_FORMAT(t.`date`,'%Y-%m-01')
                   <= DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL  0 MONTH
              THEN t.`revenue`
              ELSE NULL
            END
       ) AS revenue_past_3_months
  FROM t

NOTE: we can test expressions in SELECT statement, before we incorporate them in more complex SQL

SELECT DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL -3 MONTH  AS `n3`
     , DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL -1 MONTH  AS `n1` 
     , DATE_FORMAT(DATE(NOW()),'%Y-%m-01') + INTERVAL  0 MONTH  AS `n0`

4 Ways to Calculate a Running Total With SQL, An analytic function lets you partition data by a specific field. For instance, in this case, we can break down the rolling sum by driver_id and month. For instance, in this case, we can break down the rolling sum by driver_id and month. This will give us the running total by customer and month. So every month will start again at 0.


TIMESTAMPDIFF() does exactly what you want. It counts the number of month boundaries between two dates, so:

(CASE WHEN TIMESTAMPDIFF(MONTH, date, curdate()) = 2
      THEN `Revenue`

 END)

Sum Last X months of data, I have a dataset that tracks customer purchases and I'm trying to calculate total spend over the last 12 months. I've tried numerous formulas as. The SQL Server Query The query to fetch the cumulative figures of previous months will be, SELECT DATENAME (MONTH, DATEADD (M, MONTH (SalesDate), - 1)) Month, SUM (Quantity) [Total Quanity], SUM (Price) [Total Price] FROM dbo.Sales GROUP BY MONTH (SalesDate) HAVING MONTH (SalesDate) < (SELECT DATEPART (M, DATEADD (M, 0, GETDATE ())))


SQL Server MONTH() Function By Practical Examples, In this tutorial, you will learn how to use the SQL Server MONTH() function to extract It returns the gross sales by month in 2018 using the SUM() function and� SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL


SQL Server DATEADD Function By Practical Examples, Handling month examples. If you add a number of months to a date and the day of the date result does not exist, the DATEDIFF() function will� how to calculate sum for week,day ,month? In SQL SERVER - 705411


Calculate the Last Day of the Month using SQL, When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then� SQL AVG() function: SQL AVG function calculates the average value of a column of numeric type. The SQL AVG() function calculates NON NULL values. The SQL WHERE clause is used along with SQL AVG() function to get the result in a specific format based on one or more conditions.