WHERE Statement using OR for finanicial period check

Transact-SQL
1

Unable to figure out how to get project data for all periods across multiple years as requested by the user.
INPUT: THRU PERIOD and THRU FISCALYEAR. So all costs period 4 and earlier (many years)
I thought the parenthesis would have worked.

WHERE
"({ GL_DETAIL.PERIOD_N <= ?Period? } AND { GL_DETAIL.FISCAL_YEAR_N = ?FiscalYear? }) OR
({ GL_DETAIL.FISCAL_YEAR_N < ?FiscalYear? })
GROUP BY PROJECTNO_X"

2

You have to repeat your first line multiple times:

({ GL_DETAIL.PERIOD_N <= ?Period? } AND { GL_DETAIL.FISCAL_YEAR_N = ?FiscalYear? }) OR
({ GL_DETAIL.PERIOD_N <= ?Period? } AND { GL_DETAIL.FISCAL_YEAR_N = ?FiscalYear? -1 }

Or use between for the last decade for example:
{ GL_DETAIL.PERIOD_N <= ?Period? } AND { GL_DETAIL.FISCAL_YEAR_N BETWEEN ?FiscalYear? -10 AND ?FiscalYear? })

Trial and error works perfect for this :slight_smile: