Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Challenging question ... 2

Status
Not open for further replies.
hokky

hokky

Technical User
Nov 9, 2006
170
AU
Hi guys,

Can you help me give me the idea of my problem please ?
first,

I got the input like this from the table (
Code: 
dateTime     curve   data
20/11/2006   AUD10D  10
20/11/2006   AUD25D  20
20/11/2006   AUD50D  30
20/11/2006   AUD75D  40
20/11/2006   AUD90D  50
21/11/2006   AUD10D  300
21/11/2006   AUD25D  400
21/11/2006   AUD50D  500
21/11/2006   AUD75D  600
21/11/2006   AUD90D  700
...

We also got formula to produce the output
Value of Vol10=0.01*(AUD10D+AUD50D)
Value of Vol25=0.01*(AUD25D+AUD50D)
Value of Vol50=0.01*(AUD50D)
Value of Vol75=0.01*(AUD75D+AUD50D)
Value of Vol90=0.01*(AUD90D+AUD50D)


So the output I'm expecting is
Code: 
dateTime     curve    vol     value from the formula
20/11/2006   AUD10D   10          0.4
20/11/2006   AUD10D   25          0.5
20/11/2006   AUD10D   50          0.3
20/11/2006   AUD10D   75          0.7
20/11/2006   AUD10D   90          0.8
21/11/2006   AUD10D   10           8
21/11/2006   AUD10D   25           9
21/11/2006   AUD10D   50           5
21/11/2006   AUD10D   75           11
21/11/2006   AUD10D   90           12
...

So the main question is how to store all the data in array in different date because the date range is going to be hundreds so i can't put it in temp table.. too much..

Guys, please throw some ideas here.

Thanks.
 
Sort by date Sort by votes
Code: 
SELECT dateTime,
       curve,
       Vol, --- ????????? from where that field comes from
       SUM(CASE WHEN Vol = 10 AND curve IN ('AUD10D','AUD50D')
                     THEN Data
                WHEN Vol = 25 AND curve IN ('AUD25D','AUD50D')
                     THEN Data
                WHEN Vol = 50 AND curve  = 'AUD50D'
                     THEN Data
                WHEN Vol = 75 AND curve IN ('AUD75D','AUD50D')                                        
                     THEN Data
                WHEN Vol = 90 AND curve IN ('AUD90D','AUD50D')
                ELSE 0
           END * 0.01 AS [value from the formula]
FROM YourTable
GROUP BY dateTime,
         curve,
         Vol

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
Is there something else you are not telling us?
Code: 
set dateformat dmy
create table #t1 (
dt dateTime , curve char(6), data int) insert into #t1 select
'20/11/2006','AUD10D'      ,10         union all select
'20/11/2006','AUD25D'      ,20         union all select
'20/11/2006','AUD50D'      ,30         union all select
'20/11/2006','AUD75D'      ,40         union all select
'20/11/2006','AUD90D'      ,50         union all select
'21/11/2006','AUD10D'      ,300        union all select
'21/11/2006','AUD25D'      ,400        union all select
'21/11/2006','AUD50D'      ,500        union all select
'21/11/2006','AUD75D'      ,600        union all select
'21/11/2006','AUD90D'      ,700

select a.dt,'AUD10D' curve, substring(b.curve,4,2) vol
,0.01*case when a.curve=b.curve then a.data else (a.data+b.data) end value
from #t1 a join #t1 b
 on  a.dt=b.dt
 and a.curve='AUD50D'
order by 1,3

drop table #t1
 
Upvote 0 Downvote
yeah guys..

I tried to simplified my question...
but thanks for the idea..

I'll get back to you guys if im stuck..

Thx.
 
Upvote 0 Downvote
Where does the curve column come from on the second dataset? Is it always AUD10D?
 
Upvote 0 Downvote
no,

The format supposed to be
Code: 
dateTime     curve    vol     value from the formula
20/11/2006   AUD10D   10          0.4
20/11/2006   AUD25D   25          0.5
20/11/2006   AUD50D   50          0.3
20/11/2006   AUD75D   75          0.7
20/11/2006   AUD90D   90          0.8
21/11/2006   AUD10D   10           8
21/11/2006   AUD25D   25           9
21/11/2006   AUD50D   50           5
21/11/2006   AUD75D   75           11
21/11/2006   AUD90D   90           12
...

The problem is actually much more complicated than that, but I don't want you guys doing my job. I just need the advice and idea.. Thanks guys...

ps : btw bborissov, your solution doesn't work. The result didn't show the sum function, they only grabbing each data.
 
Upvote 0 Downvote
PDreyer's code works great (although you have to switch the curve displayed to give the correct one since your first requirements were incorrect).

And if you have a clustered index on (dt, curve) you'll get a nice merge join instead of a hash join.

PDreyer's inner join method proves to be superior to a left join because it costs less to calculate the case statement with an inner join, than to use a left join with the more complicated join logic that entails.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

The best part about anything that has cheese is the cheese.[/color]
 
Upvote 0 Downvote
Ok guys,

I am getting stucked so I think I will try to explain my whole problem here.

first the input table :
Code: 
dateTime	           curve	                            data
10/19/2007 0:00	CURRENCY10DELTA10Y                      	0.8
10/19/2007 0:00	CURRENCY10DELTA12M                      	1.15
10/19/2007 0:00	CURRENCY10DELTA1M                       	0.85
10/19/2007 0:00	CURRENCY10DELTA1W                       	1
10/19/2007 0:00	CURRENCY10DELTA2M                       	0.9
10/19/2007 0:00	CURRENCY10DELTA2Y                       	1.244444
10/19/2007 0:00	CURRENCY10DELTA3M                       	0.95
10/19/2007 0:00	CURRENCY10DELTA3Y                       	1.244444
10/19/2007 0:00	CURRENCY10DELTA4Y                       	1.244444
10/19/2007 0:00	CURRENCY10DELTA5Y                       	1.5
10/19/2007 0:00	CURRENCY10DELTA6M                       	1.05
10/19/2007 0:00	CURRENCY10DELTA6Y                       	1.029347
10/19/2007 0:00	CURRENCY10DELTA7Y                       	0.65
10/19/2007 0:00	CURRENCY10DELTA8Y                       	0.707761
10/19/2007 0:00	CURRENCY10DELTA9M                       	1.11025
10/19/2007 0:00	CURRENCY10DELTA9Y                       	0.757144
10/19/2007 0:00	CURRENCY10Y                             	9
10/19/2007 0:00	CURRENCY12M                             	10.8
...

periode table
Code: 
Days	periodeTime
7	1W
30	1M
61	2M
…	…

PeriodeTime
1W = 1 Week = 7 days

and we have 5 deltas which are delta (10,25,50,75,90)

The formula of value :
Vol of delta 10 = 0.01*(CURRENCY10DELTA - CURRENCY90DELTA)+...
Vol of delta 25 = 0.01*(CURRENCY25DELTA - CURRENCY75DELTA)+...
Vol of delta 50 = 0.01*(CURRENCY50DELTA)+...
Vol of delta 75 = 0.01*(CURRENCY75DELTA - CURRENCY25DELTA)+...
Vol of delta 90 = 0.01*(CURRENCY90DELTA - CURRENCY10DELTA)+...

The curve format in input table is :
CURRENCY+[DELTA]+[PeriodeTime]

if DELTA doesn't exist such as "CURRENCY10Y" means CURRENCY50DELTA10Y

Finally the output we expect is
Code: 
dateTime	Periode	delta	Value of formula
10/19/2007	7	10	…
10/19/2007	7	25	…
10/19/2007	7	50	…
10/19/2007	7	75	…
10/19/2007	7	90	…
10/19/2007	30	10	…
10/19/2007	30	25	…
10/19/2007	30	50	…
10/19/2007	30	75	…
10/19/2007	30	90	…
10/19/2007	…	…	
10/19/2007	3652	10	
10/19/2007	3652	25	
10/19/2007	3652	50	
10/19/2007	3652	75	
10/19/2007	3652	90	…
10/20/2007	7	10	…
10/20/2007	7	25	…
10/20/2007	7	50	…
10/20/2007	7	75	…
10/20/2007	7	90	…
10/20/2007	30	10	…
10/20/2007	30	25	…
10/20/2007	30	50	…
10/20/2007	30	75	…
10/20/2007	30	90	…
10/20/2007	…	…	…
10/20/2007	3652	10	…
10/20/2007	3652	25	…
10/20/2007	3652	50	…
10/20/2007	3652	75	…
10/20/2007	3652	90	…

I have tried until using 3 while loops but still the result is not satisfied, please share some ideas.

Much appreciated, Thanks.
 
Upvote 0 Downvote
Please elaborate on what ... is in the formula/expected output
 
Upvote 0 Downvote
First of all, you should stop storing multiple attributes in a single column. You are right now experiencing the excruciating pain that doing something like this in a database always causes.

Your curve column is nice for display on a report or something... maybe. But in the database it becomes silly to work with a multi-valued column. So before you try ANYTHING else, split out your input table. If you can fix it so the original data collection does this, that is best.

Example:
Currencydatetime '10/19/2007 0:00'
Periode 30
Delta 10
Data 0.85

Code: 
SELECT
   CurrencyDatetime, -- a column named 'datetime' is not best practice
   PeriodeDays = P.Days,
   Delta = Coalesce(D.Value, 50),
   Data
FROM
   InputTable I
   INNER JOIN Periode P ON Right(I.curve, Len(P.periodeTime)) = P.periodeTime
   LEFT JOIN (
      SELECT 10
      UNION ALL SELECT 25
      UNION ALL SELECT 50
      UNION ALL SELECT 75
      UNION ALL SELECT 90
   ) D (Value) ON I.curve LIKE '%' + Convert(varchar(2), D.Value) + 'DELTA%'
Now you have your data in an even beginning reasonable form to do queries on.

P.S. For what it's worth, by oversimplifying your question, a bunch of people spent a fair amount of completely wasted time solving the wrong problem. Please try to be more specific in the future and not assume that simplifying is okay.
 
Upvote 0 Downvote
The join could also be

INNER JOIN Periode P ON I.curve LIKE '%' + P.periodeTime
 
Upvote 0 Downvote
Thanks Esquared for your advice,

but your code still didn't work.
The result is empty

CurrencydateTime PeriodeDays Delta Data

 
Upvote 0 Downvote
You're right my code is a complete failure although it might give you some ideas.

How does the days value from the periode table relate to the desired result set?
 
Upvote 0 Downvote
No, my code was perfect-- to give you a starting place to even begin your query. It wasn't supposed to be the final query. But before I go there, I have questions.

In your formula, why do you have "..." in there?

Vol of delta 10 = 0.01*(CURRENCY10DELTA - CURRENCY90DELTA)+...
Vol of delta 25 = 0.01*(CURRENCY25DELTA - CURRENCY75DELTA)+...
Vol of delta 50 = 0.01*(CURRENCY50DELTA)+...
Vol of delta 75 = 0.01*(CURRENCY75DELTA - CURRENCY25DELTA)+...
Vol of delta 90 = 0.01*(CURRENCY90DELTA - CURRENCY10DELTA)+...

Let's say one has a CURRENCY10DELTA10Y as the main record. Does this pair with a CURRENCY90DELTA10Y? Or does it match with all the CURRENCY90DELTAs? How does one handle multiple hits? What if that record is missing?

I've been trying to get you a solution and I have to say that the way you have 2 discrete pieces of data in one column is very unpleasant. (The way curve combines period with delta is an absolute nightmare of nastiness!) Plus being able to leave out the delta value when it's 50... yuck.

Plus I also have to say that your sample data was badly incomplete. It didn't give me a set to actually work with. I had to massage it to get anything out of it (for example, you gave me only DELTA10s and no matching DELTA90s. Next time, please do this work yourself to provide data that will actual help those helping you to give you an answer, instead of making us do this work for you.

Code: 
create table #badinput (
   currencydatetime datetime not null,
   curve varchar(50) not null,
   data decimal (12,10) not null,
   primary key clustered (currencydatetime, curve)
)
insert #badinput values ('20071019 0:00', 'currency10delta10y', 0.8)
insert #badinput values ('20071019 0:00', 'currency10delta12m', 1.15)
insert #badinput values ('20071019 0:00', 'currency10delta1m', 0.85)
insert #badinput values ('20071019 0:00', 'currency10delta1w', 1)
insert #badinput values ('20071019 0:00', 'currency10delta2m', 0.9)
insert #badinput values ('20071019 0:00', 'currency10delta2y', 1.244444)
insert #badinput values ('20071019 0:00', 'currency10delta3m', 0.95)
insert #badinput values ('20071019 0:00', 'currency10delta3y', 1.244444)
insert #badinput values ('20071019 0:00', 'currency10delta4y', 1.244444)
insert #badinput values ('20071019 0:00', 'currency10delta5y', 1.5)
insert #badinput values ('20071019 0:00', 'currency10delta6m', 1.05)
insert #badinput values ('20071019 0:00', 'currency10delta6y', 1.029347)
insert #badinput values ('20071019 0:00', 'currency10delta7y', 0.65)
insert #badinput values ('20071019 0:00', 'currency10delta8y', 0.707761)
insert #badinput values ('20071019 0:00', 'currency10delta9m', 1.11025)
insert #badinput values ('20071019 0:00', 'currency10delta9y', 0.757144)
insert #badinput values ('20071019 0:00', 'currency10y', 9)
insert #badinput values ('20071019 0:00', 'currency12m', 10.8)
-- have to create corresponding records to be able to see much of anything
insert #badinput select currencydatetime, replace(curve, 'currency10delta', 'currency90delta'), data * 1.02 from #badinput where curve like '%currency10delta%'

