 |
| SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
November 24th, 2004, 03:14 PM
|
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
subtraction
Hi,
I have a query in sql server 2000. I want to add a column called
balance to the result set.
Balance will be studsTotal-michsatotal but I am not sure how to add
this line to the query.
Any help would be much appreciated.
Thanks
---My query
select oo.semelmosad,
max(case when hasamafactor=7 then michsaklita end ) as mk7,
max(case when hasamafactor=5 then michsaklita end ) as mk5,
sum (michsaklita ) as michsatotal,
max(case when hasamafactor=7 then studs end ) as s7,
max(case when hasamafactor=5 then studs end ) as s5,
sum (studs) as studsTotal
from
(
select mymichsa.semelmosad,shemmosad, michsaklita ,hasamafactor,
count(t2.serial_no) as studs
from
(
select semelmosad, michsaklita, yearcode,
case when case when guf=28 then 7 end is not null then case
when guf=28 then 7 end else case when guf=32 then 5 end end as
newguffinal
from pnimiot.dbo.michsa
where yearcode=26
) as myMichsa
LEFT join
pnimiotfile.dbo.importfile T2
on t2.semelmosad=mymichsa.semelmosad and
t2.fileyear=mymichsa.yearcode and newguffinal=hasamafactor
inner join pnimiot.dbo.mosad as t3
on t2.semelmosad=t3.semelmosad
where fileyear=26 and leavedate is null
group by mymichsa.semelmosad,
mymichsa.michsaklita,shemmosad,hasamafactor
) as oo
group by semelmosad
order by semelmosad
|
|
November 25th, 2004, 05:13 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2004
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
|
|
try
select oo.semelmosad,
max(case when hasamafactor=7 then michsaklita end ) as mk7,
max(case when hasamafactor=5 then michsaklita end ) as mk5,
sum (michsaklita ) as michsatotal,
max(case when hasamafactor=7 then studs end ) as s7,
max(case when hasamafactor=5 then studs end ) as s5,
sum (studs) as studsTotal,
sum (studs) - sum (michsaklita ) As Balance
from
(
select mymichsa.semelmosad,shemmosad, michsaklita ,hasamafactor,
count(t2.serial_no) as studs
from
(
select semelmosad, michsaklita, yearcode,
case when case when guf=28 then 7 end is not null then case
when guf=28 then 7 end else case when guf=32 then 5 end end as newguffinal
from pnimiot.dbo.michsa
where yearcode=26
) as myMichsa
LEFT join pnimiotfile.dbo.importfile T2
on t2.semelmosad=mymichsa.semelmosad
and t2.fileyear=mymichsa.yearcode
and newguffinal=hasamafactor
inner join pnimiot.dbo.mosad as t3
on t2.semelmosad=t3.semelmosad
where fileyear=26
and leavedate is null
group by mymichsa.semelmosad, mymichsa.michsaklita,shemmosad,hasamafactor
) as oo
group by semelmosad
order by semelmosad
Regards
Ganesh
|
|
November 25th, 2004, 04:36 PM
|
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
thanks, i'll try that.
I have another question about another query.
I don't have much experience therefore i'm struggling.
I have the following query:
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
What I have to do is if the hasamafactor=5 then i want to display only the cases m10-m14. if hasamafactor=7 then i want to display the rest.
I have tried adding if..then but i get syntax errors.
|
|
November 26th, 2004, 01:03 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2004
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
|
|
try this
Code:
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,HASAMAFACTOR
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 or HASAMAFACTOR = 5 then kamut else 0 end ) as m10,
max ( case when misgeret=11 or HASAMAFACTOR = 5 then kamut else 0 end ) as m11,
max ( case when misgeret=14 or HASAMAFACTOR = 5 then kamut else 0 end ) as m14,
max ( case when misgeret=16 or HASAMAFACTOR = 5 then kamut else 0 end ) as m16,
max ( case when misgeret=18 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m18,
max ( case when misgeret=19 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m19,
max ( case when misgeret=21 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m21,
max ( case when misgeret=22 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m22,
max ( case when misgeret=26 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m26,
max ( case when misgeret=28 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m28,
max ( case when misgeret=30 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m30,
max ( case when misgeret=31 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m31,
max ( case when misgeret=32 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m32,
max ( case when misgeret=38 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m38,
max ( case when misgeret=42 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m42,
max ( case when misgeret=51 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m51,
max ( case when misgeret=52 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m52,
max ( case when misgeret=53 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m53,
max ( case when misgeret=54 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m54,
max ( case when misgeret=60 or HASAMAFACTOR <> 5 then kamut else 0 end ) as m60,
max ( case when misgeret=65 or HASAMAFACTOR <> 5 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.semelmosad
group by semelmosad,shemmosad,HASAMAFACTOR
order by hasamafactor,semelmosad
Regards
Ganesh
|
|
|
