Skip to Content
0
Former Member
Jul 16, 2008 at 05:05 PM

Query variable doesn't work

34 Views

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