Syntax on View/Cross tab join

[klaul]

Hoping someone can help me here!
I'm having some problems trying to get the right syntax for a view, and am wondering if someone could point me in the right
direction!

My code is currently:

SELECT DISTINCT a.Department,
		a.Section,
    		SUM(CASE b.Type WHEN 'T' THEN b.LYAmt ELSE 0 END) AS TLYAmt,
    		SUM(CASE b.Type WHEN 'I' THEN b.LYAmt ELSE 0 END) AS ILYAmt,
    		SUM(CASE b.Type WHEN 'A' THEN b.LYAmt ELSE 0 END) AS ALYAmt,
    		SUM(CASE b.Type WHEN 'T' THEN b.CYAmt ELSE 0 END) AS TCYAmt,
    		SUM(CASE b.Type WHEN 'I' THEN b.CYAmt ELSE 0 END) AS ICYAmt,
	    	SUM(CASE b.Type WHEN 'A' THEN b.CYAmt ELSE 0 END) AS ACYAmt	
		FROM tbl_Departments a
		INNER JOIN tbl_AccountDetail b ON
		a.DeptCode = b.DeptCode
		GROUP BY a.section

So basically a cross-tab join. What I would like to do however, is have multiple values in Type making up 'TLYAmt'; so rather than having

SUM(CASE b.Type WHEN 'T' THEN b.LYAmt ELSE 0 END) AS TLYAmt

I would be like to have

SUM(CASE b.Type WHEN 'T' or 'P' or 'H' THEN b.LYAmt ELSE 0 END) AS TLYAmt

Except of course that that syntax doesn't work!
(so for example, if LyAmt for T was 1, P was 2, and H was 3, TLYAmt would return 6. Just to make it a bit more complicated, I wouldn't have multiple values for each CASE statement.

I've googled around and can't seem to find a solution, so am wondering if anyone knows whether this can be done? If it can't (and I am fast beginning to believe I am barking up the wrong tree!), can someone suggest something that might work in it's place?

Many thanks!

[ck9663]

OR does not work that way...

try:

SUM(CASE WHEN b.Type  = 'T' or b.Type  = 'P' or b.Type  = 'H' THEN b.LYAmt ELSE 0 END) AS TLYAmt

for cleaner code, you can also

SUM(CASE WHEN b.Type  in ('T', 'P','H') THEN b.LYAmt ELSE 0 END) AS TLYAmt

Happy coding!

[klaul]

That works fantastically - thank you very much!