-- construct a reasonable periode table
create table #periode (
   days int not null,
   periodetime varchar(5) not null,
   primary key clustered (periodetime)
)
insert #periode values (7, '1w')
insert #periode values (30, '1m')
insert #periode values (61, '2m')
insert #periode (days, periodetime)
select distinct 0, replace(right(curve, 3), 'a', '') from #badinput i where not exists (select * from #periode p where p.periodetime = replace(right(curve, 3), 'a', ''))
update #periode set days = datediff(dd, getdate(), dateadd(mm, convert(int, replace(periodetime, 'm', '')), getdate())) where periodetime like '%m%' and days = 0
update #periode set days = datediff(dd, getdate(), dateadd(yy, convert(int, replace(periodetime, 'y', '')), getdate())) where periodetime like '%y%' and days = 0

-- fix combined columns problem
[b]select
   currencydatetime,
   periodedays = p.days,
   delta = coalesce(d.value, 50),
   data
into
   #input
from
   #badinput i
   inner join #periode p on right(i.curve, len(p.periodetime)) = p.periodetime
   left join (
      select 10
      union all select 25
      union all select 50
      union all select 75
      union all select 90
   ) d (value) on i.curve like '%' + convert(varchar(2), d.value) + 'delta%'[/b]

select
   i1.currencydatetime,
   periode = i1.periodedays,
   delta = i1.delta,
   valueofformula = (i1.data - coalesce(i2.data, 0)) / 100
