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!