Professional Web Applications Themes

calculating the difference between two rows - Microsoft SQL / MS SQL Server

Every weekday a row gets appended to my table and I would like to calculate the difference between the current row and previous row for each column. My data looks like this: count(1)count(2)count(3)count(4)load_date 7148 7148 6028 7148 7/30/2003 7159 7159 6036 7159 8/1/2003 7159 7159 6036 7159 8/2/2003 7159 7159 6036 7159 8/3/2003 7159 7159 6036 7159 8/4/2003 7160 7160 6037 7160 8/5/2003 7166 7166 6038 7166 8/6/2003 So the results would be: 11 11 8 11 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 6 6 1 6 What is the ...

  1. #1

    Default calculating the difference between two rows

    Every weekday a row gets appended to my table and I would
    like to calculate the difference between the current row
    and previous row for each column. My data looks like this:

    count(1)count(2)count(3)count(4)load_date
    7148 7148 6028 7148 7/30/2003
    7159 7159 6036 7159 8/1/2003
    7159 7159 6036 7159 8/2/2003
    7159 7159 6036 7159 8/3/2003
    7159 7159 6036 7159 8/4/2003
    7160 7160 6037 7160 8/5/2003
    7166 7166 6038 7166 8/6/2003

    So the results would be:

    11 11 8 11
    0 0 0 0
    0 0 0 0
    0 0 0 0
    1 1 1 1
    6 6 1 6


    What is the function for this calculation?

    Thanks in advance for any help.

    Helena


    Helena Guest

  2. #2

    Default Re: calculating the difference between two rows

    Helena,

    CREATE TABLE counts (
    count_1 INT NOT NULL,
    count_2 INT NOT NULL,
    count_3 INT NOT NULL,
    count_4 INT NOT NULL,
    load_dt DATETIME NOT NULL PRIMARY KEY);

    INSERT INTO counts VALUES (7148,7148,6028,7148,'2003-07-30');
    INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-01');
    INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-02');
    INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-03');
    INSERT INTO counts VALUES (7159,7159,6036,7159,'2003-08-04');
    INSERT INTO counts VALUES (7160,7160,6037,7160,'2003-08-05');
    INSERT INTO counts VALUES (7166,7166,6038,7166,'2003-08-06');

    SELECT
    curr.load_dt,
    curr.count_1 - prev.count_1 AS chg_1,
    curr.count_2 - prev.count_2 AS chg_1,
    curr.count_3 - prev.count_3 AS chg_1,
    curr.count_4 - prev.count_4 AS chg_1
    FROM
    counts AS curr,
    counts AS prev
    WHERE
    prev.load_dt = (
    SELECT MAX(load_dt)
    FROM counts
    WHERE load_dt < curr.load_dt)
    ORDER BY
    curr.load_dt;


    Hope that helps,
    Rich


    "Helena Silva" <com> wrote in message
    news:008301c35c3a$e836f440$gbl... 


    Rich Guest

  3. #3

    Default Re: calculating the difference between two rows

    If we can assume that you haver a row for every day, then you can use
    this:

    SELECT T.load_dt,
    (T.count_1 - Y.count_1) AS chg_1,
    (T.count_2 - Y.count_2) AS chg_2,
    (T.count_3 - Y.count_3) AS chg_3,
    (T.count_4 - Y.count_4) AS chg_4
    FROM Counts AS T, Counts AS Y -- today, yesterday
    WHERE T.load_dt = DATEADD(DD, 1, Y.load_dt);

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Replies: 7
    Last Post: September 2nd, 05:44 AM
  2. Replies: 3
    Last Post: September 20th, 12:38 PM
  3. Calculating the difference between two arrays
    By Nick Bown in forum PHP Development
    Replies: 1
    Last Post: September 1st, 11:50 AM
  4. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139