i want to calculate column value except one condition in sql server?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

SQL

ALTER proc [dbo].[K_RT_DateMeatTallyBasedGrid]
      @sno int
      as
      begin

    select PN.partyname,Round(DT.totalweight,2) as   totalweight,BT.BirdName,DT.rateperkg,DT.dcno,round((DT.totalweight*DT.rateperkg),2)as totalamount from K_RT_Dailyentryretail DT
    inner join  K_RT_PartyName PN on PN.sno=DT.partyname
    inner join K_RT_BirdType  BT on BT.sno=DT.BirdType
    inner join K_RT_WarehouseDetails WD on DT.branchdate=WD.sno
    where DT.branchdate=@sno order by  WD.[date] desc

    end

i want to get "round((DT.totalweight*DT.rateperkg),2)as totalamount" this out put but partyname='ClosingStock', at that time i don't want to calculate how can i write please help me....

Posted 3-Jan-14 20:00pm

santhu888

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2014-01-03T20:05:00

so you want to not include the values from closingstock ? This is not a total, so you can use case:

SQL

select case when partyname = 'ClosingStock' then 0 else round((DT.totalweight*DT.rateperkg),2) end as totalAmount

Just plug that in to your select statement instead of just the calculation you have now

Renuka Ruke · Accepted Answer · 2014-01-03T20:05:00

Solution 2

SQL

use case condition

ALTER proc [dbo].[K_RT_DateMeatTallyBasedGrid]
      @sno int
      as
      begin
 
    select PN.partyname,Round(DT.totalweight,2) as   totalweight,
           BT.BirdName,DT.rateperkg,DT.dcno,
           CASE WHEN partyname='ClosingStock' THEN 0 
           ELSE round((DT.totalweight*DT.rateperkg),2) 
           END AS totalamount 
    from K_RT_Dailyentryretail DT
    inner join  K_RT_PartyName PN on PN.sno=DT.partyname
    inner join K_RT_BirdType  BT on BT.sno=DT.BirdType
    inner join K_RT_WarehouseDetails WD on DT.branchdate=WD.sno
    where DT.branchdate=@sno order by  WD.[date] desc
 
    end

may be it will help u

Posted 3-Jan-14 20:05pm

Renuka Ruke

Comments

Christian Graus 4-Jan-14 2:06am

*grin* same answer, at exactly the same second :-)

Renuka Ruke 4-Jan-14 2:08am

S :)

Christian Graus 4-Jan-14 2:11am

Yours is more copy and paste. I misread the question at first and was writing a CTE based solution.

Renuka Ruke 4-Jan-14 2:18am

wht u mean by CTE based solution

Renuka Ruke 4-Jan-14 2:21am

ohh my answer was accepted actually i am testing my knowledge :)

Christian Graus 4-Jan-14 2:21am

http://www.codeproject.com/Articles/702691/SQL-Wizardry-Part-Three-Common-Table-Expressions-C

Renuka Ruke 4-Jan-14 2:25am

thanks nice one :)