from
   #input i1
   left join #input i2 on
      i1.delta = 100 - i2.delta
      and i1.periodedays = i2.periodedays
      and i1.delta <> 50 
order by
   i1.currencydatetime,
   periode

I know this is still not perfect because there is some mysterious thing about getting multiple days out of only one day's input. I suspect that you want the data to be repeated for as many days as the periodedays value, but what if your input data has more than one date in it? What then? There is much left unexplained that I have been forced to guess at here. ([red]Hint hint hint: please explain in FULL detail if you would like more help.[/red])

P.S. The part in bold in the code above is the exact query I gave you earlier (adding an into clause). It works just fine! Amazing.

D is the alias of the derived table I created inside the parentheses. You have to give the derived table some sort of name or there'd be no way to reference the thing.
 
Upvote 0 Downvote
Hi Esquared,

I have massage the data, I know the raw data looks suck but that's the data that I'm going to get man, nothing I can do about it.
The Formula I put "..." means, they are still continuing.

There is not such pair thing, we need to match via the formula, it shouldn't be multiple hits and shouldn't be missing, if it's missing or multiple hits, we should send the error message.

Here's the data after I massage them
Code: 
CurrencyDateTime        Currency        Delta        Tenor        Periode        Data 
22/10/2007 0:00           AUDUSD              10        1W         7                    1 
22/10/2007 0:00           AUDUSD              25        1W         7                    0.275 
22/10/2007 0:00           AUDUSD              50        1W         7                    16.8 
22/10/2007 0:00           AUDUSD              75        1W         7                    1.8 
22/10/2007 0:00           AUDUSD              90        1W         7                    3.3 
22/10/2007 0:00           AUDUSD              10        1M         30                    0.85 
22/10/2007 0:00           AUDUSD              25        1M         30                    0.25 
22/10/2007 0:00           AUDUSD              50        1M         30                    14.4 
22/10/2007 0:00           AUDUSD              75        1M         30                    2.1 
22/10/2007 0:00           AUDUSD              90        1M         30                    3.8 
...


