Data Connections

[ldoodle]

Hey,

I have a few data connections in my workbook. I need to pass a parameter to the SELECT portion of the SQL query, not the WHERE clause.

SELECT cast(sum(case when year(oi.ITEMDATE) = [U][B]?[/B][/U] then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) [Total Fees 1],

This kicks up a message about invalid syntax.

This is the complete query. As you may be able to see, I'm getting last years and this years values in separate columns. At the moment, it's based on the current date of the SQL server, but I would like to be able to use parameters so I can load historic data.

select		datename(month, oi.ITEMDATE) [Month],			cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) [Total Fees 1],
			cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) [Total Fees 2],
			cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Disbs 1],
			cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Disbs 2],
			cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) - 
				cast(sum(case when year(oi.ITEMDATE) = year(getdate()) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Profit 1],
			cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) as decimal(11, 2)) - 
				cast(sum(case when year(oi.ITEMDATE) = year(dateadd(year, -1, getdate())) then (case when wh.WIPCODE in ('AGENTS', 'CHGINV', 'OFFEES', 'TRANSL', 'MISC', 'SEARCH', 'TRAVLR') then (wh.LOCALTRANSVALUE * oi.BILLPERCENTAGE/100) * -1 else 0 end) end) as decimal(11, 2)) [Profit 2]
from		WORKHISTORY wh, NAME n, OPENITEM oi, DEBTORHISTORY dh
where		wh.STATUS <> 0 and (wh.REFENTITYNO = -5167 or (wh.REFENTITYNO is null and wh.ENTITYNO = -5167)) and
			month(oi.ITEMDATE) >= 5 and month(oi.ITEMDATE) < month(getdate()) and wh.MOVEMENTCLASS = 2 and
			n.NAMENO = wh.EMPLOYEENO and dh.REFENTITYNO = wh.REFENTITYNO and dh.REFTRANSNO = wh.REFTRANSNO and dh.MOVEMENTCLASS in (1, 2) and
			dh.ITEMENTITYNO = oi.ITEMENTITYNO and dh.ITEMTRANSNO = oi.ITEMTRANSNO and dh.ACCTENTITYNO = oi.ACCTENTITYNO and
			dh.ACCTDEBTORNO = oi.ACCTDEBTORNO and oi.ITEMTYPE not in (513, 514)
group by	datename(month, oi.ITEMDATE)
order by	max(month(oi.ITEMDATE))