CODEWITHSUNDEEP
sqlsql-server-2012
Lalaland
Lalaland

Reputation: 308

SQL to bucket numbers

so i am trying to bucket business days into buckets but I am getting stuck:

IF OBJECT_ID('mattexcel2') IS NOT NULL DROP TABLE mattexcel2 PRINT ' DROP TEMP TABLE'
SELECT * INTO mattexcel2 FROM SA_MASTER_BASE PRINT ' INSERT INTO TEMP TABLE'
GO
ALTER TABLE mattexcel2 ADD [Bucket] NVARCHAR(255) PRINT 'Bucket'
GO
UPDATE mattexcel2 SET [Bucket] = '0-3 Days' where [Business days in current Status] <= 3
GO

When I run this in SQL I get:

Conversion failed when converting the nvarchar value '1.91' to data type int.

So I want 1.9 to fall under my Bucket column as 0-3 Days.

Upvotes: 0

Views: 312

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

As mentioned in the comments, the problem is:

[Business days in current Status] <= 3

Because [Business days in current Status] is a string, it needs to be converted to a number for the comparison. SQL Server chooses an integer. You can fix this by using a decimal place:

UPDATE mattexcel2
    SET [Bucket] = '0-3 Days' 
    WHERE try_convert(decimal(38, 4), [Business days in current Status]) <= 3.0;

However, I would suggest that you use a computed column:

alter table mattexcel2
    add bd_bucket as (case when try_convert(decimal(38, 4), [Business days in current Status]) <= 3.0
                           then '0-3 Days'
                      end);

A computed column will always have the right value, without being updated.

Upvotes: 1

Related Questions