I just put the sample the periode over 7 days and 30 days..
we should be able to apply to the rest.

The formula is :
Code: 
Value of Vol (Periode,Delta)

let's take example periode 7 days.

-- Value of Vol(7,10 Delta)
=0.01*(data[Currency50Delta]+data[Currency10Delta]+0.5*-1*data[Currency90Delta])
-- So, from the table data above, we can get the result
=0.01*(16.8+1+0.5*-1*3.3) = [b]0.1615[/b]

-- Value of Vol(7,25 Delta)
=0.01*(data[Currency50Delta]+data[Currency25Delta]+0.5*-1*data[Currency75Delta])
-- So, from the table data above, we can get the result
=0.01*(16.8+0.275+0.5*-1*1.8) = [b]0.16175[/b]

-- Value of Vol(7,50 Delta)
=0.01*(data[Currency50Delta]

-- Value of Vol(7,75 Delta)
=0.01*(data[Currency50Delta]+data[Currency25Delta]-0.5*-1*data[Currency75Delta])

-- Value of Vol(7,90 Delta)
=0.01*(data[Currency50Delta]+data[Currency10Delta]-0.5*-1*data[Currency90Delta])

So from formula above and the input data, we will get the output as follow :

Code: 
CurrencyDateTime	Periode	Delta	Value of Formula
22/10/2007	7	0.1	0.1615
22/10/2007	7	0.25	0.16175
22/10/2007	7	0.5	0.168
22/10/2007	7	0.75	0.17975
22/10/2007	7	0.9	0.1945
22/10/2007	30	0.1	0.1335
22/10/2007	30	0.25	0.136
22/10/2007	30	0.5	0.144
22/10/2007	30	0.75	0.157
22/10/2007	30	0.9	0.1715

I just got the idea using match the value using same table comparison like (let's say input table called inputtable)

select ...

from inputtable I1, inputtable I2
where I1.periode = I2.periode

But I'm just not sure how to apply the formula into the record by grabbing 3 values..
say like we want to fill the record "Value of formula" for periode 7 delta 10 we need to grab :
data[10delta],data[50delta],data [90delta] for the same periode.

I need someone to nail this, I believe you are the expert can nail it..

Thanks man, you're so kind..
 
Upvote 0 Downvote
Code: 
set dateformat dmy
create table #massage(
CurrencyDateTime datetime,Currency char(6),Delta int,Tenor char(2),Periode int,Data decimal(13,6)) /*
------------------------- ---------------- --------- ------------- ----------- ------------------  */ insert into #massage select
'22/10/2007 0:00'        ,'AUDUSD'        ,10       ,'1W'         ,7          , 1           union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,25       ,'1W'         ,7          , 0.275       union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,50       ,'1W'         ,7          ,16.8         union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,75       ,'1W'         ,7          , 1.8         union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,90       ,'1W'         ,7          , 3.3         union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,10       ,'1M'         ,30         , 0.85        union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,25       ,'1M'         ,30         , 0.25        union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,50       ,'1M'         ,30         ,14.4         union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,75       ,'1M'         ,30         , 2.1         union all select
'22/10/2007 0:00'        ,'AUDUSD'        ,90       ,'1M'         ,30         , 3.8 

select a.CurrencyDateTime, a.periode, x.c2/100. Delta
,case when c2<50 then .01*(a.Data+b.data-.5*c.data)
      when c2=50 then .01* a.Data
      else /*c2>50*/  .01*(a.Data+c.data+.5*b.data)
      end Value
from #massage a
join #massage b
  on a.CurrencyDateTime=b.CurrencyDateTime
 and a.Currency=b.Currency
 and a.Periode=b.Periode
 and a.Delta=50
join #massage c
  on a.CurrencyDateTime=c.CurrencyDateTime
 and a.Currency=c.Currency
 and a.Periode=c.Periode
 and a.Delta=50
join (
select 
10, 90 union all select 
25, 75 union all select 
50, 50 union all select 
75, 25 union all select
90, 10)x(c2,c3)
  on b.Delta=x.c2
  and c.Delta=x.c3
order by 1,2,3

drop table #massage
 
Upvote 0 Downvote
Where in tarnation did this come from:

+0.5*-1
-0.5*-1

And your formula you JUST gave is COMPLETELY DIFFERENT from the other formulas you gave. Now you have THREE values being used in a formula, plus the unexplained 0.5 plus or minus multiplication value.

0.01*(data[Currency50Delta]+data[Currency10Delta]+0.5*-1*data[Currency90Delta])

[red]You still haven't explained how the formula is derived well enough to be able to help you.[/red]
 
Upvote 0 Downvote
As I said, the formula wasn't complete now I provide the complete formula.

The formula,
+0.5*-1
-0.5*-1

are a must from the formula, the 0.5 is fix number from the formula and the "-1" is from the scaling factor table for the currency because at the moment we are using currency AUDUSD so we are using -1. don't worry about that.

the formula is different than before because I didn't give the full formula, now it is.

That's all, and I have given the example using the real data as my example above.

Thanks man.
 
Upvote 0 Downvote
I see it now, PDreyer appears to have guessed correctly about the +.5 and -.5. I was too irritated and impatient to really examine it.

PDreyer's code works and looks fine. See how easy it was once you actually gave enough detail?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
SSMS question 1
Replies
4
Views
1K
Sana Shazadi
S
Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
674
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
the goal mfrom asking question is t
Replies
0
Views
248
ahmedsa2018
ahmedsa2018
Andrzejek
  • Locked
  • Question
Management Studio behavior question 1
Replies
3
Views
408
MarkSweetland
MarkSweetland
Uprightguy
  • Locked
  • Question
Query Question 2
Replies
4
Views
403
Uprightguy
Uprightguy

Part and Inventory Search

Sponsor

Back
Top