Challenging question ... 2

[hokky]

Hi guys,

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

I got the input like this from the table (

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

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.

 

[hokky]

PDreyer/ESquared

Thanks for your help, really appreciated
two more question (hope you don't mind):

1. how do I create the error handling (ie. duplicate row, or data that we need in formula is missing)
2. how do I create the header 

From the output result,we only use one currency right. But there are many currency there, so I need to put header every currency

From output

Currency	CurrencyDateTime	periode 	delta	value
AUDUSD	22/10/2007 0:00	7	0.1	0.1945
AUDUSD	22/10/2007 0:00	7	0.25	0.17975
AUDUSD	22/10/2007 0:00	7	0.5	0.168
AUDUSD	22/10/2007 0:00	7	0.75	0.16175
AUDUSD	22/10/2007 0:00	7	0.9	0.1615
AUDCHF	22/10/2007 0:00	7	0.1	0.1945
AUDCHF	22/10/2007 0:00	7	0.25	0.17975
AUDCHF	22/10/2007 0:00	7	0.5	0.168
AUDCHF	22/10/2007 0:00	7	0.75	0.16175
AUDCHF	22/10/2007 0:00	7	0.9	0.1615

to become

Currency Unit				
AUDUSD	22/10/2007 0:00	7	0.1	0.1945
	22/10/2007 0:00	7	0.25	0.17975
	22/10/2007 0:00	7	0.5	0.168
	22/10/2007 0:00	7	0.75	0.16175
	22/10/2007 0:00	7	0.9	0.1615
	…	…	…	…

Currency Unit				
AUDCHF	22/10/2007 0:00	7	0.1	0.1945
	22/10/2007 0:00	7	0.25	0.17975
	22/10/2007 0:00	7	0.5	0.168
	22/10/2007 0:00	7	0.75	0.16175
	22/10/2007 0:00	7	0.9	0.1615
	…	…	…	…

Thanks guys,

 

[hokky]

Hi PDreyer,

I try to understand your code,
the main question is why you put in the join
and a.Delta=50 in join #massage b and c

Would you care to elaborate ?
Thanks man

 

[PDreyer]

1. how do I create the error handling (ie. duplicate row, or data that we need in formula is missing)

You need to clean your data first. Duplicates can be prevented with e.g. a unique index on
CurrencyDateTime, Currency,Periode, Delta

And sets with missing data can be identified with e.g.
select a.Currency, a.CurrencyDateTime, a.Periode, count(*)
from #massage a
where a.Delta in (10,25,50,75,90)
group by a.Currency, a.CurrencyDateTime, a.Periode
having count(*)!=5

2. how do I create the header

Formatting should be done by your front end

why you put in the join
and a.Delta=50 in join #massage b and c
Would you care to elaborate ?

For the formula
0.01*(data[Currency50Delta]+data[Currency10Delta]+0.5*-1*data[Currency90Delta])
0.01*(data[Currency50Delta]+data[Currency25Delta]+0.5*-1*data[Currency75Delta])
0.01*(data[Currency50Delta]
0.01*(data[Currency50Delta]+data[Currency25Delta]-0.5*-1*data[Currency75Delta])
0.01*(data[Currency50Delta]+data[Currency10Delta]-0.5*-1*data[Currency90Delta])

you need 3 different rows hence the a,b,c join
a.delta is always = 50
b.delta is 10 to 90
c-delta is 90 to 10

and the join between b and c is done with the cross reference table x

 

[ESquared]

To accomodate the different currencies you would need equijoins on the currency column. And those were already put in there by the foresightful PDreyer.

hokky, you really could save everyone some time if you would give full details from the start.

 

[hokky]

PDreyer,

Thanks for the code and your code works, I just need to ask simple question :

in this code :
x.c2/100. Delta

what's the red dot for/means ? I know if I take off that dot it won't work, do you mind to explain ?

thanks man

 

[ESquared]

It forces the calculation to return a numeric/decimal data type instead of an integer. Normally for clarity we use a zero place digit like "100.0" instead of just "100.".

To be exact, 100.0 will be interpreted as numeric(3,0).

 

[ESquared]

Sorry, I misspoke:

100.0 will be interpreted as numeric(4,1).
100. will be interpreted as numeric(3,0).

Further multiplication and division extends the scale and precision as necessary.

 

[hokky]

Hi ESquared,

Thanks for your explanation.

Can I ask more about what difference between numeric and decimal ? plus example ?

I know about decimal (5,2) like --> 100.32
what about numeric (5,2) like --> ??

Thanks,

 

[PDreyer]

what difference between numeric and decimal

The spelling of the word

 

[ESquared]

Yes they are the same data type, one is a synonym for the other. SQL Server thinks in "numeric" but "decimal" does fine.

 

[hokky]

100. will be interpreted as numeric(3,0).

Looks like not completely right

select 90/100.
result
.900000 --> not exactly decimal(3,0)

select 900/1000000.
result
.00090000 --> not exactly decimal(7,0)

 

[ESquared]

100.
3 digits
0 behind the decimal point
= decimal(3,0)

Believe me, I checked this before I posted using the variantproperty function.

Once you do something else with it, the data type changes. Look in books online for how addition, subtraction, multiplication, and division with the decimal data type changes the precision and scale of the result.

Think of it this way: if you take two varchar(2) strings and concatenate them, you get a varchar(4) string, right?

100. IS decimal(3,0). That it's decimal at all forces the entire operation to be decimal as well.