Hi

A where clause should limit the results.
( hasamafactor=5
AND ( misgeret=11
OR misgeret=14 ) )
OR
( hasamafactor<>5
AND misgeret<>11
AND misgeret<>14 )

You may want to check out dynamic pivot solutions such as
http://www.sqlteam.com/item.asp?ItemID=2955

John


"avital" <avitalnagar@walla.co.il> wrote in message
news:9f5390a8.0411260758.6f8f4c47@posting.google.c om...
> Hi,
>
> I have a sql query with cases. I need to add a condition that if
> hasamafactor=5 then display only cases m11-m14 else display the rest.
> Of course sum ( kamut) as total4mosad
> has to be only for the relevant cases and not all of them.
>
> CREATE PROCEDURE [dbo].[MisgeretMosad_Sum]
>
>
>
> (@ParamYear tinyint,
>
> @ParamMonth tinyint,
>
> @hasamafactor tinyint
>
> )
>
>
>
> AS
>
>
>
> --drop table #mytmp
>
> select * into #mytmp from (
>
>
>
> select count(class) as
> kamut,misgeret,t2.semelmosad,t3.shemmosad,HASAMAFA CTOR
>
>
>
> from pnimiotfile.dbo.importfile T2
>
>
>
> LEFT join
>
> pnimiot.dbo.tblmisgeret as t1
>
> on t2.misgeret =t1.codemisrad
>
> left join pnimiot.dbo.mosad as t3
>
> on t2.semelmosad=t3.semelmosad
>
>
>
> where fileyear=@ParamYear and filemonth=@ParamMonth and leavedate is
> null
>
> and hasamafactor=@hasamafactor
>
> group by misgeret,t2.semelmosad,t3.shemmosad,HASAMAFACTOR
>
>
>
> ) t
>
>
>
> select semelmosad, shemmosad, HASAMAFACTOR,
>
> max ( case when misgeret=10 then kamut else 0 end ) as
> m10,
>
> max ( case when misgeret=11 then kamut else 0 end ) as
> m11,
>
> max ( case when misgeret=14 then kamut else 0 end ) as
> m14,
>
> max ( case when misgeret=16 then kamut else 0 end ) as
> m16,
>
> max ( case when misgeret=18 then kamut else 0 end ) as
> m18,
>
> max ( case when misgeret=19 then kamut else 0 end ) as
> m19,
>
> max ( case when misgeret=21 then kamut else 0 end ) as
> m21,
>
> max ( case when misgeret=22 then kamut else 0 end ) as
> m22,
>
> max ( case when misgeret=26 then kamut else 0 end ) as
> m26,
>
> max ( case when misgeret=28 then kamut else 0 end ) as
> m28,
>
> max ( case when misgeret=30 then kamut else 0 end ) as
> m30,
>
> max ( case when misgeret=31 then kamut else 0 end ) as
> m31,
>
> max ( case when misgeret=32 then kamut else 0 end ) as
> m32,
>
> max ( case when misgeret=38 then kamut else 0 end ) as
> m38,
>
> max ( case when misgeret=42 then kamut else 0 end ) as
> m42,
>
> max ( case when misgeret=51 then kamut else 0 end ) as
> m51,
>
> max ( case when misgeret=52 then kamut else 0 end ) as
> m52,
>
> max ( case when misgeret=53 then kamut else 0 end ) as
> m53,
>
> max ( case when misgeret=54 then kamut else 0 end ) as
> m54,
>
> max ( case when misgeret=60 then kamut else 0 end ) as
> m60,
>
> max ( case when misgeret=65 then kamut else 0 end ) as
> m65,
>
> sum ( kamut) as total4mosad
>
> from #mytmp
>
> --left join pnimiot.dbo.mosad m on
> pnimiotfile.dbo.importfile.semelmosad=t2.semelmosa d
>
> group by semelmosad,shemmosad,HASAMAFACTOR
>
> order by hasamafactor,semelmosad
>
> GO