loop though the next symbol pairs. [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <%xbTa.11536$KF3.3345794169@newssvr10.news.prodigy.com> [ref] => [htmlstate] => on_nl2br [postusername] => Jay [ip] => jayesh131@hotma [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> loop though the next symbol pairs. > > > > > > > > >[/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <#uylWHJUDHA.3024@tk2msftngp13.phx.gbl> [ref] => <%xbTa.11536$KF3.3345794169@newssvr10.news.prodigy.com> [htmlstate] => on_nl2br [postusername] => Delbert [ip] => delbert@noincom [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> loop though the next symbol pairs. > > > > > > > > > > > > > > > > > > > > > > > > > > >[/ref] > > > >[/ref] > >[/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <%xbTa.11536$KF3.3345794169@newssvr10.news.prodigy.com> <#uylWHJUDHA.3024@tk2msftngp13.phx.gbl> [htmlstate] => on_nl2br [postusername] => Delbert [ip] => delbert@noincom [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> loop though the next symbol pairs. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >[/ref] > > > >[/ref] > >[/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <0evTa.11930$JF3.3410614374@newssvr10.news.prodigy.com> [ref] => <%xbTa.11536$KF3.3345794169@newssvr10.news.prodigy.com> <#uylWHJUDHA.3024@tk2msftngp13.phx.gbl> [htmlstate] => on_nl2br [postusername] => Jay [ip] => jayesh131@hotma [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 4 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Help avoid Cursor to calc Correlation - Microsoft SQL / MS SQL Server

Help avoid Cursor to calc Correlation - Microsoft SQL / MS SQL Server

This is probably a lenghtly post but its mostly data If you are familiar with correlations - I am essentially trying to create a correlation matrix. MyTable ======== Date Symbol Price ===== ====== ====== 1/1/99 MSFT 25.00 1/1/99 INTC 21.00 1/1/99 AMD 23.00 1/2/99 MSFT 22.00 1/2/99 INTC 23.00 1/2/99 AMD 21.00 1/3/99 MSFT 24.00 1/3/99 INTC 23.00 1/3/99 AMD 26.00 1/4/99 MSFT 28.00 1/4/99 INTC 24.00 1/4/99 AMD 23.00 1/5/99 MSFT 25.00 1/5/99 INTC 21.00 1/5/99 AMD 28.00 ----- ----- Desired Output Table ================= SymbolA SymbolB CorrelationCoefficient ======= ======= ================= MSFT INTC 0.7 MSFT AMD 0.8 INTC AMD 1.2 ...

  1. #1

    Default Help avoid Cursor to calc Correlation

    This is probably a lenghtly post but its mostly data

    If you are familiar with correlations - I am essentially trying to create a
    correlation matrix.

    MyTable
    ========

    Date Symbol Price
    ===== ====== ======
    1/1/99 MSFT 25.00
    1/1/99 INTC 21.00
    1/1/99 AMD 23.00

    1/2/99 MSFT 22.00
    1/2/99 INTC 23.00
    1/2/99 AMD 21.00

    1/3/99 MSFT 24.00
    1/3/99 INTC 23.00
    1/3/99 AMD 26.00

    1/4/99 MSFT 28.00
    1/4/99 INTC 24.00
    1/4/99 AMD 23.00

    1/5/99 MSFT 25.00
    1/5/99 INTC 21.00
    1/5/99 AMD 28.00

    -----
    -----

    Desired Output Table
    =================

    SymbolA SymbolB CorrelationCoefficient
    ======= ======= =================
    MSFT INTC 0.7
    MSFT AMD 0.8
    INTC AMD 1.2

    Basically, I need to calculate the correlation coefficient between each
    symbol pair in MyTable


    This is what I am doing right now:
    ==========================

    I create two identical cursors that simply contain a list of distinct
    symbols.

    Basically, Cursor1 = Cursor2 = {MSFT, INTC, AMD} and use a nested loop. I
    then use the following logic:

    SELECT A.Date, A.Price, B.Price
    FROM MyTable A JOIN MyTable B ON ......Date
    WHERE A.Symbol = Cusror1.Symbol AND B.Symbol = Cursor2.Symbol

    Essentially, I get temp table #Temp in this format to calculate correl.

    Date Price_SymbolA (X) Price_SymbolB (Y)
    ==== =============== ===============

    Now I can easily calculate the Correlation using stadard formula

    I use Corrrel = SELECT SUM().../...SQUARE() etc FROM Temp


    Then:
    INSERT INTO Output Values (Cusror1.Symbol, Cursor2.Symbol, Correl)


    -- End of Loop --> loop though the next symbol pairs.









    Jay Guest

  2. #2

    Default Re: Help avoid Cursor to calc Correlation

    How about:

    SELECT
    rd1.Symbol as SymbolA,
    rd2.Symbol as SymbolB,
    FancyFormula as CorrelationCoefficient
    FROM RawData rd1 CROSS JOIN RawData rd2
    GROUP BY rd1.Symbol, rd2.Symbol

    and then once you get the fancy formula worked out,
    you can switch the query to be an insert query.

    Bye,
    Delbert Glass

    "Jay" <com> wrote in message
    news:%xbTa.11536$news.prodigy.com... 



    Delbert Guest

  3. #3

    Default Re: Help avoid Cursor to calc Correlation

    Instead of a CROSS JOIN use an INNER JOIN
    and say the dates have to be the same.

    BTW, I hope from several view points
    that you do not really desire those
    particular values for the correlation coefficients.

    Bye,
    Delbert Glass


    "Jay" <com> wrote in message
    news:qQhTa.11578$news.prodigy.com... [/ref]
    > create [/ref][/ref]
    each [/ref][/ref]
    loop. [/ref][/ref]
    correl. 
    > >
    > >[/ref]
    >
    >[/ref]


    Delbert Guest

  4. #4

    Default Re: Help avoid Cursor to calc Correlation

    I used a CROSS JOIN as you suggested but with the clause A.Date = B.Date. I
    will try it with INNER JOIN too.

    No, I just used sample data. My actual data uses daily returns on some FX
    futures.
    Thanks.


    "Delbert Glass" <com> wrote in message
    news:phx.gbl... 
    > > create [/ref]
    > each [/ref][/ref]
    distinct [/ref]
    > loop. [/ref]
    > correl. 
    > >
    > >[/ref]
    >
    >[/ref]


    Jay Guest

Similar Threads

  1. Calc help please
    By The in forum FileMaker
    Replies: 8
    Last Post: November 6th, 06:37 AM
  2. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  3. mssql, duplicate code, and the dearth of correlation
    By Lucas Fletcher in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 10th, 11:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •