SQL Finds a Delta
December 7, 2011 Hey, Ted
|
Is there a way using SQL to calculate the amount a numeric column changes from one row to the next? –Ken Yes. Here’s how it’s done. First, you need a table to use for an example.
create table mydata
( Name char(8), Qty dec(3,0))
insert into mydata values
('Bob', 25),
('Bill', 85),
('Sue', 17),
('Bob', 28),
('Bob', 32),
('Bill', 105),
('Sue', 45)
To pull this off, the data must be stored in the order in which it is to be retrieved. If necessary, create a temporary table to hold the sorted data.
declare global temporary table SortedData as
(select name, qty
from mydata
order by name, qty)
with data
Join each row to the one before it.
with NumberedData as
(select rrn(x) as seq, x.name, x.qty
from session/SortedData as x)
select after.name,
coalesce(before.qty,0) as before,
after.qty as after,
after.qty - coalesce(before.qty,0) as change
from NumberedData as after
left join NumberedData as before
on after.name = before.name
and after.seq = before.seq + 1
NumberedData is a common table expression. It creates a temporary table in memory, with three columns: relative record number, name, and quantity. You have to have relative record number in order to join one row of NumberedData to another row of NumberedData. If you could see NumberedData, it would look like this: SEQ NAME QTY 1 Bill 85 2 Bill 105 3 Bob 25 4 Bob 28 5 Bob 32 6 Sue 17 7 Sue 45 In this example, I’m tracking the change for each person. I want to see the change between records 1 and 2, but not between 2 and 3. For that reason, the first part of the join compares the name columns of two rows to be sure they match. The second part of the join checks to see that the “before” row is numbered one less than the “after” row. Voilà! NAME BEFORE AFTER CHANGE Bill 0 85 85 Bill 85 105 20 Bob 0 25 25 Bob 25 28 3 Bob 28 32 4 Sue 0 17 17 Sue 17 45 28 –Ted
|
