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
1  3339 
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" <av*********@walla.co.il> wrote in message
news:9f**************************@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: GMKS |
last post by:
Hello all,
I have 13 check boxes on a form.
I am trying to check all the check boxes to determine if they are true or false when I close the form.
At present only the first IF...Then...Else...
|
by: Dan H. |
last post by:
Hello,
I have an application that requires a collection that is sorted by a double
field first, and then by a long field.
So, lets say I have an class that has 2 fields called time, priority. I...
|
by: Kai Bohli |
last post by:
Hi all !
I need to translate a string to Ascii and return a string again. The code below dosen't work for
Ascii (Superset) codes above 127. Any help are greatly appreciated.
protected...
|
by: Amy |
last post by:
Hi,
I have 6 If Then Else statements I was supposed to write. I did so but I
know that they have to be wrong because they all look the same. Could
someone take a look at them and point me in the...
|
by: ECathell |
last post by:
I had read an article at one time that suggested a pattern to get around deeply nested if..then..else hell...
Can anyone point me in that direction?
select case statements wont work for me in...
|
by: David Lozzi |
last post by:
Howdy,
I have a function that uploads an image and that works great. I love ..Nets built in upload, so much easier than 3rd party uploaders!
Now I am making a public function that will take the...
|
by: lawjake |
last post by:
I am having a dispute at work over endifs, and cannot find any treatise on
this. So I am hoping that I can get a lot of correspondece confirming my
belief.
Here it is: I believe the following:...
|
by: =?Utf-8?B?WmFyYm9yZw==?= |
last post by:
I'm writing a little program that will run when a user logs in, checks their
password expiration and also installs a piece of monitoring software if
necessary.
The program has to run on Vista so I...
|
by: jzakiya |
last post by:
I'm translating a program in Python that has this IF Then chain
IF x1 < limit: --- do a ---
IF x2 < limit: --- do b ---
IF x3 < limit: --- do c ---
.-----
------
IF x10 < limt: ---...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
