how would I change the following query so that it pulls data from the last month without using an actual date

aa3 0
Viewed 31
SELECT PARENTACCOUNT, 
       PREVAVAILBALANCE, 
       BALANCECHANGE, 
       NEWBALANCE, 
       ACTIVITYDATE
FROM SAVINGSTRANSACTION
WHERE PARENTACCOUNT='0000099980' AND
PARENTID='0010' OR
PARENTACCOUNT='0000099979'AND
PARENTACCOUNT = '0010'

I need this to pull value for the last month without using an actual date because I will be using it again next month

mysql sql
1 Answers

Use YEAR and MONTH minus a interval of 1 month in your WHERE clause based on your ACTIVITYDATE column:

SELECT PARENTACCOUNT, 
       PREVAVAILBALANCE, 
       BALANCECHANGE, 
       NEWBALANCE, 
       ACTIVITYDATE
FROM SAVINGSTRANSACTION
WHERE PARENTACCOUNT='0000099980' 
AND (PARENTID='0010' OR PARENTACCOUNT='0000099979')
AND PARENTACCOUNT = '0010'
AND (YEAR(ACTIVITYDATE) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(ACTIVITYDATE) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH))

Whenever you run the query, it'll get the previous months data.

GRIV0
Related