Please help with a single T-sql line...

[selvsagt]

Hi. I am converting a lot of queries to views, and i'm sort of in a gridlock on how to write this line in T-Sql:

Sum(IIf(IsNull([dato]) Or [dato]>[Kursdato],0,[kapitalnedsettelse])) AS kapitalned

In english:
I want the field "kapitalned" to be 0 if the field "date" is null, or greater than the field "kursdato". If its not I want to return a sum of the field kapitalnedsettelse.

I suppose I should use a case statement, but im in a gridlock on how it should be written. Any suggestions? (i have many of lines like this, so i just need a kick in the right direction).

Any help is deeply appreciated!

 

[MSAccessRookie]

In General, The Simplest form of IIf() is similar to what is posted below. A representation for this that is compatible with SQL Server Views or T-SQL follows. Of Course, more complex examples may require more complex resolutions.

-- Rookie

[B]IIf( { Condition to Test }, { [COLOR=seagreen]Value if TRUE[/COLOR] }, { [COLOR=red]Value if FALSE[/COLOR] } )
END [/B]

 [B][COLOR=black]CASE WHEN { Condition to Test }[/COLOR][/B]
 [B][COLOR=black]         THEN { [COLOR=seagreen]Value if TRUE [/COLOR]} 
          ELSE { [COLOR=red]Value if FALSE[/COLOR] } 
END [/COLOR][/B]

 

[selvsagt]

I know the basics, but have problems combining case, OR and isnull....
In access i can "change" the operator, but not in SQL.

I am looking for an example of these three together.

Example in the line underneath, where dato isnull or bigger than kursdato, then kapitalnedsettelse should be zero with the name kapned else it should be sum of the field kapitalnedsettelse...
How can I do the same with t-sql?

In access:

Sum(IIf(IsNull([dato]) Or [dato]>[Kursdato],0,[kapitalnedsettelse])) AS kapitalned

 

[MSAccessRookie]

I know the basics, but have problems combining case, OR and isnull....
In access i can "change" the operator, but not in SQL.

I am looking for an example of these three together.

Example in the line underneath, where dato isnull or bigger than kursdato, then kapitalnedsettelse should be zero with the name kapned.
How can I do the same with t-sql?

In access:

Sum(IIf(IsNull([dato]) Or [dato]>[Kursdato],0,[kapitalnedsettelse])) AS kapitalned

Your MS Access Condition to Test is made up of two Logical results combined with OR. The first is the IsNull, and the second is the IIf(). In the previous response, I addressed the IIf() only,

In MS Access, IsNull uses a single parameter, and returns a Boolean result. In SQL Server, IsNull() requires two arguments, and returns a value consistent with type of the First Parameter, so it may not match to a Boolean (for additional information see below). To get around this replace the MS Access IsNull(Value to test) with If (IsNull(Value to test, ValueifNull)=ValueifNull)

-- Rookie

Example of MS SQL IsNull()

http://msdn.microsoft.com/library/ms184325.aspx

 

[Minty]

I haven't tested this but I think very simply put

kapitalned =
CASE [dato] 
	WHEN null Then 0
	WHEN >[Kursadto] Then 0
ELSE 
	Sum([kapitalnedsettelse])
END

 

[MSAccessRookie]

I haven't tested this but I think very simply put

kapitalned =
CASE [dato] 
    WHEN null Then 0
    WHEN >[Kursadto] Then 0
ELSE 
    Sum([kapitalnedsettelse])
END

If that does not work, you can also try:

SELECT Sum(CASE [dato] 
    WHEN null Then 0
    WHEN >[Kursadto] Then 0
ELSE 
    [kapitalnedsettelse]
 END)AS kapitalned

 

[BlueIshDan]

This makes me miss PL-SQL. Kinda the same ordeal yes? lol

 

[selvsagt]

Thanks guys, you really helped med out. Some small tweaking, and it seem to work now.

Now I have to do this with the remaining "hundred" lines or so

The end result;

CASE WHEN dato > kursdato THEN 0 WHEN dato IS NULL THEN 0 ELSE SUM(kapitalnedsettelse) END AS kapned