Incrementing case statement problem

Transact-SQL
1

Hi
I have googled but cannot find an unswer to this. I am probably googling the wrong thing :slight_smile:
I have a field and each time it increments by a specified value the results increments
So days between 0.5 and 2 returns the value 0.5, between 2.1 and 4 returns 1.00, between 4.1 and 6 returns 1.5
How do I write this without having to create a line for every value. Any help would be appreciated.
Case
when days <= 2 then 0.5
when days <=4 then 1.0
when days <= 6 then 1.5
when days <= 8 then 2.00
end as Due_Days,

2

(ceiling(days)+ceiling(days)%2)*.25

1 Like
3

Thank you

4

hi

i am trying to do this ... with a different approach ...
this may have performance benefits !!!

Instead of case statement
I created a look up table and joined that !!!

Hope it helps :slight_smile: :slight_smile:

please click arrow to the left for DROP Create SAMPLE Data 
drop table #data 
go 

create table #data 
(
days decimal(10,2)
)
go 

insert into #data select 2.3 
insert into #data select 5.0
insert into #data select 10.5
insert into #data select 6.0
insert into #data select 3.1
insert into #data select 6.3
insert into #data select 7.7

insert into #data select 8.2
insert into #data select 2.0
insert into #data select 2.0
insert into #data select 4.2
insert into #data select 5.5
insert into #data select 1.5
insert into #data select 1.3
go 

select  'Sample Data'
       , *
from 
  #data

image

5

Look Up Table

please click arrow to the left for LOOKUP Table Script 
drop table #lookup 
go 

create table #lookup
(
start_days decimal(10,2) ,
end_days   decimal(10,2) ,
value      decimal(10,2) 
)
go 

insert into #lookup select 0.5 , 2 , 0.5
insert into #lookup select 2.1 , 4 , 1.00
insert into #lookup select 4.1 , 6 , 1.5
insert into #lookup select 6.1 , 8 , 2.00
go 

select 
      'LookUp Table '
	 , * 
from 
   #lookup

image

6

There is a difference between mine and BitsMed

I have to add a row in LookUp Table
for > 8
greater than 8 condition

please click arrow to the left for BITSMed Solution SQL 
select    'BitsMed Solution'
        , *
		, (ceiling(days)+ceiling(days)%2.0)*0.25 
from 
    #data
go

image

please click arrow to the left for SQL Mine 
select  'SQL '
       , a.*
	   , b.value 
from 
    #data a 
	   join 
    #lookup b 
	    on 
	 	  a.days between b.start_days and b.end_days

image