Help, I have a query with a variable that doesn't work, if I substitute a valid value for the variable it works correctly. What am I doing wrong here?
SELECT T1.FormatCode, T0.Account, MONTH(T0.TaxDate) AS Period,
CASE MONTH(TaxDate) WHEN 1 THEN SUM(Debit - Credit) END AS January,
CASE MONTH(TaxDate) WHEN 2 THEN SUM(Debit - Credit) END AS February,
CASE MONTH(TaxDate) WHEN 3 THEN SUM(Debit - Credit) END AS March,
CASE MONTH(TaxDate) WHEN 4 THEN SUM(Debit - Credit) END AS April,
CASE MONTH(TaxDate) WHEN 5 THEN SUM(Debit - Credit) END AS May,
CASE MONTH(TaxDate) WHEN 6 THEN SUM(Debit - Credit) END AS June,
CASE MONTH(TaxDate) WHEN 7 THEN SUM(Debit - Credit) END AS July,
CASE MONTH(TaxDate) WHEN 8 THEN SUM(Debit - Credit) END AS August,
CASE MONTH(TaxDate) WHEN 9 THEN SUM(Debit - Credit) END AS Sept,
CASE MONTH(TaxDate) WHEN 10 THEN SUM(Debit - Credit) END AS Oct,
CASE MONTH(TaxDate) WHEN 11 THEN SUM(Debit - Credit) END AS Nov,
CASE MONTH(TaxDate) WHEN 12 THEN SUM(Debit - Credit) END AS Dec
FROM JDT1 AS T0 INNER JOIN
OACT AS T1 ON T0.Account = T1.AcctCode
WHERE (T0.TaxDate > '12/31/2007') and T1.segment_1 = [%0]
GROUP BY T1.FormatCode, T0.Account, MONTH(T0.TaxDate), YEAR(T0.TaxDate)
ORDER BY T1.formatcode
Segment_1 is the value I what to let the user enter.
This query also groups by month, so there is a row for each month, each GL account. Is there a way I can collapse this to one line per account? And show each the GL amount of each month across?
The purpose of this query is to give a report of each GL account with a column amount for each month.
Thanks