sql2008 query

marky101
marky101 used Ask the Experts™
on
need query that uses previous record and current record in calculation

ex select  * ,   t1.amount - t2.amount  from customers as t1 inner join
t1.id = t2.id customers as t2

something along these lines
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Please post a few rows of sample data from the customers table, and the result you would expect given that sample.

Author

Commented:
date            time              customer     totals   diff
1/4/2012   10:00am         jSmith          110        0
1/4/2012   10:05am         jSmith          120      10
1/4/2012   10:10am         jSmith          150      30


i want to have a query where I can access the current and previous totals fields to produce the diff field ..diff field is blank right now and i want to update it

diff = current record - previous record

their is no identity field for the primary key, otherwise i could have a solution already :-)


thanks

mark
Commented:
;with CTE as
         (select date, time, customer, totals,
          ROW_NUMBER() OVER(PARTITION BY null ORDER BY date, time ) as ranking
          from YourTable
         )
select t1.date,t1.time,t1.customer,t1.totals,isnull((t1.totals-t2.ranking),0) as diff
from
        CTE t1
        inner join CTE t2
        on t1.ranking = t2.ranking+1
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Top Expert 2010

Commented:
Kicking it old school, without the CTE:

CREATE TABLE customers ([date] datetime, [time] datetime, customer varchar(100), totals money)

INSERT INTO customers ([date], [time], customer, totals)
SELECT '2012-01-04', '10:00:00', 'jSmith', 110 UNION ALL
SELECT '2012-01-04', '10:05:00', 'jSmith', 120 UNION ALL
SELECT '2012-01-04', '10:10:00', 'jSmith', 150 UNION ALL
SELECT '2012-01-05', '10:00:00', 'jSmith', 200 UNION ALL
SELECT '2012-02-01', '10:00:00', 'bMiller', 110 UNION ALL
SELECT '2012-04-01', '18:30:00', 'bMiller', 170

SELECT t1.date, t1.time, t1.customer, t1.totals, COALESCE(t1.totals - 
    (SELECT TOP 1 t2.totals
    FROM customers t2
    WHERE t2.customer = t1.customer AND (t2.date + t2.time) < (t1.date + t1.time)
    ORDER BY (t2.date + t2.time) DESC), 0) AS diff
FROM customers t1
ORDER BY t1.customer, t1.date, t1.time

DROP TABLE customers

Open in new window


That returns:

2012-02-01 00:00:00.000	1900-01-01 10:00:00.000	bMiller	110.00	0.00
2012-04-01 00:00:00.000	1900-01-01 18:30:00.000	bMiller	170.00	60.00
2012-01-04 00:00:00.000	1900-01-01 10:00:00.000	jSmith	110.00	0.00
2012-01-04 00:00:00.000	1900-01-01 10:05:00.000	jSmith	120.00	10.00
2012-01-04 00:00:00.000	1900-01-01 10:10:00.000	jSmith	150.00	30.00
2012-01-05 00:00:00.000	1900-01-01 10:00:00.000	jSmith	200.00	50.00

Open in new window


It would be easier (and better, performance-wise), if date and time were combined in a single column:

CREATE TABLE customers ([datetime] datetime, customer varchar(100), totals money)

INSERT INTO customers ([datetime], customer, totals)
SELECT '2012-01-04 10:00:00', 'jSmith', 110 UNION ALL
SELECT '2012-01-04 10:05:00', 'jSmith', 120 UNION ALL
SELECT '2012-01-04 10:10:00', 'jSmith', 150 UNION ALL
SELECT '2012-01-05 10:00:00', 'jSmith', 200 UNION ALL
SELECT '2012-02-01 10:00:00', 'bMiller', 110 UNION ALL
SELECT '2012-04-01 18:30:00', 'bMiller', 170

SELECT t1.[datetime], t1.customer, t1.totals, COALESCE(t1.totals - 
    (SELECT TOP 1 t2.totals
    FROM customers t2
    WHERE t2.customer = t1.customer AND t2.[datetime] < t1.[datetime]
    ORDER BY t2.[datetime] DESC), 0) AS diff
FROM customers t1
ORDER BY t1.customer, t1.[datetime]

DROP TABLE customers

Open in new window


Returns:

2012-02-01 10:00:00.000	bMiller	110.00	0.00
2012-04-01 18:30:00.000	bMiller	170.00	60.00
2012-01-04 10:00:00.000	jSmith	110.00	0.00
2012-01-04 10:05:00.000	jSmith	120.00	10.00
2012-01-04 10:10:00.000	jSmith	150.00	30.00
2012-01-05 10:00:00.000	jSmith	200.00	50.00

Open in new window

Author

Commented:
how to use this query to update the field , it displays the data , now i want to update the field

Commented:
where do you want to update it?

Author

Commented:
i want the query to update the field with the changed value
so i need an update query at the end of the existing query
but it is not working .. yet

Commented:
You mean to say your original table has diff column already? If that is the case than giving the exact schema with the primary key value specified will be helpful. Instead of doing select from CTE , we can join the CTE with the main table directly to update.

Anyway you always have option of storing the data fetched in temporary table and then using it with any related tables to do any processing

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial