CODEWITHSUNDEEP
sql-server
Serge Kashlik
Serge Kashlik

Reputation: 413

SQL IF/ CASE statement

I am fairly new to SQL and I can't figure out what to do here.

I have a database of financial data with one column being [Days]. I need to add a new column into it which will add a category in which the number of days fall into (0, 1-30, 30-60 etc).

In excel this would look like this =IF(A1>90,"90-120",IF(A1>60,"60-90".......)

The final database should look like this:

Days  |  Category
29    |    0-30
91    |   90-120
0     |     0 
.
.
.

Thx in advance

Upvotes: 0

Views: 185

Answers (6)

STLDev
STLDev

Reputation: 6154

One solution to your dilemma may be to insert a new database column that uses a SQL Server feature known as a "Computed Column Specification" into your table.

A Computed Column Specification is a method whereby a database column's value can be calculated when the row is updated. That value can be optionally also be persisted in the database so that when it is queried no calculation has to be performed at that time (just on the INSERT).

I like this solution because you don't have to do any special calculations upon querying the data. You'll pull the new column data with a simple SELECT.

You didn't list specifics, so let's suppose that your database table is named [FinancialData], and that it has defined in it a column named [Days] that is of some numeric type (int, smallint, tinyint, decimal, float, money, numeric, or real).

You can add the computed column as follows:

ALTER TABLE [FinancialData] ADD
    Category AS (CASE WHEN [Days] >= 90 THEN '90-120'
                      WHEN [Days] >= 60 THEN '60-90'
                      WHEN [Days] >= 30 THEN '30-60'
                      WHEN [Days] >= 1  THEN '1-30'
                      WHEN [Days] = 0 THEN '0'
                 END) PERSISTED;

Note the word "PERSISTED" in the SQL statement above. This is what causes the database table to actually store the calculated value in the database table when the [Days] column is inserted or changed. If you don't want to store the value, simply leave out the word "PERSISTED".

When the computed column is added to the table by executing the SQL statement above, values will be computed and stored for all existing rows in the table. When inserting a new row into the table, do not supply a value for the new [Category] column. This is because a) it won't work, and b) that column's value will be computed from the [Days] column value.

To retrieve data from the new column, you simply list that column in the SELECT statement (or use *):

SELECT [Days], [Category]
    FROM [FinancialData];

A couple of caveats to note: 1) This is SQL Server specific. Most other database engines have no support for this feature. 2) You didn't state whether the [Days] column is nullable - if so, this solution will have to be modified to support that.

Upvotes: 0

Nolan Shang
Nolan Shang

Reputation: 2328

select *,number/30, ltrim(number/30*30)+'-' +ltrim((number/30+1)*30) from #Numbers

+--------+---+-------+
| Number |   |       |
+--------+---+-------+
| 1      | 0 | 0-30  |
| 0      | 0 | 0-30  |
| 15     | 0 | 0-30  |
| 29     | 0 | 0-30  |
| 32     | 1 | 30-60 |
| 54     | 1 | 30-60 |
| 59     | 1 | 30-60 |
| 60     | 2 | 60-90 |
| 63     | 2 | 60-90 |
+--------+---+-------+

Upvotes: 0

Sahi
Sahi

Reputation: 1484

try this

    create table #tmp ([Days] int)

    insert into #tmp values (29)
    insert into #tmp values (91)
    insert into #tmp values (0)
    insert into #tmp values (65)

    SELECT 
        CASE WHEN [Days]=0 then CONVERT(VARCHAR(15),0)
             ELSE CONVERT(VARCHAR(15),[Days]/30*30)+'-'+ CONVERT(VARCHAR(15),([Days]/30*30)+30) END AS Category     
    from #tmp

    drop table #tmp

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

Here is another way using IIF if you are using SQL Server 2012+:

CREATE TABLE Numbers (Number INT );

INSERT INTO Numbers VALUES
(1),
(0),
(15),
(29),
(32),
(54),
(59),
(60),
(63),
(89),
(90),
(140);

SELECT IIF(Number BETWEEN 90 AND 120, '90-120', 
           IIF(Number BETWEEN 60 AND 89, '60-90', 
               IIF(Number BETWEEN 30 AND 59 , '30-60' , 
                  IIF(Number BETWEEN 1 AND 29, '1-30' , 
                      IIF(Number = 0, '0', 'OutRange'))))) AS Category
FROM Numbers;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use case:

select days,
       (case when days > 90 then '90-120'  -- should this be >= ?
             when days > 60 then '60-90'   -- should this be >= ?
             . . .
        end) as Category
from t;

Upvotes: 2

sacse
sacse

Reputation: 3744

Complete SQL:

select Days,
       (case when days > '90' then '91-120'
             when days > '60' then '61-90'
             when days > '30' then '31-60' 
             when days > '0' then '1-30' else '0' end
        end) as Category
from t;

Upvotes: 0

Related Questions