Synametrics Technologies

Hi,
I appreciate any help with this!
I have this query that takes past 6 month, I want to make this dynamic because I'm using this in Powerbi and so each month I refresh the data in powerbi, it bring in the current past 6 month.

SELECT DISTINCT AGENT_ID
FROM DB2PROD.BONUS_PAYMENT
WHERE BONUS_TYPE = 'R'
AND NEW_BIZ_YR = '2022'
AND NEW_BIZ_MO >= '3'

Fareed,

You did not mention which back-end RDBMS you were using. However, since the table name has the word DB2, I assume you're connecting to a DB2 database. My answer below is based on this assumption.

DB2 contains several functions for date/time. Please refer to Fun with dates and times - IBM Developer for details.

Please note that I have not tested the following query against a DB2 database, and therefore, you may have to fine-tune it.

SELECT DISTINCT AGENT_ID
FROM DB2PROD.BONUS_PAYMENT
WHERE BONUS_TYPE = 'R'
AND NEW_BIZ_YR = YEAR(current_date - 6 MONTHS)
AND NEW_BIZ_MO = MONTH(current_date - 6 MONTHS)