Adeel Khan Adeel Khan - 6 months ago 11
SQL Question

how to add data two colums add after add two add next

i have following query i want add two records like 16+16 = 32 + 15 = 47 like this i wANT 30 + 15 , = 47 + 15 like this ,

WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY gps_time) AS rn
FROM rawtTackHistory_A2Z where car_id = 12956
)
SELECT mc.gps_time,DATEDIFF(second, mc.gps_time, mp.gps_time)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1


enter image description here

Answer

I'm not 100% sure what you're asking, but it sounds like you want a cumulative sum. That's a question that's been answered already:

http://stackoverflow.com/a/2120639/2565840

EDIT: in your case I think the query below should work

WITH    
rows AS (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY gps_time) AS rn
        FROM    rawtTackHistory_A2Z where car_id = 12956 
),
differences AS (
    SELECT  mc.rn, mc.gps_time,DATEDIFF(second, mc.gps_time, mp.gps_time) time_diff
    FROM    rows mc
    JOIN    rows mp
    ON      mc.rn = mp.rn - 1
)
SELECT t1.gps_time, t1.time_diff, SUM(t2.time_diff) time_sum
FROM differences t1
INNER JOIN differences t2 
ON t1.rn >= t2.rn
GROUP BY t1.rn, t1.gps_time, t1.time_diff
ORDER BY t1.rn

or if you're using SQL Server 2012 or later, this should run quicker:

SELECT gps_time
     , DATEDIFF(second, LAG(gps_time) OVER (ORDER BY gps_time), gps_time) time_diff
     , DATEDIFF(second, MIN(gps_time) OVER (ORDER BY gps_time), gps_time) time_sum
FROM rawtTackHistory_A2Z 
ORDER BY gps_time

It's using a windowing clause (OVER). More detail here: https://msdn.microsoft.com/en-us/library/ms189461.aspx