Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Question Unanswered: Help with Time Series Fluctuation???

    Hi All,
    I have a table Test1:
    ID date Value
    AAUGVAL 2/27/1987 60.848
    AAUGVAL 3/2/1987 64.288
    AAUGVAL 3/3/1987 63.77
    AAUGVAL 3/4/1987 62.495
    AAUGVAL 3/5/1987 62.65
    AAUGVAL 3/6/1987 62.548
    AAUGVAL 3/9/1987 62.292
    AAUGVAL 3/10/1987 63.045
    AAUGVAL 3/11/1987 63.021
    ....
    I am trying to see the value % changes day by day and here is is the code I wrote:
    select
    starttime=cast(v.date as char(8)), endtime=cast(a.date as char(8)),
    startval=v.Value, endval=a.Value,
    change=substring('- +', sign((a.Value-v.Value)+2,1)+ cast(abs(a.Value-v.Value) as varchar)
    from
    (select date,Value, ranking =(select count(distinct date) from Test1 T where T.Value<=S.Value)
    from Test1 S) v left outer join
    (select date,Value, ranking=(select count(distinct date) from Test1 T where T.Value<=S.Value)
    from Test1 S) a
    on( a.ranking=v.ranking+1)

    I got the following error message:
    Server: Msg 174, Level 15, State 1, Line 4
    The sign function requires 1 arguments.
    Server: Msg 170, Level 15, State 1, Line 7
    Line 7: Incorrect syntax near 'v'.
    Server: Msg 170, Level 15, State 1, Line 9
    Line 9: Incorrect syntax near 'a'.

    Could someone please help with this? Thank you in advance!
    shiparsons

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you merely had an extra left parenthesis, that's all
    Code:
    select starttime=cast(v.date as char(8))
         , endtime=cast(a.date as char(8))
         , startval=v.Value
         , endval=a.Value
         , change=substring('- +', sign(a.Value-v.Value)+2, 1)
                    + cast(abs(a.Value-v.Value) as varchar)
      from (
           select date
                , Value
                , ranking =
                  ( select count(distinct date) 
                      from Test1 T 
                     where T.Value <= S.Value )
             from Test1 S
           ) v 
    left outer 
      join (
           select date
                , Value
                , ranking =
                  ( select count(distinct date) 
                      from Test1 T 
                     where T.Value <= S.Value )
             from Test1 S
           ) a
        on a.ranking
         = v.ranking + 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    25
    It would be easier if you used DATEDIFF( datepart , startdate , enddate )

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by db0
    It would be easier if you used DATEDIFF( datepart , startdate , enddate )
    could you please show how?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Datediff?

    I don't see it.

    But I do have to ask why you are ranking/sorting your data by VALUE, when your post mentions comparing to the most recent prior DATE.

    Also, save your formatting for your reporting tool or user interface.

    Also, what you are calculating is the "change in percentage", not the "percentage change".

    Here is an alternate method that compares values for each day to the value of the prior record, sorted by date.

    This method works without the use of subqueries in the SELECT clause, which are ineffecient and should be avoided.

    Code:
    select	PriorValues.startdate,
    	Test1.date as enddate,
    	PriorValues.startvalue,
    	Test1.value as endvalue,
    	change = Test1.value - PriorValues.startvalue
    from	Test1
    	inner join --PriorValues
    		(select	StartTest.date as startdate,
    			Min(EndTest.date) as enddate,
    			StartTest.value as startvalue
    		from	Test1 StartTest
    			inner join Test1 EndTest on StartTest.date < EndTest.date
    		group by StartTest.date,
    			StartTest.value) PriorValues
    		on Test1.date = PriorValues.enddate
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2005
    Posts
    88
    Thank you all your replies.

    Blindman, your codes works great!
    R937,
    I tried the code you posted and got the following error message.

    Server: Msg 8116, Level 16, State 1, Line 1
    Argument data type float is invalid for argument 2 of substring function.

    I am wondering what is wrong with the code. Would you please help?
    The [Value] field in Test table was defined as float.

    Thank you very much for the help!

  7. #7
    Join Date
    Feb 2005
    Posts
    88
    Blindman,
    If I want to insert a [where ID=' '] clause into the codes you posted, how/where I should put it so that I can compare daily fluctuation for one time series.

    Thank you for the help!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This should return the same data, separated by ID. You can use the entire resultset, or filter for a specific Test1.ID in your WHERE clause:

    Code:
    select	Test1.ID,
    	PriorValues.startdate,
    	Test1.date as enddate,
    	PriorValues.startvalue,
    	Test1.value as endvalue,
    	change = Test1.value - PriorValues.startvalue
    from	Test1
    	inner join --PriorValues
    		(select	StartTest.ID,
    			StartTest.date as startdate,
    			Min(EndTest.date) as enddate,
    			StartTest.value as startvalue
    		from	Test1 StartTest
    			inner join Test1 EndTest
    				on StartTest.ID = EndTest.ID
    				and StartTest.date < EndTest.date
    		group by StartTest.ID,
    			StartTest.date,
    			StartTest.value) PriorValues
    		on Test1.ID = PriorValues.ID
    		and Test1.date = PriorValues.enddate
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2005
    Posts
    88
    Blindman,
    You are the best! The codes work exactly as what I expect! I have a lot of values ='-999.999' in the table, is there a way I can exclude them out of the comparison?

    Thank you in advance for the help!
    shiparsons

Posting Permissions

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