Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Exclamation Unanswered: Difference over time

    Hi

    Apologies in advance if something is wrong as I am quite the novice.
    I would like to calculate the difference in a variable over the previous year.

    example if I have a table with the price of cheese at the start of each year how would i run a loop that would tell me that change in price from one year to the next.

    my data would look like this

    Table: Cheese

    Name Price Date
    Cheese 1.00 01/01/2000
    Cheese 1.10 01/01/2001
    Cheese 0.90 01/01/2002
    Cheese 1.00 01/01/2003


    I would like the output to tbe

    Name Price Change Date
    Cheese 1.00 0 01/01/2000
    Cheese 1.10 0.10 01/01/2001
    Cheese 0.90 -0.20 01/01/2002
    Cheese 1.00 0.1 01/01/2003


    I appreciate all the help and effort

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    SELECT name,
           price,
           price - lag(price) over (partition by name order by "date" desc) as change
           "date"
    FROM cheese
    Note I quoted "date" because that is a reserved word.

  3. #3
    Join Date
    Nov 2009
    Posts
    6

    Thank you so much

    Another question

    Just out of interest how would I increase the lag?
    i.e if I had to look back two or three periods instead of just one?


    Regards
    Christo

  4. #4
    Join Date
    May 2008
    Posts
    277
    That's totally different from how I'd do it.

    Code:
    select
        name,
        o.price,
        o.price - c.price as change,
        c."date"
    from
        cheese as o
        inner join cheese as c
            using (name)
    where
        o."date" = date '01/01/2000'
        and c."date" >= o."date
    order by c."date"
    Just change the date for o in order to adjust the time period being displayed.

Tags for this Thread

Posting Permissions

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