SEO

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

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