Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 24th, 2004, 03:14 PM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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


Reply With Quote
  #2 (permalink)  
Old November 25th, 2004, 05:13 AM
Friend of Wrox
 
Join Date: Jun 2004
Location: Chennai, Tamil Nadu, India.
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

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
Reply With Quote
  #3 (permalink)  
Old November 25th, 2004, 04:36 PM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 89
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Reply With Quote
  #4 (permalink)  
Old November 26th, 2004, 01:03 AM
Friend of Wrox
 
Join Date: Jun 2004
Location: Chennai, Tamil Nadu, India.
Posts: 449
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to r_ganesh76
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtraction in an Access Query pankaj_daga Access 2 April 13th, 2007 06:49 AM
subtraction operator overload arnie6 C++ Programming 0 April 19th, 2005 07:57 AM
Dates Subtraction akhamis SQL Language 1 December 2nd, 2004 04:38 PM



All times are GMT -4. The time now is 04:07 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.