help on calculation including nulls

Hi everybody

I have this sql which calculates the total

MY TABLE IS:

A B C

100 5 3
100 0 5
20 0 0

sql is

A - B + C as total

the result is it gives me only the total of the first row since it has values on the on the B AND C

the result is

A B C total

100 5 3 98
100 0 5
20 0 0

with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...

thanks for any helpRead up on the COALESCE function in the (online) help files.

COALESCE(Field, ValueIfNull)|||Thanks solved my problem and thanks for reminding me on the way of saying thanks in advance..|||you can also use isnull-

select a, b, c, (isnull(A,0) - isnull(B, 0) + isnull(C,0)) as total|||coalesce is generally preferred as it's ANSI. also it can take multiple args.|||Yes thats right, COALESCE have several advantages over ISNULL

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html
http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx (http://http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx)

readability is the only advantage of using ISNULL.|||readability is the only advantage of using ISNULL.

readability? what's more readable:

select ISNULL(a, ISNULL(b, ISNULL(c,d)))

select COALESCE(a,b,c,d)

obviously I prefer coalesce. :)|||IsNull is more readable imo.
Before I learnt of coalesce I had no idea what it meant.|||IsNull is more readable imo.only for two values, and even then it's six of one, six of the other

ISNULL(foo,0)
COALESCE(foo,0)

now try it with multiple expressions, and see if ISNULL is really more readable|||Readability -

Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

this is what databases.aspfaq.com says.

I just wanted to tell alexyeth about the alternative available.
I alway prefer COALESCE.|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

IMO - the fact you don't know what coalesce means is an advantage. Come across it and you have to look it up. Come across ISNULL as a vb programmer and you might think you know what it does.

BOOOOOOOOO to T-SQL ISNULL!|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

I'm pretty sure sql server inherited this function from sybase. doesn't change the fact that ISNULL is a stupid name for the function though. :)|||Goddam Bill lover ;)|||eh, I have a soft spot for sql server, that's all. ;)

help me to get this out put ?

I everybody

I have table Call EmpTran like this

EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL
1 2 15 03/02/2007 NULL
1 2 12 04/03/2007 01/04/2006
2 3 55 01/01/2006 NULL
3 4 53 01/01/2006 NULL
3 4 58 01/01/2006 01/04/2007

SQL for Create Table and values--
create table EmpTran
(
EmpId int,
TranId int ,
Opt int,
S_Date DATETIME,
Ar_Date DATETIME
)

insert into EmpTran values(1,2,1,'01/01/2007',NULL)
insert into EmpTran values(1,2,15,'03/02/2007',NULL)
insert into EmpTran values(1,2,12,'04/03/2007','01/04/2006')
insert into EmpTran values(2,3,55,'01/01/2006',NULL)
insert into EmpTran values(3,4,53,'01/01/2006',NULL)
insert into EmpTran values(3,4,58,'01/01/2006','01/04/2007')

No i am bit stuck when applying these condition in to this table and pupulate the out put
so my conditions is like this
I need to find out a EmpId,TranId,Opt,S_Date,Ar_Date where Ar_Date Is Null
and then if a one employee is get reapeated morethan once and if that EmpId got Ar_Date then i need to get earliest Ar_Date in that emp_Id and check that Ar_Date is less than the S_date of that Perticular Row then display the values

bellow i explained by each row data condition,

EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL 'Should display,cos Ar_date is null + Max Ar_date is <S_Date
1 2 15 03/02/2007 NULL 'Should display,cos Ar_date is null + Max Ar_date is <S_Date
1 2 12 04/03/2007 01/04/2006 'should not include cos this got Ar_date
2 3 55 01/01/2006 NULL 'Should not include this cos EmpId not repeated
3 4 53 01/01/2006 NULL 'should not get this row,cos Ar_date is > S_Date
3 4 58 01/01/2006 01/04/2007 'should not include cos this got Ar_date

so after all these requirement satisfy my table should looks like this
EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL
1 2 15 03/02/2007 NULL

any idea ?

regards
suis

If you use sql server 2005,

Code Snippet

Create Table #EmpTran (

EmpId int,

TranId int ,

Opt int,

S_Date DATETIME,

Ar_Date DATETIME

)

insert into #EmpTran values(1,2,1,'01/01/2007',NULL)

insert into #EmpTran values(1,2,15,'03/02/2007',NULL)

insert into #EmpTran values(1,2,12,'04/03/2007','01/04/2006')

insert into #EmpTran values(2,3,55,'01/01/2006',NULL)

insert into #EmpTran values(3,4,53,'01/01/2006',NULL)

insert into #EmpTran values(3,4,58,'01/01/2006','01/04/2007')

;With CTE

as

(

Select

*

, Max(Ar_Date) Over (Partition By EmpId) Max_Ar_Date

, Count(EmpId) Over(Partition By EmpId) Count_EmpId

From

#EmpTran

)

Select

EmpId

, TranId

, Opt

, S_Date

, Ar_Date

from

CTE

Where

Count_EmpId > 1 And Ar_Date is NULL And S_Date > Max_Ar_Date

If you are using Sql Server 2000,

Code Snippet

Select

EmpId

, TranId

, Opt

, S_Date

, Ar_Date

From

#EmpTran Main

Join (Select

EmpId MaxEmpId,

Max(Ar_Date) Max_Ar_Date,

Count(EmpId) Count_EmpId

From #EmpTran

Group By EmpId) as MaxData On MaxData.MaxEmpId = Main.EmpID

Where

Count_EmpId > 1

And Ar_Date is NULL

And S_Date > Max_Ar_Date

|||Hi i have a small question out of that SQL ?
my question is what is the reason u start to write u r sql using

;With CTE

as

?

Whats that mean ?

I am using Sql server 2000 ? not 2005 ?

can i write the same sql in sql2000 ?

regards

Suis

|||

This is one of the new feature in SQL Server 2005 called Common Table Expression.

You can use derived table also here. But I like to use CTE; it has more advantages than Derived tables.

|||Hi
Problem is i am using sql server 2000 ?
how can i write the same sql in SQL server 2000 ?
regards
suis|||check my first post i edited it; you can get the query for sql server 2000 also.|||hi
sorry i didnt read the bottom part of u r comments,
i will try the sql server 2000 part and let u know the result
thanks
regards
suis|||Hi kent,
According to my data SQL looks alright ? i got the correct result !
thanks
regards
suis