Please help with a single T-sql line... (1 Viewer)

selvsagt

Registered User.
Local time
Today, 07:20
Joined
Jun 29, 2006
Messages
99
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:

Code:
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

AWF VIP
Local time
Today, 02:20
Joined
May 2, 2008
Messages
3,428
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

Code:
[B]IIf( { Condition to Test }, { [COLOR=seagreen]Value if TRUE[/COLOR] }, { [COLOR=red]Value if FALSE[/COLOR] } )
END [/B]
Code:
 [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

Registered User.
Local time
Today, 07:20
Joined
Jun 29, 2006
Messages
99
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:
Code:
Sum(IIf(IsNull([dato]) Or [dato]>[Kursdato],0,[kapitalnedsettelse])) AS kapitalned
 
Last edited:

MSAccessRookie

AWF VIP
Local time
Today, 02:20
Joined
May 2, 2008
Messages
3,428
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:
Code:
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

AWF VIP
Local time
Today, 06:20
Joined
Jul 26, 2013
Messages
10,355
I haven't tested this but I think very simply put
Code:
kapitalned =
CASE [dato] 
	WHEN null Then 0
	WHEN >[Kursadto] Then 0
ELSE 
	Sum([kapitalnedsettelse])
END
 

MSAccessRookie

AWF VIP
Local time
Today, 02:20
Joined
May 2, 2008
Messages
3,428
I haven't tested this but I think very simply put
Code:
kapitalned =
CASE [dato] 
    WHEN null Then 0
    WHEN >[Kursadto] Then 0
ELSE 
    Sum([kapitalnedsettelse])
END

If that does not work, you can also try:
Code:
SELECT Sum(CASE [dato] 
    WHEN null Then 0
    WHEN >[Kursadto] Then 0
ELSE 
    [kapitalnedsettelse]
 END)AS kapitalned
 

selvsagt

Registered User.
Local time
Today, 07:20
Joined
Jun 29, 2006
Messages
99
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;
Code:
CASE WHEN dato > kursdato THEN 0 WHEN dato IS NULL THEN 0 ELSE SUM(kapitalnedsettelse) END AS kapned
 

Users who are viewing this thread

Top Bottom