Specific condition after 'THEN' in a Case Statement Table Update

sqlglobal · July 31, 2020, 11:22am

I need to update a temp table making use of a Case statement but there is a specific condition to use as part of the THEN.

UPDATE tbl
SET
ABC = Case when Days >= 1 and Days < 7 THEN Rate
when Days >= 7 and Days < 14 THEN Rate

in both cases the Rate needs to be used for ABC but in the Rate table each rate has a different Rate_Type i.e. '1WEEK' and '2WEEK'. In the first Case I need the '1WEEK' Rate and in the second Case I need the '2WEEK' Rate.

The Rate table looks like:
RateDate Ccy Rate Rate_Type
10/05/2019 EUR 1.2312 1WEEK
10/05/2019 EUR 1.1457 2WEEK

How do I need to do the case statement to distinguish between the 1WEEK and 2WEEK Rate value ?

harishgg1 · July 31, 2020, 11:42am

hi

all depends on WHAT basis you want to choose 1WEEK or 2WEEK

from what you have said its not clear .... !!!

this looks "very very" simple to do ...

please provide ..Sample data ... with realistic data ... with ddl and dml ..
for the 2 tables in question

example ..
Example = Create Table

sqlglobal · July 31, 2020, 12:10pm

'Spot' table with the applicable rates:

The basis to be used to determine the Rate_Type to use is the number of Days_to_Maturity

harishgg1 · July 31, 2020, 12:35pm

for ... some one looking at this its not clear !!! at least me !! again i have to ask questions !!

how to make it "simple and easy" for others to understand what you are saying
is a PHD subject by itself !!!

what do you want to do ? ...
where are " all the elements " of what you want to do ???

if i am not able to make sense .. Please excuse me !!!

sqlglobal · July 31, 2020, 1:12pm

So in my case statement if the 'Days_to_maturity' is between 0 and 7 days then I need to extract the "7DAY" Rate from the 'Spot table'. If the 'Days_to_maturity' is between 7 and 30 days then I need to extract the "1MNTH" Rate. Lastly, when the 'Days_to_maturity' is between 30 and 60 days then I need to extract the "2MNTH" Rate

You can see below example the 3 rates are different for this specific USD & EUR combination for 7DAY, 1MNTH and 2MNTH hence this Case statement to extract the correct rate.

mike01 · July 31, 2020, 1:12pm

you'll probably need to put max around that update to get the right values. But without DDL and sample data, we're just guessing. The join condition is where the change would need to be to make it more efficient and correct, but you need to help us help you

mike01 · July 31, 2020, 1:34pm

Here's a stab at what I think you are looking for

IF OBJECT_ID('tempdb..#ResultsFX') IS NOT NULL 
    DROP TABLE #ResultsFX

IF OBJECT_ID('tempdb..#SpotRates') IS NOT NULL 
    DROP TABLE #SpotRates

Create table #ResultsFX (Days_To_Maturity int,
						 BaseCurrency char(3),
						 SellCurrency  char(3),
						 Daily_dif_Rate1 numeric(12,8))

Create table #SpotRates (RateDate date,
						 BaseCCY char(3),
						 RateCCY char(3),
						 MDInd char(1),
						 Rate numeric(12,8),
						 RateType varchar(10))

insert into #ResultsFX values
(6, 'COP','BGN', null),
(7, 'COP','BGN', null),
(30, 'COP','BGN', null),
(70, 'COP','BGN', null),
(6, 'INR','PEN', null),
(7, 'INR','PEN', null),
(30, 'INR','PEN', null),
(70, 'INR','PEN', null)

insert into #SpotRates values
('7/30/2020','COP','BGN','M',0.000438990,'7Day'),
('7/30/2020','COP','BGN','M',0.074353949,'2MNTH'),
('7/30/2020','COP','BGN','M',1.000247719,'1MNTH'),
('7/30/2020','COP','BGN','M',1.045677901,'3MNTH'),
('7/30/2020','INR','PEN','M',0.046835326,'7Day'),
('7/30/2020','INR','PEN','M',0.025950510,'2MNTH'),
('7/30/2020','INR','PEN','M',4.946421514,'1MNTH'),
('7/30/2020','INR','PEN','M',4.100394893,'3MNTH')


select r.BaseCurrency, r.SellCurrency, r.Days_To_Maturity, s.rate			
from #ResultsFX r
	join #SpotRates s
		on r.BaseCurrency = s.BaseCCY
		and r.SellCurrency = s.RateCCY
		and s.RateType = Case when r.Days_To_Maturity >= 0 and r.Days_To_Maturity < 7 
									then '7Day' 
							  when r.Days_To_Maturity >= 7 and r.Days_To_Maturity < 30
									then '1MNTH'
							  when r.Days_To_Maturity >= 30 and r.Days_To_Maturity < 60
									then '2MNTH'
						 end
							


select r.BaseCurrency, r.SellCurrency, r.Days_To_Maturity, 
			max(Case when r.Days_To_Maturity >= 0 and r.Days_To_Maturity < 7 and s.RateType = '7Day'
									then s.rate
							  when r.Days_To_Maturity >= 7 and r.Days_To_Maturity < 30 and s.RateType = '1MNTH'
									then s.rate
							  when r.Days_To_Maturity >= 30 and r.Days_To_Maturity < 60 and s.RateType = '2MNTH'
									then s.rate
						 end) as Rate	
from #ResultsFX r
	join #SpotRates s
		on r.BaseCurrency = s.BaseCCY
		and r.SellCurrency = s.RateCCY
		and s.RateType in ('7Day','1MNTH', '2MNTH')
Group by r.BaseCurrency, r.SellCurrency, r.Days_To_Maturity

harishgg1 · July 31, 2020, 2:01pm

i hope this helps and is Clear .. please let me know .. I can explain it in a much easier way ...

i don't think you need a case statement !!! just join should do it ....

create a temp table with mapping

Now join

select 
    a.rate 
from 
      RatesTable  a  
         join 
     TempTable b 
                on a.RateType = b.RateType 
        join 
    Table_Days c 
               on c.Days_To_Maturity between b.BeginDays and b.Endays

sqlglobal · July 31, 2020, 2:31pm

Thank you all, as you pointed out I got it to work by removing the case statement and changing to join to allow for the different Rate_Types.

jeffw8713 · July 31, 2020, 5:36pm

I know this is a little late - but there are a couple of other options:

Use a CTE and pivot/cross-tab the rate table - then join to the pivot/cross-tab version. This creates a different column for each rate type based on the other columns to be joined.
Use multiple joins - one for each rate type needed.
Use OUTER APPLY to pull the specific rate type based on the current rows values.