Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2011
    Posts
    12

    Question Unanswered: Cursor or select into and design

    Hi there,

    I am hoping someone can help me with my problem. I have recently switched (2 weeks ago) from using excel and vba to sql and as thinking in terms of a database is really brand new to me I need a lot of help and a lot of understanding!

    First of all I need some advice on the design of this. My main data needs to be updated daily from the web. How this can automatically be pulled into the sql database I have no idea and I am currently manually adding it using an insert. The historical data I imported through a bulk insert.

    Once the data is up to date I want to run a query against this and calculate various things including:
    1. % Move for the day
    2. Direction of the move
    3. Number of times the move for the day is in the same direction i.e Up 3 days in a row or down 2 days in a row
    4. % of the Move until the direction changes i.e if the move is Up 3 days - what was the % move from the last down day up to the last up move before it changed direction
    5. Moving average based on a 14 day period

    How should I structure this. Should I keep the main data that needs to be updated daily in one table and write the sql query result to a different table. The only problem is I would really like to just run the query with the calculation against the latest updated date and update the table only for the changes rather than doing it against the whole data set. I don't know how to do this but if I know what the right structure is then I can at least work towards that.

    So at the moment I have been able to insert the historical data into a database table called PriceData.

    It contains the following columns:
    PriceRowNo - (Primary Key)
    PriceDate (set to be unique)
    Price

    I need to compare today's price with yesterday's price and calculate the % move for the day and write that to a new column called DailyMove. This column as explained above is required to do other calculations in columns that I need to add eventually so will need the data to be written to the same table unless you feel my structure is wrong to start with.

    I have tried both a Cursor and Inserting into a TempDB and performing a calculation on it but I am not getting the required results. I have read about a cursor and how it is not the best approach due to the resources it consumes but I thought that if I would only be using it to update the latest records added it might not be a problem. Even so I also tried to do it with a select into statement in which I tried comparing the two tables and doing the calculation and then trying to write that back to my table.

    Needless to say I did not succeed hence my question.


    Could anyone please look at my two different efforts and let me know where I am being stupid!! Thanks

    --With the below code I get a calculated result but the result should start on row 2 of the data as for row one there is no price movement yet as it is the start of the data.


    --Select original table data and insert into TempDB
    select *
    into TempPriceData
    from PriceData

    --Calculation comparing Price movement between two tables
    Select
    P.PriceRowNo
    ,P.PriceDate
    ,P.Price
    ,(tP.Price-P.Price)/P.Price As DailyMove
    from Price as P
    Left join TempPriceData as tP
    ON P.PriceRowNo+1 = tP.PriceRowNo


    -- For the cursor I know I need to somehow add the DailyMove column to the PriceData table but if I try and do that before running the cursor it skips all the rows and giving me an error except right at the end it adds a row and inputs the value based on the the 2nd days price change. I have tried declaring it as a variable as well but am getting errors on that version as well.

    --Cursor
    Declare @PriceYesterday decimal(15,2)
    Declare @PriceToday decimal(15,2)


    Declare MoveForTheDayCursor CURSOR FAST_FORWARD FOR
    Select
    Price from PriceData

    --Doing this to try and skip the first record as there should be calculation
    Open MoveForTheDayCursor
    FETCH NEXT FROM MoveForTheDayCursor
    INTO @PriceYesterday

    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM MoveForTheDayCursor
    INTO @PriceYesterday
    FETCH NEXT FROM MoveForTheDayCursor
    INTO @PriceToday

    UPDATE PriceData
    SET PriceData.DailyMove={(@PriceToday - @PriceYesterday)/@PriceYesterday)}
    END
    CLOSE MoveForTheDayCursor
    DEALLOCATE MoveForTheDayCursor


    An extract of my data table is below
    PriceRowNo, PriceDate,Price
    1,20110110,12.54
    2,20110111,13.08
    3,20110112,14.05
    4,20110113,14.85
    5,20110114,14.65
    6,20110115,14.57
    7,20110116,14.38
    8,20110117,14.52


    I would really appreciate it if someone could give me some assistance with this as I am really lost as to how to approach this!!

    Thanks for your time.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The following snippet of code shows how to compute the percentage of change without resorting to a cursor. The logic is slippery at first, but the elegance ought to make it worth your while to understand how it works.
    Code:
    CREATE TABLE PriceData (
       PriceRowNo       INT         NOT NULL
       CONSTRAINT XPKPriceData
          PRIMARY KEY CLUSTERED (PriceRowNo)
    ,  PriceDate        DATETIME    NOT NULL
    ,  Price            MONEY       NOT NULL
    )
    GO
    
    CREATE INDEX XIE01PriceData ON PriceData (PriceDate)
    GO
    
    INSERT INTO PriceData
       VALUES (1,'2011-01-10',12.54)
    ,         (2,'2011-01-11',13.08)
    ,         (3,'2011-01-12',14.05)
    ,         (4,'2011-01-13',14.85)
    ,         (5,'2011-01-14',14.65)
    ,         (6,'2011-01-15',14.57)
    ,         (7,'2011-01-16',14.38)
    ,         (8,'2011-01-17',14.52)
    GO
    
    SELECT (this.Price - prev.Price) / prev.Price, *
       FROM PriceData AS this
       LEFT JOIN PriceData AS prev
          ON (prev.PriceDate = (SELECT MAX(z.PriceDate)
             FROM PriceData AS z
             WHERE z.PriceDate < this.PriceDate))
    You can extend the concept using a more complex "z" expression to find both the n day average and also where the trend reverses for your second calcuation.

    Note that I've provided an answer based on the question that you posted, but that you've probably grossly simplified your example so you may need to modify my solution to fit your real problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2011
    Posts
    12

    Thanks

    Hi Pat,

    At this moment in time all I can say is thank you very much as I don't understand it at all!

    I will work through it. May I get back to you if I have any or a lot of questions?

    I am guessing as my data are already in a table I don't have to do the insert section. Is this correct?

    Thank you so much for your time and sharing your knowledge with me!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Work through it, get back with questions, take your time. There are many SQL geeks around DBForums, and we'll all be glad to help where we can.

    My INSERT INTO simply took the sample data that you provided and put it into the table that I created. You already have a table full of data, so you don't need to do that.

    If you'd included some more data (like a month or two), I could have tried to provide solutions for your other questions. Since there wasn't enough data, I figured one example would get you started.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2011
    Posts
    12
    OK great!

    YES, I would really appreciate all the help I can get with the other calculations. I will try and understand the solution you sent through and then make an attempt at the following calculation. I will then send you my 'attempt'!

    I don't know where you are located but have a good evening.

    Thanks again

  6. #6
    Join Date
    Jul 2011
    Posts
    12

    Code and data

    Hi again Pat,

    Unfortunately I was only able to spend time looking through the code you sent me on Sat tonight and THANKS it gives me exactly what I need. I am still trying to understand it completely which give you an idea of how far away I am from writing these kind of things.

    From what I can see you are joining the table to itself and giving the tables aliases of prev and this. The on and where clause is a bit confusing to me. Would you mind explaining it to me in English. Would it be correct to say you left join the PREV table to the THIS on the max date in a different table called Z and in this table Z the max date is < the date in the THIS table???

    Also you mentioned in your previous post that you would be able to assist me with some of the other column calculations that I need to do. I have therefore posted 200 entries below which could be used for this purpose. I hope they are the correct format.

    As I mentioned in my previous post I need the following calculations:
    1. A simple text field showing the movement as up or down
    2. A field showing the movement plus the number of time the move has been in the same direction. I have included the first 20 or so expected values
    3. The percentage move of the series meaning the total move from the last up to the last down or the last down to the last up. For example in the data set included the move ending in UP-4 on 20101103 was 0.02521 and record 18 was the last move of the down 3 movement which moved from 16.92 down to 16.73 resulting in a down movement of DOWN 3 of -0.01123.
    4. A 14 day moving average.

    These things were so easy in excel but not so anymore.


    For the text field showing as up or down is there a way to go something like select where((this.price-prev.price)/prev.price> 0 as UP or ((this.price-prev.price)/prev.price< 0 as DOWN using the same joining structure that you put together for the first one?

    Also I am guessing with the count the only way would be to have some sort of multiple joins but there could be instances where the movement in the same direction could be 10+.

    Bottom line is PLEASE HELP!!

    Here are the expected results of the first 20 rows and further down below are 200 records.

    I really appreciate your help with this and any guidance would be really great!

    Thanks

    MoveForDay Direction & count MoveOfSeries

    0.011405 UP-1 -
    0.004748 UP-2 -
    0.002953 UP-3 -
    0.005889 UP-4 0.02521
    -0.002927 DOWN-1 -0.00293
    0.003523 UP-1 0.00352
    -0.019310 DOWN-1 -
    -0.005370 DOWN-2 -0.02458
    0.002999 UP-1 -
    0.008373 UP-2 0.01140
    -0.000593 DOWN-1 -0.00059
    0.001187 UP-1 -
    0.001778 UP-2 -
    0.001183 UP-3 0.00415
    -0.003546 DOWN-1 -
    -0.007117 DOWN-2 -
    -0.000597 DOWN-3 -0.01123




    Data
    (1,'20101028',16.66)
    (2,'20101029',16.85)
    (3,'20101101',16.93)
    (4,'20101102',16.98)
    (5,'20101103',17.08)
    (6,'20101104',17.03)
    (7,'20101105',17.09)
    (8,'20101108',16.76)
    (9,'20101109',16.67)
    (10,'20101110',16.72)
    (11,'20101111',16.86)
    (12,'20101112',16.85)
    (13,'20101115',16.87)
    (14,'20101116',16.9)
    (15,'20101117',16.92)
    (16,'20101118',16.86)
    (17,'20101119',16.74)
    (18,'20101122',16.73)
    (19,'20101123',16.82)
    (20,'20101124',17.02)
    (21,'20101126',17.05)
    (22,'20101129',17.05)
    (23,'20101130',17.23)
    (24,'20101201',17.29)
    (25,'20101202',17.32)
    (26,'20101203',17.23)
    (27,'20101206',17.21)
    (28,'20101207',17.28)
    (29,'20101208',17.24)
    (30,'20101209',17.06)
    (31,'20101210',17.06)
    (32,'20101213',16.99)
    (33,'20101214',17.15)
    (34,'20101215',17.2)
    (35,'20101216',17.17)
    (36,'20101217',17.21)
    (37,'20101220',17.28)
    (38,'20101221',17.28)
    (39,'20101222',17.22)
    (40,'20101223',17.24)
    (41,'20101227',17.23)
    (42,'20101228',17.29)
    (43,'20101229',17.32)
    (44,'20101230',17.2)
    (45,'20101231',17.19)
    (46,'20110103',17.07)
    (47,'20110104',17.09)
    (48,'20110105',17.12)
    (49,'20110106',17.25)
    (50,'20110107',17.45)
    (51,'20110110',17.49)
    (52,'20110111',17.45)
    (53,'20110112',17.44)
    (54,'20110113',17.45)
    (55,'20110114',17.55)
    (56,'20110118',17.56)
    (57,'20110119',17.56)
    (58,'20110120',17.46)
    (59,'20110121',17.53)
    (60,'20110124',17.44)
    (61,'20110125',17.3)
    (62,'20110126',17.29)
    (63,'20110127',17.53)
    (64,'20110128',17.55)
    (65,'20110131',17.63)
    (66,'20110201',17.78)
    (67,'20110202',17.85)
    (68,'20110203',17.75)
    (69,'20110204',17.94)
    (70,'20110207',17.98)
    (71,'20110208',17.96)
    (72,'20110209',17.88)
    (73,'20110210',18.03)
    (74,'20110211',18.05)
    (75,'20110214',17.93)
    (76,'20110215',17.96)
    (77,'20110216',17.83)
    (78,'20110217',17.83)
    (79,'20110218',17.76)
    (80,'20110222',17.86)
    (81,'20110223',17.96)
    (82,'20110224',18.22)
    (83,'20110225',18.11)
    (84,'20110228',18.27)
    (85,'20110301',18.12)
    (86,'20110302',18.22)
    (87,'20110303',18.27)
    (88,'20110304',18.27)
    (89,'20110307',18.29)
    (90,'20110308',18.29)
    (91,'20110309',18.18)
    (92,'20110310',18.26)
    (93,'20110311',18.44)
    (94,'20110314',18.52)
    (95,'20110315',18.56)
    (96,'20110316',18.68)
    (97,'20110317',18.6)
    (98,'20110318',18.71)
    (99,'20110321',18.69)
    (100,'20110322',18.69)
    (101,'20110323',18.67)
    (102,'20110324',18.72)
    (103,'20110325',18.78)
    (104,'20110328',18.77)
    (105,'20110329',18.79)
    (106,'20110330',18.6)
    (107,'20110331',18.88)
    (108,'20110401',18.93)
    (109,'20110404',19.14)
    (110,'20110405',19.26)
    (111,'20110406',19.4)
    (112,'20110407',19.25)
    (113,'20110408',18.98)
    (114,'20110411',18.93)
    (115,'20110412',18.97)
    (116,'20110413',18.92)
    (117,'20110414',18.83)
    (118,'20110415',19)
    (119,'20110418',19.16)
    (120,'20110419',19.14)
    (121,'20110420',18.11)
    (122,'20110421',17.91)
    (123,'20110425',18.11)
    (124,'20110426',17.44)
    (125,'20110427',17.69)
    (126,'20110428',17.64)
    (127,'20110429',17.81)
    (128,'20110502',17.91)
    (129,'20110503',17.67)
    (130,'20110504',17.59)
    (131,'20110505',17.32)
    (132,'20110506',16.87)
    (133,'20110509',16.69)
    (134,'20110510',16.87)
    (135,'20110511',16.68)
    (136,'20110512',17.06)
    (137,'20110513',17.36)
    (138,'20110516',17.61)
    (139,'20110517',17.59)
    (140,'20110518',17.48)
    (141,'20110519',17.23)
    (142,'20110520',17.27)
    (143,'20110523',17.5)
    (144,'20110524',17.69)
    (145,'20110525',17.84)
    (146,'20110526',18.02)
    (147,'20110527',17.95)
    (148,'20110531',17.99)
    (149,'20110601',18.14)
    (150,'20110602',18.41)
    (151,'20110603',18.46)
    (152,'20110606',18.61)
    (153,'20110607',18.48)
    (154,'20110608',18.28)
    (155,'20110609',18.29)
    (156,'20110610',18.32)
    (157,'20110613',18.34)
    (158,'20110614',18.54)
    (159,'20110615',18.68)
    (160,'20110616',18.7)
    (161,'20110617',18.68)
    (162,'20110620',18.82)
    (163,'20110621',18.79)
    (164,'20110622',18.54)
    (165,'20110623',18.53)
    (166,'20110624',18.54)
    (167,'20110627',18.43)
    (168,'20110628',18.42)
    (169,'20110629',18.55)
    (170,'20110630',18.68)
    (171,'20110701',18.54)
    (172,'20110705',18.59)
    (173,'20110706',18.75)
    (174,'20110707',18.61)
    (175,'20110708',18.87)
    (176,'20110711',19.09)
    (177,'20110712',19.18)
    (178,'20110713',19.29)
    (179,'20110714',19.37)
    (180,'20110715',19.31)
    (181,'20110718',19.21)
    (182,'20110719',19.37)
    (183,'20110720',19.44)
    (184,'20110721',19.42)
    (185,'20110722',19.14)
    (186,'20110725',19.41)
    (187,'20110726',19.42)
    (188,'20110727',19.45)
    (189,'20110728',19.69)
    (190,'20110729',19.66)
    (191,'20110801',20)
    (192,'20110802',19.89)
    (193,'20110803',20.12)
    (194,'20110804',20)
    (195,'20110805',19.78)
    (196,'20110808',19.86)
    (197,'20110809',19.85)
    (198,'20110810',19.71)
    (199,'20110811',19.89)
    (200,'20110812',20.01)

  7. #7
    Join Date
    Jul 2011
    Posts
    12

    My effort

    Hi again!

    I used your code from Saturday and inserted the result into a new table called MoveForDayTempData. I then used a select case statement as shown below:

    Select CASE
    When MFDTDate.MoveForDay>0 THEN 'UP'
    When MFDTDate.MoveForDay=0 THEN 'NO CHANGE'
    ELSE 'DOWN'
    END AS Direction
    From MoveForDayTempData as MFDTDate

    Please let me know what you think.

  8. #8
    Join Date
    Jul 2011
    Posts
    12
    This only gives me the text of the movement but I also need to combine that with a directional count. Would I need to use some sort of variable which is increased when values are the same and reset to one if not??

  9. #9
    Join Date
    Jul 2011
    Posts
    12

    Further assistance please

    Hi Pat

    From a lack of not knowing which way to go with this I have been working on a cursor again to try and get the directional count calculation. End result = NOT WORKING.

    When you do have a moment would you mind looking at this and my 2 previous posts where i explained the calculations that I need to do. For this specific one I need to count the number of times that the price moved in the same direction before changing direction. I tried using a cursor and a counter resetting it to one when the direction changes but am getting errors. I am also not sure how to insert this result into a table so that I can eventually use it as part of my final query results.

    As I mentioned in my previous post I used your select into and join code to get the direction and then tried using the results in the cursor.

    Thanks again for all your help.

    Select
    ((this.Price - prev.Price) / prev.Price)
    as MoveForDay
    INTO MoveForDayTempData
    FROM PriceData AS this
    LEFT JOIN PriceData AS prev
    ON (prev.PriceDate = (SELECT MAX(z.PriceDate)
    FROM PriceData AS z
    WHERE z.PriceDate < this.PriceDate))

    Select CASE
    When MFDTData.MoveForDay>0 THEN 'UP'
    When MFDTData.MoveForDay=0 THEN 'NO CHANGE'
    ELSE 'DOWN'
    END AS Direction
    From MoveForDayTempData as MFDTData



    Declare @MoveYesterday VARCHAR(800)
    Declare @MoveToday VARCHAR(800)
    Declare @CountOfDirection INT

    Declare DirectionCursor CURSOR FAST_FORWARD
    FOR
    Select
    Direction from MFDTData

    Open DirectionCursor
    FETCH NEXT FROM DirectionCursor
    INTO @MoveYesterday
    FETCH NEXT FROM DirectionCursor
    INTO @MoveToday

    Set @CountOfDirection = 1

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT CASE
    When @MoveToday<>@MoveYesterday
    THEN 1
    When @MoveToday = @MoveYesterday
    THEN @CountOfDirection +1
    END AS DIRECTIONCOUNTER

    SET @MoveYesterday = @MoveToday

    FETCH NEXT FROM DirectionCursor
    INTO @MoveToday

    END

    CLOSE DirectionCursor
    DEALLOCATE DirectionCursor

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Caveat Emptor! Note that weekends and holidays are a bugaboo, you'll have to figure out how you want to deal with them. The "two week average" might need to be adjusted one day either way depending on how your reporting needs to figure things. I took a strict two weekes, but many systems add or deduct one day to make calcuations easier to handle.
    Code:
    CREATE TABLE PriceData (
       PriceRowNo       INT         NOT NULL
       CONSTRAINT XPKPriceData
          PRIMARY KEY CLUSTERED (PriceRowNo)
    ,  PriceDate        DATETIME    NOT NULL
    ,  Price            MONEY       NOT NULL
    )
    GO
    
    CREATE INDEX XIE01PriceData ON PriceData (PriceDate)
    GO
    
    INSERT INTO PriceData
       VALUES (  1,'2010-10-28',16.66)
    ,         (  2,'2010-10-29',16.85)
    ,         (  3,'2010-11-01',16.93)
    ,         (  4,'2010-11-02',16.98)
    ,         (  5,'2010-11-03',17.08)
    ,         (  6,'2010-11-04',17.03)
    ,         (  7,'2010-11-05',17.09)
    ,         (  8,'2010-11-08',16.76)
    ,         (  9,'2010-11-09',16.67)
    ,         ( 10,'2010-11-10',16.72)
    ,         ( 11,'2010-11-11',16.86)
    ,         ( 12,'2010-11-12',16.85)
    ,         ( 13,'2010-11-15',16.87)
    ,         ( 14,'2010-11-16',16.9)
    ,         ( 15,'2010-11-17',16.92)
    ,         ( 16,'2010-11-18',16.86)
    ,         ( 17,'2010-11-19',16.74)
    ,         ( 18,'2010-11-22',16.73)
    ,         ( 19,'2010-11-23',16.82)
    ,         ( 20,'2010-11-24',17.02)
    ,         ( 21,'2010-11-26',17.05)
    ,         ( 22,'2010-11-29',17.05)
    ,         ( 23,'2010-11-30',17.23)
    ,         ( 24,'2010-12-01',17.29)
    ,         ( 25,'2010-12-02',17.32)
    ,         ( 26,'2010-12-03',17.23)
    ,         ( 27,'2010-12-06',17.21)
    ,         ( 28,'2010-12-07',17.28)
    ,         ( 29,'2010-12-08',17.24)
    ,         ( 30,'2010-12-09',17.06)
    ,         ( 31,'2010-12-10',17.06)
    ,         ( 32,'2010-12-13',16.99)
    ,         ( 33,'2010-12-14',17.15)
    ,         ( 34,'2010-12-15',17.2)
    ,         ( 35,'2010-12-16',17.17)
    ,         ( 36,'2010-12-17',17.21)
    ,         ( 37,'2010-12-20',17.28)
    ,         ( 38,'2010-12-21',17.28)
    ,         ( 39,'2010-12-22',17.22)
    ,         ( 40,'2010-12-23',17.24)
    ,         ( 41,'2010-12-27',17.23)
    ,         ( 42,'2010-12-28',17.29)
    ,         ( 43,'2010-12-29',17.32)
    ,         ( 44,'2010-12-30',17.2)
    ,         ( 45,'2010-12-31',17.19)
    ,         ( 46,'2011-01-03',17.07)
    ,         ( 47,'2011-01-04',17.09)
    ,         ( 48,'2011-01-05',17.12)
    ,         ( 49,'2011-01-06',17.25)
    ,         ( 50,'2011-01-07',17.45)
    ,         ( 51,'2011-01-10',17.49)
    ,         ( 52,'2011-01-11',17.45)
    ,         ( 53,'2011-01-12',17.44)
    ,         ( 54,'2011-01-13',17.45)
    ,         ( 55,'2011-01-14',17.55)
    ,         ( 56,'2011-01-18',17.56)
    ,         ( 57,'2011-01-19',17.56)
    ,         ( 58,'2011-01-20',17.46)
    ,         ( 59,'2011-01-21',17.53)
    ,         ( 60,'2011-01-24',17.44)
    ,         ( 61,'2011-01-25',17.3)
    ,         ( 62,'2011-01-26',17.29)
    ,         ( 63,'2011-01-27',17.53)
    ,         ( 64,'2011-01-28',17.55)
    ,         ( 65,'2011-01-31',17.63)
    ,         ( 66,'2011-02-01',17.78)
    ,         ( 67,'2011-02-02',17.85)
    ,         ( 68,'2011-02-03',17.75)
    ,         ( 69,'2011-02-04',17.94)
    ,         ( 70,'2011-02-07',17.98)
    ,         ( 71,'2011-02-08',17.96)
    ,         ( 72,'2011-02-09',17.88)
    ,         ( 73,'2011-02-10',18.03)
    ,         ( 74,'2011-02-11',18.05)
    ,         ( 75,'2011-02-14',17.93)
    ,         ( 76,'2011-02-15',17.96)
    ,         ( 77,'2011-02-16',17.83)
    ,         ( 78,'2011-02-17',17.83)
    ,         ( 79,'2011-02-18',17.76)
    ,         ( 80,'2011-02-22',17.86)
    ,         ( 81,'2011-02-23',17.96)
    ,         ( 82,'2011-02-24',18.22)
    ,         ( 83,'2011-02-25',18.11)
    ,         ( 84,'2011-02-28',18.27)
    ,         ( 85,'2011-03-01',18.12)
    ,         ( 86,'2011-03-02',18.22)
    ,         ( 87,'2011-03-03',18.27)
    ,         ( 88,'2011-03-04',18.27)
    ,         ( 89,'2011-03-07',18.29)
    ,         ( 90,'2011-03-08',18.29)
    ,         ( 91,'2011-03-09',18.18)
    ,         ( 92,'2011-03-10',18.26)
    ,         ( 93,'2011-03-11',18.44)
    ,         ( 94,'2011-03-14',18.52)
    ,         ( 95,'2011-03-15',18.56)
    ,         ( 96,'2011-03-16',18.68)
    ,         ( 97,'2011-03-17',18.6)
    ,         ( 98,'2011-03-18',18.71)
    ,         ( 99,'2011-03-21',18.69)
    ,         (100,'2011-03-22',18.69)
    ,         (101,'2011-03-23',18.67)
    ,         (102,'2011-03-24',18.72)
    ,         (103,'2011-03-25',18.78)
    ,         (104,'2011-03-28',18.77)
    ,         (105,'2011-03-29',18.79)
    ,         (106,'2011-03-30',18.6)
    ,         (107,'2011-03-31',18.88)
    ,         (108,'2011-04-01',18.93)
    ,         (109,'2011-04-04',19.14)
    ,         (110,'2011-04-05',19.26)
    ,         (111,'2011-04-06',19.4)
    ,         (112,'2011-04-07',19.25)
    ,         (113,'2011-04-08',18.98)
    ,         (114,'2011-04-11',18.93)
    ,         (115,'2011-04-12',18.97)
    ,         (116,'2011-04-13',18.92)
    ,         (117,'2011-04-14',18.83)
    ,         (118,'2011-04-15',19)
    ,         (119,'2011-04-18',19.16)
    ,         (120,'2011-04-19',19.14)
    ,         (121,'2011-04-20',18.11)
    ,         (122,'2011-04-21',17.91)
    ,         (123,'2011-04-25',18.11)
    ,         (124,'2011-04-26',17.44)
    ,         (125,'2011-04-27',17.69)
    ,         (126,'2011-04-28',17.64)
    ,         (127,'2011-04-29',17.81)
    ,         (128,'2011-05-02',17.91)
    ,         (129,'2011-05-03',17.67)
    ,         (130,'2011-05-04',17.59)
    ,         (131,'2011-05-05',17.32)
    ,         (132,'2011-05-06',16.87)
    ,         (133,'2011-05-09',16.69)
    ,         (134,'2011-05-10',16.87)
    ,         (135,'2011-05-11',16.68)
    ,         (136,'2011-05-12',17.06)
    ,         (137,'2011-05-13',17.36)
    ,         (138,'2011-05-16',17.61)
    ,         (139,'2011-05-17',17.59)
    ,         (140,'2011-05-18',17.48)
    ,         (141,'2011-05-19',17.23)
    ,         (142,'2011-05-20',17.27)
    ,         (143,'2011-05-23',17.5)
    ,         (144,'2011-05-24',17.69)
    ,         (145,'2011-05-25',17.84)
    ,         (146,'2011-05-26',18.02)
    ,         (147,'2011-05-27',17.95)
    ,         (148,'2011-05-31',17.99)
    ,         (149,'2011-06-01',18.14)
    ,         (150,'2011-06-02',18.41)
    ,         (151,'2011-06-03',18.46)
    ,         (152,'2011-06-06',18.61)
    ,         (153,'2011-06-07',18.48)
    ,         (154,'2011-06-08',18.28)
    ,         (155,'2011-06-09',18.29)
    ,         (156,'2011-06-10',18.32)
    ,         (157,'2011-06-13',18.34)
    ,         (158,'2011-06-14',18.54)
    ,         (159,'2011-06-15',18.68)
    ,         (160,'2011-06-16',18.7)
    ,         (161,'2011-06-17',18.68)
    ,         (162,'2011-06-20',18.82)
    ,         (163,'2011-06-21',18.79)
    ,         (164,'2011-06-22',18.54)
    ,         (165,'2011-06-23',18.53)
    ,         (166,'2011-06-24',18.54)
    ,         (167,'2011-06-27',18.43)
    ,         (168,'2011-06-28',18.42)
    ,         (169,'2011-06-29',18.55)
    ,         (170,'2011-06-30',18.68)
    ,         (171,'2011-07-01',18.54)
    ,         (172,'2011-07-05',18.59)
    ,         (173,'2011-07-06',18.75)
    ,         (174,'2011-07-07',18.61)
    ,         (175,'2011-07-08',18.87)
    ,         (176,'2011-07-11',19.09)
    ,         (177,'2011-07-12',19.18)
    ,         (178,'2011-07-13',19.29)
    ,         (179,'2011-07-14',19.37)
    ,         (180,'2011-07-15',19.31)
    ,         (181,'2011-07-18',19.21)
    ,         (182,'2011-07-19',19.37)
    ,         (183,'2011-07-20',19.44)
    ,         (184,'2011-07-21',19.42)
    ,         (185,'2011-07-22',19.14)
    ,         (186,'2011-07-25',19.41)
    ,         (187,'2011-07-26',19.42)
    ,         (188,'2011-07-27',19.45)
    ,         (189,'2011-07-28',19.69)
    ,         (190,'2011-07-29',19.66)
    ,         (191,'2011-08-01',20)
    ,         (192,'2011-08-02',19.89)
    ,         (193,'2011-08-03',20.12)
    ,         (194,'2011-08-04',20)
    ,         (195,'2011-08-05',19.78)
    ,         (196,'2011-08-08',19.86)
    ,         (197,'2011-08-09',19.85)
    ,         (198,'2011-08-10',19.71)
    ,         (199,'2011-08-11',19.89)
    ,         (200,'2011-08-12',20.01)
    GO
    
    SELECT (this.Price - prev.Price) / prev.Price
    ,  (this.Price - ma.Price) / ma.Price
    ,  DATEDIFF(day, ch.PriceDate, this.PriceDate) AS days
    ,  ch.PriceDate
    ,  *
       FROM PriceData AS this
       LEFT JOIN PriceData AS prev
          ON (prev.PriceDate = (SELECT MAX(z1.PriceDate)
             FROM PriceData AS z1
             WHERE z1.PriceDate < this.PriceDate))
       LEFT JOIN PriceData AS ma
          ON (ma.PriceDate = (SELECT MAX(z2.PriceDate)
             FROM PriceData AS z2
             WHERE z2.PriceDate < DateAdd(day, -14, this.PriceDate)))
       LEFT JOIN PriceData AS ch
          ON (ch.PriceDate = (SELECT Max(z3.PriceDate)
             FROM PriceData AS z3
             INNER JOIN PriceData AS z4
                ON (z4.PriceDate = (SELECT Max(z5.PriceDate)
                   FROM PriceData AS z5
                   WHERE  z5.PriceDate < z3.PriceDate))
             WHERE  z3.PriceDate < this.PriceDate
                AND (  (this.Price = prev.Price AND NOT (z3.Price = z4.Price))
                   OR  (this.Price < prev.Price AND NOT (z3.Price < z4.Price))
                   OR  (this.Price > prev.Price AND NOT (z3.Price > z4.Price))
                     )
                  ))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jul 2011
    Posts
    12

    Code and data

    Hi Pat

    Thanks for your reply! I was afraid you left the forum!

    I have just run the code you gave me but are confused about the results I am getting. It is a given that I don't understand the code so that might be part of the reason but I am unfortunately also not seeing the results I was expecting. The expected results for the first few lines should be the following if these are the columns that you are calculating with the code:

    MoveForDay Direction & count MoveOfSeries

    0.011405 UP-1 -
    0.004748 UP-2 -
    0.002953 UP-3 -
    0.005889 UP-4 0.02521
    -0.002927 DOWN-1 -0.00293
    0.003523 UP-1 0.00352
    -0.019310 DOWN-1 -
    -0.005370 DOWN-2 -0.02458
    0.002999 UP-1 -
    0.008373 UP-2 0.01140
    -0.000593 DOWN-1 -0.00059
    0.001187 UP-1 -
    0.001778 UP-2 -
    0.001183 UP-3 0.00415
    -0.003546 DOWN-1 -
    -0.007117 DOWN-2 -
    -0.000597 DOWN-3 -0.01123


    Thanks
    Ohann

  12. #12
    Join Date
    Jul 2011
    Posts
    12

    Weekend days

    Hi Pat

    Me again! With regards to the weekend days issue on the MA, would it not be possible to use the PriceRowNo somehow and use that - 14 to calculate the MA rather than using the date - not as if I know how!

  13. #13
    Join Date
    Jul 2011
    Posts
    12

    My post of last night

    Pat,

    One more thing regarding my post last night - I showed the expected results for the first few lines but forgot to include the MA data.

    Thanks

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to understand why you've calculated the MoveOfSeries value incorrectly before you continue any further. The problem is that you've made an unwaranted assumption which your data does not support... Go back and look at the data, the problem ought to jump out at you.

    Explain for me how you compute the moving average. Think hard about it as you explain, I've been down this road many times and only a few people grok the flaw that most people embrace when using code to calculate it because they've computed it too many times with a spreadsheet and they repeat the same flawed computation out of habit.

    This code isn't perfect, but it is pretty darned good. It may need some tuning in the expressions in the final SELECT statement to accomodate the analytical needs of your process. You need to understand how this code works... Trying to just use it verbatim without understanding it will lead you to make bad decisions because you don't understand the nuances of the computation and how those nuances affect the results.
    Code:
    DROP TABLE PriceData
    GO
    
    CREATE TABLE PriceData (
       PriceRowNo       INT         NOT NULL
       CONSTRAINT XPKPriceData
          PRIMARY KEY CLUSTERED (PriceRowNo)
    ,  PriceDate        DATETIME    NOT NULL
    ,  Price            MONEY       NOT NULL
    )
    GO
    
    CREATE INDEX XIE01PriceData ON PriceData (PriceDate)
    GO
    
    INSERT INTO PriceData
       VALUES (  1,'2010-10-28',16.66)
    ,         (  2,'2010-10-29',16.85)
    ,         (  3,'2010-11-01',16.93)
    ,         (  4,'2010-11-02',16.98)
    ,         (  5,'2010-11-03',17.08)
    ,         (  6,'2010-11-04',17.03)
    ,         (  7,'2010-11-05',17.09)
    ,         (  8,'2010-11-08',16.76)
    ,         (  9,'2010-11-09',16.67)
    ,         ( 10,'2010-11-10',16.72)
    ,         ( 11,'2010-11-11',16.86)
    ,         ( 12,'2010-11-12',16.85)
    ,         ( 13,'2010-11-15',16.87)
    ,         ( 14,'2010-11-16',16.9)
    ,         ( 15,'2010-11-17',16.92)
    ,         ( 16,'2010-11-18',16.86)
    ,         ( 17,'2010-11-19',16.74)
    ,         ( 18,'2010-11-22',16.73)
    ,         ( 19,'2010-11-23',16.82)
    ,         ( 20,'2010-11-24',17.02)
    ,         ( 21,'2010-11-26',17.05)
    ,         ( 22,'2010-11-29',17.05)
    ,         ( 23,'2010-11-30',17.23)
    ,         ( 24,'2010-12-01',17.29)
    ,         ( 25,'2010-12-02',17.32)
    ,         ( 26,'2010-12-03',17.23)
    ,         ( 27,'2010-12-06',17.21)
    ,         ( 28,'2010-12-07',17.28)
    ,         ( 29,'2010-12-08',17.24)
    ,         ( 30,'2010-12-09',17.06)
    ,         ( 31,'2010-12-10',17.06)
    ,         ( 32,'2010-12-13',16.99)
    ,         ( 33,'2010-12-14',17.15)
    ,         ( 34,'2010-12-15',17.2)
    ,         ( 35,'2010-12-16',17.17)
    ,         ( 36,'2010-12-17',17.21)
    ,         ( 37,'2010-12-20',17.28)
    ,         ( 38,'2010-12-21',17.28)
    ,         ( 39,'2010-12-22',17.22)
    ,         ( 40,'2010-12-23',17.24)
    ,         ( 41,'2010-12-27',17.23)
    ,         ( 42,'2010-12-28',17.29)
    ,         ( 43,'2010-12-29',17.32)
    ,         ( 44,'2010-12-30',17.2)
    ,         ( 45,'2010-12-31',17.19)
    ,         ( 46,'2011-01-03',17.07)
    ,         ( 47,'2011-01-04',17.09)
    ,         ( 48,'2011-01-05',17.12)
    ,         ( 49,'2011-01-06',17.25)
    ,         ( 50,'2011-01-07',17.45)
    ,         ( 51,'2011-01-10',17.49)
    ,         ( 52,'2011-01-11',17.45)
    ,         ( 53,'2011-01-12',17.44)
    ,         ( 54,'2011-01-13',17.45)
    ,         ( 55,'2011-01-14',17.55)
    ,         ( 56,'2011-01-18',17.56)
    ,         ( 57,'2011-01-19',17.56)
    ,         ( 58,'2011-01-20',17.46)
    ,         ( 59,'2011-01-21',17.53)
    ,         ( 60,'2011-01-24',17.44)
    ,         ( 61,'2011-01-25',17.3)
    ,         ( 62,'2011-01-26',17.29)
    ,         ( 63,'2011-01-27',17.53)
    ,         ( 64,'2011-01-28',17.55)
    ,         ( 65,'2011-01-31',17.63)
    ,         ( 66,'2011-02-01',17.78)
    ,         ( 67,'2011-02-02',17.85)
    ,         ( 68,'2011-02-03',17.75)
    ,         ( 69,'2011-02-04',17.94)
    ,         ( 70,'2011-02-07',17.98)
    ,         ( 71,'2011-02-08',17.96)
    ,         ( 72,'2011-02-09',17.88)
    ,         ( 73,'2011-02-10',18.03)
    ,         ( 74,'2011-02-11',18.05)
    ,         ( 75,'2011-02-14',17.93)
    ,         ( 76,'2011-02-15',17.96)
    ,         ( 77,'2011-02-16',17.83)
    ,         ( 78,'2011-02-17',17.83)
    ,         ( 79,'2011-02-18',17.76)
    ,         ( 80,'2011-02-22',17.86)
    ,         ( 81,'2011-02-23',17.96)
    ,         ( 82,'2011-02-24',18.22)
    ,         ( 83,'2011-02-25',18.11)
    ,         ( 84,'2011-02-28',18.27)
    ,         ( 85,'2011-03-01',18.12)
    ,         ( 86,'2011-03-02',18.22)
    ,         ( 87,'2011-03-03',18.27)
    ,         ( 88,'2011-03-04',18.27)
    ,         ( 89,'2011-03-07',18.29)
    ,         ( 90,'2011-03-08',18.29)
    ,         ( 91,'2011-03-09',18.18)
    ,         ( 92,'2011-03-10',18.26)
    ,         ( 93,'2011-03-11',18.44)
    ,         ( 94,'2011-03-14',18.52)
    ,         ( 95,'2011-03-15',18.56)
    ,         ( 96,'2011-03-16',18.68)
    ,         ( 97,'2011-03-17',18.6)
    ,         ( 98,'2011-03-18',18.71)
    ,         ( 99,'2011-03-21',18.69)
    ,         (100,'2011-03-22',18.69)
    ,         (101,'2011-03-23',18.67)
    ,         (102,'2011-03-24',18.72)
    ,         (103,'2011-03-25',18.78)
    ,         (104,'2011-03-28',18.77)
    ,         (105,'2011-03-29',18.79)
    ,         (106,'2011-03-30',18.6)
    ,         (107,'2011-03-31',18.88)
    ,         (108,'2011-04-01',18.93)
    ,         (109,'2011-04-04',19.14)
    ,         (110,'2011-04-05',19.26)
    ,         (111,'2011-04-06',19.4)
    ,         (112,'2011-04-07',19.25)
    ,         (113,'2011-04-08',18.98)
    ,         (114,'2011-04-11',18.93)
    ,         (115,'2011-04-12',18.97)
    ,         (116,'2011-04-13',18.92)
    ,         (117,'2011-04-14',18.83)
    ,         (118,'2011-04-15',19)
    ,         (119,'2011-04-18',19.16)
    ,         (120,'2011-04-19',19.14)
    ,         (121,'2011-04-20',18.11)
    ,         (122,'2011-04-21',17.91)
    ,         (123,'2011-04-25',18.11)
    ,         (124,'2011-04-26',17.44)
    ,         (125,'2011-04-27',17.69)
    ,         (126,'2011-04-28',17.64)
    ,         (127,'2011-04-29',17.81)
    ,         (128,'2011-05-02',17.91)
    ,         (129,'2011-05-03',17.67)
    ,         (130,'2011-05-04',17.59)
    ,         (131,'2011-05-05',17.32)
    ,         (132,'2011-05-06',16.87)
    ,         (133,'2011-05-09',16.69)
    ,         (134,'2011-05-10',16.87)
    ,         (135,'2011-05-11',16.68)
    ,         (136,'2011-05-12',17.06)
    ,         (137,'2011-05-13',17.36)
    ,         (138,'2011-05-16',17.61)
    ,         (139,'2011-05-17',17.59)
    ,         (140,'2011-05-18',17.48)
    ,         (141,'2011-05-19',17.23)
    ,         (142,'2011-05-20',17.27)
    ,         (143,'2011-05-23',17.5)
    ,         (144,'2011-05-24',17.69)
    ,         (145,'2011-05-25',17.84)
    ,         (146,'2011-05-26',18.02)
    ,         (147,'2011-05-27',17.95)
    ,         (148,'2011-05-31',17.99)
    ,         (149,'2011-06-01',18.14)
    ,         (150,'2011-06-02',18.41)
    ,         (151,'2011-06-03',18.46)
    ,         (152,'2011-06-06',18.61)
    ,         (153,'2011-06-07',18.48)
    ,         (154,'2011-06-08',18.28)
    ,         (155,'2011-06-09',18.29)
    ,         (156,'2011-06-10',18.32)
    ,         (157,'2011-06-13',18.34)
    ,         (158,'2011-06-14',18.54)
    ,         (159,'2011-06-15',18.68)
    ,         (160,'2011-06-16',18.7)
    ,         (161,'2011-06-17',18.68)
    ,         (162,'2011-06-20',18.82)
    ,         (163,'2011-06-21',18.79)
    ,         (164,'2011-06-22',18.54)
    ,         (165,'2011-06-23',18.53)
    ,         (166,'2011-06-24',18.54)
    ,         (167,'2011-06-27',18.43)
    ,         (168,'2011-06-28',18.42)
    ,         (169,'2011-06-29',18.55)
    ,         (170,'2011-06-30',18.68)
    ,         (171,'2011-07-01',18.54)
    ,         (172,'2011-07-05',18.59)
    ,         (173,'2011-07-06',18.75)
    ,         (174,'2011-07-07',18.61)
    ,         (175,'2011-07-08',18.87)
    ,         (176,'2011-07-11',19.09)
    ,         (177,'2011-07-12',19.18)
    ,         (178,'2011-07-13',19.29)
    ,         (179,'2011-07-14',19.37)
    ,         (180,'2011-07-15',19.31)
    ,         (181,'2011-07-18',19.21)
    ,         (182,'2011-07-19',19.37)
    ,         (183,'2011-07-20',19.44)
    ,         (184,'2011-07-21',19.42)
    ,         (185,'2011-07-22',19.14)
    ,         (186,'2011-07-25',19.41)
    ,         (187,'2011-07-26',19.42)
    ,         (188,'2011-07-27',19.45)
    ,         (189,'2011-07-28',19.69)
    ,         (190,'2011-07-29',19.66)
    ,         (191,'2011-08-01',20)
    ,         (192,'2011-08-02',19.89)
    ,         (193,'2011-08-03',20.12)
    ,         (194,'2011-08-04',20)
    ,         (195,'2011-08-05',19.78)
    ,         (196,'2011-08-08',19.86)
    ,         (197,'2011-08-09',19.85)
    ,         (198,'2011-08-10',19.71)
    ,         (199,'2011-08-11',19.89)
    ,         (200,'2011-08-12',20.01)
    GO
    
    SELECT (this.Price - prev.Price) / prev.Price AS MoveForDay
    ,  CASE
          WHEN this.Price = prev.Price THEN 'Same'
          WHEN this.Price < prev.Price THEN 'Down'
          WHEN this.Price > prev.Price THEN ' Up '
          ELSE '????'
       END AS direction
    ,  DATEDIFF(day,  ch.PriceDate, this.PriceDate)
    -  DATEDIFF(week, ch.PriceDate, this.PriceDate) * 2 AS MoveOfSeries
       FROM PriceData AS this
       LEFT JOIN PriceData AS prev
          ON (prev.PriceDate = (SELECT MAX(z1.PriceDate)
             FROM PriceData AS z1
             WHERE z1.PriceDate < this.PriceDate))
       LEFT JOIN PriceData AS ma
          ON (ma.PriceDate = (SELECT MAX(z2.PriceDate)
             FROM PriceData AS z2
             WHERE z2.PriceDate < DateAdd(day, -14, this.PriceDate)))
       LEFT JOIN PriceData AS ch
          ON (ch.PriceDate = (SELECT Max(z3.PriceDate)
             FROM PriceData AS z3
             INNER JOIN PriceData AS z4
                ON (z4.PriceDate = (SELECT Max(z5.PriceDate)
                   FROM PriceData AS z5
                   WHERE  z5.PriceDate < z3.PriceDate))
             WHERE  z3.PriceDate < this.PriceDate
                AND (  (this.Price = prev.Price AND NOT (z3.Price = z4.Price))
                   OR  (this.Price < prev.Price AND NOT (z3.Price < z4.Price))
                   OR  (this.Price > prev.Price AND NOT (z3.Price > z4.Price))
                     )
                  ))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Jul 2011
    Posts
    12

    Issues

    Hi Pat

    I have looked at my MoveOfSeries calculation. Is the problem the fact that in the calculation I effectively assume I know what the next move is as I based it on historical data whereas going forward this will obviously not be the case? For example in the first calculation I don't show any values next to UP1,2 and 3 although in reality I wouldn't know what tomorrow's move would be. Let me know if this is what you are referring to.

    With regards to the 14 day MA I would normally just add the last 14 instances and divide by 14. Again the issue here might be the fact that the MA should be based on the previous days closing price as today's price is still to be determined.

    I really hope this is what you are referring to.

    Thanks
    Ohann

Posting Permissions

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