I need procedure for this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need procedure for this

Author

Topic

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-06 : 06:00:43

Hi,

I hope u anyone to help out of this...!

i need procedure below this.
I hav two table name splits & dividends..

Table Structure
***************

splits

id ----- splits_date ----- splits_factor
fss_01 -- 12/02/2001 -- 0.6
fss_01 -- 11/05/2002 -- 1.4
fss_01 -- 12/10/2002 -- 1.2
fss_01 -- 06/04/2004 -- 1.6
fss_01 -- 22/07/2005 -- 1.2
fss_01 -- 25/09/2006 -- 0.8

fss_02 -- 12/02/2001 -- 0.6
fss_02 -- 11/05/2002 -- 1.4
fss_02 -- 12/10/2002 -- 3.2
fss_02 -- 06/04/2004 -- 2.6
fss_02 -- 22/07/2005 -- 3.2
fss_02 -- 25/09/2006 -- 0.8

dividends

id ---- ex_date ------ DPS ------ DPS_Adj
fss_01 -- 12/02/2001 -- 0.6 -- 0.4
fss_01 -- 13/04/2002 -- 1.4 -- 0.8
fss_01 -- 14/08/2002 -- 1.2 -- 0.4
fss_01 -- 17/07/2004 -- 1.6 -- 0.7
fss_01 -- 15/08/2005 -- 1.2 -- 1.2
fss_01 -- 15/09/2006 -- 0.8 -- 1.1

fss_02 -- 16/02/2001 -- 0.6 -- 2.1
fss_02 -- 12/05/2002 -- 1.4 -- 2.1
fss_02 -- 16/10/2002 -- 3.2 -- 1.5
fss_02 -- 18/04/2004 -- 2.6 -- 1.1
fss_02 -- 19/07/2005 -- 3.2 -- 1.2
fss_02 -- 20/09/2006 -- 0.8 -- 0.9

calculation part:-
*****************
updates wil happen in dividends table:-

dividends.DPS = dividends.DPS_Adj * splits.splits_factor

CONDITION:-
*********

where both tables id will same and compare ex_date column between min_splits_date and max_splits_date

from splits table

For Example:-
----------

[ where table1.id = table2.id and exdate between min_splits_date and max_splits_date ]

loop flow
---------

update table1 set DPS = DPS_Adj * splits_factor from dividends , splits
where
{
cursor loop 1

where table1.'fss_01' = table2.'fss_01' and ex_date between '22/07/2005' and '25/09/2006'

cursor loop 2

where table1.'fss_01' = table2.'fss_01' and ex_date between '06/04/2004' and '22/07/2005'

cursor loop 3

where table1.'fss_01' = table2.'fss_01' and ex_date between '12/10/2002' and '06/04/2004'
}

Note:- I need to change the splits_factor value in calculation part on compare the current date [ min_splits_date ]

------------------------------------------------------------------------------

column data will update base on find_min_splits_date and find_max_splits_date

The DPS value would be update till the date range from splits table

Suggestion welcome..

MAG,
Start with the new Idea..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-06 : 09:37:58

[code]declare @splits table
(
id varchar(10),
splits_date date,
splits_factor decimal(3, 1)
)

set dateformat dmy

insert @splits
values ('fss_01', '12/02/2001', 0.6),
('fss_01', '11/05/2002', 1.4),
('fss_01', '12/10/2002', 1.2),
('fss_01', '06/04/2004', 1.6),
('fss_01', '22/07/2005', 1.2),
('fss_01', '25/09/2006', 0.8),
('fss_02', '12/02/2001', 0.6),
('fss_02', '11/05/2002', 1.4),
('fss_02', '12/10/2002', 3.2),
('fss_02', '06/04/2004', 2.6),
('fss_02', '22/07/2005', 3.2),
('fss_02', '25/09/2006', 0.8)

declare @dividends table
(
id varchar(10),
ex_date date,
dps decimal(3, 1),
dps_adj decimal(3, 1)
)

insert @dividends
values ('fss_01', '12/02/2001', 0.6, 0.4),
('fss_01', '13/04/2002', 1.4, 0.8),
('fss_01', '14/08/2002', 1.2, 0.4),
('fss_01', '17/07/2004', 1.6, 0.7),
('fss_01', '15/08/2005', 1.2, 1.2),
('fss_01', '15/09/2006', 0.8, 1.1),
('fss_02', '16/02/2001', 0.6, 2.1),
('fss_02', '12/05/2002', 1.4, 2.1),
('fss_02', '16/10/2002', 3.2, 1.5),
('fss_02', '18/04/2004', 2.6, 1.1),
('fss_02', '19/07/2005', 3.2, 1.2),
('fss_02', '20/09/2006', 0.8, 0.9)

SELECT d.id,
d.ex_date,
d.dps,
d.dps_adj,
d.dps_adj * s.splits_factor AS [New dps by Peso]
FROM @Dividends AS d
OUTER APPLY (
SELECT TOP(1) x.splits_factor
FROM @Splits AS x
WHERE x.id = d.id
AND x.splits_date <= d.ex_date
ORDER BY x.splits_date DESC
) AS s[/code]

N 56°04'39.26"
E 12°55'05.63"

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-07 : 01:11:45

See,

DPS value will update base on ex_date between min_split date and max_split date for an id.

Using CURSOR is better

for example:-

update dividends set d.DPS = d.DPS_ADJ * s.splits_factor from dividends d, splits s where d.id = s.id and
ex_date between min_splits date and max_spilts date.

I need to update DPS column between the splits date range till the date is available in splits table for the particular id.

Suggestion welcome..!

MAG,
Start with the new Idea..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-07 : 01:43:14

So... What's wrong with my approach?

N 56°04'39.26"
E 12°55'05.63"

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-07 : 02:19:12

ya Thanks Mr.Pesco..!

Hmmm,

Wat am try to say is,

for example:-

d.dps = d.dps_adj * s.splits_factor from dividends d, splits s where d.id = s.id and ex_date between min_split_date and
max_split_date

LOOP STRUCTURE:
---------------

dps = 1.1 * 0.8 where 'fss_01' = 'fss_01' and '15/09/2006' betweeen '25/09/2006' and '31/12/2999'

dps = 1.2 * 1.2 where 'fss_01' = 'fss_01' and '15/08/2005' betweeen '22/07/2005' and '25/09/2006'

dps = 0.7 * 1.6 where 'fss_01' = 'fss_01' and '17/07/2004' betweeen '06/04/2004' and '22/07/2005'

dps = 0.4 * 1.2 where 'fss_01' = 'fss_01' and '14/08/2002' betweeen '06/04/2004' and '22/07/2005'

dps = 0.8 * 1.4 where 'fss_01' = 'fss_01' and '13/04/2002' betweeen '12/10/2002' and '06/04/2004'

dps = 0.4 * 0.6 where 'fss_01' = 'fss_01' and '12/02/2001' betweeen '12/02/2001' and '12/10/2002'

UDPATED VALUES:-
--------------

0.88

1.44

1.12

0.48

1.12

0.24

Could pls corss check Your query once..

I need above calculation flow..

Thank in advance

MAG,
Start with the new Idea..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-07 : 09:25:15

Your calculations are all wrong.
Some of your calculations are DPS * DPS_ADJ and some of your calculations are DPS_ADJ * SPLITS_FACTOR.

What are the rules for this?

N 56°04'39.26"
E 12°55'05.63"

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-10 : 00:59:19

I need procedure for this.....!

I need to calculate dps based on split date, where the ex_date between split date.

For Ex:-
Table Name:- splits

id split_date split_factor
------------------------------------------
Fs_001 -- 14/05/2009 --- 0.53
Fs_001 -- 20/02/2010 --- 0.33
Fs_001 -- 30/08/2010 --- 0.24

Table Name:- dividends

id ex_date dps dps_adj
-----------------------------------------------------
Fs_001 -- 14/05/2009 -- 0.17 -- 0.44
Fs_001 -- 20/02/2010 -- 0.31 -- 0.25
Fs_001 -- 04/09/2010 -- 0.21 -- 0.22

Iteration 1:-
*********

-1st get the Ex_date from dividend table max(date), [ 04/09/2010 ]

-2nd get the split_date from split table , where ex_date between 30/08/2010 and 31/12/2010

[note:- 31/12/2010 default its only for first iteration..]

==> dps = 0.21 * 0.24 where 04/09/2010 between 30/08/2010 and 31/12/2010

Between this two split_date, DPS value wil calculate

Iteration 2:-
*********

-1st get the Ex_date from dividend table max(date), [20/02/2010]

-2nd get the split_date from split table , where ex_date between 20/02/2010 and 29/08/2010

note:- Here max split date is calculated before one day back.. [ dateadd(day,-1,'30/08/2010') ]

==> dps = 0.31 * 0.33 where 20/02/2010 between 20/02/2010 and 29/08/2010

Between this two split_date, DPS value wil calculate

Suggestion welcome..

Thank in advance..!

MAG,
Start with the new Idea..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-10 : 06:40:41

This is EXACTLY what my suggestion does.

N 56°04'39.26"
E 12°55'05.63"

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-11 : 02:31:47

ok I think, I need to change some step in ur suggestion

MAG,
Start with the new Idea..
http://mageshkumarm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources