SQL Find Difference Between Previous and Current Row

SQL Find difference between previous and current row

See sqlFiddle

;WITH tblDifference AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers 
    FROM tableName
)

SELECT cur.columnOfNumbers, cur.columnOfNumbers - previous.columnOfNumbers
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1

sql query for difference between current row and previous row based on datetime

In SQL Server 2012+, you can use lag(). In SQL Server 2008, use apply:

select t.*,
       coalesce(t.cumulativeValue - tprev.cumulativeValue, t.cumulativeValue) as diff
from t outer apply
     (select top 1 tprev.*
      from t tprev
      where tprev.siteId = t.siteId and tprev.readtime < t.readtime
      order by tprev.readtime desc
     ) tprev;

Find the difference between previous row and current row

If your row are ordered by PRESENTERID, instead of doing P2.PNRFNAME > P1.PNRFNAME you can use PRESENTERID.

Try;

SELECT P2.PNRFNAME ,P1.PNRFNAME AS "pnrfname" , P2.SALARY_YEARLY - P1.SALARY_YEARLY AS "salary difference" 
FROM PRESENTERS P2
INNER JOIN PRESENTERS P1
ON P2.PRESENTERID> P1.PRESENTERID
ORDER BY P2.PRESENTERID, P1.PRESENTERID

SQL Query for difference between current and previous row by GROUP

Please try this (please replace temp table with your table):

If you are using SQL server, then you can use Common Table Expressions (CTE) as blow:

 IF (OBJECT_ID('tempdb..#temp_table') IS NOT NULL)
    BEGIN
      DROP TABLE #temp_table
    END;

CREATE TABLE #temp_table (SiteId INT NOT NULL, [year] INT, [class] [Char] NOT NULL)

INSERT INTO #temp_table (SiteId, [year], [class])
       values 
(1, 2007,   'A'),
(1, 2007,   'B'),
(1, 2008,   'A'),
(1, 2008,   'B'),
(1, 2008,   'C')

;with temp_cte as
  (SELECT  siteid, [year], [year]-1 as [yearbefore]
            ,STUFF((SELECT '|' + CAST([class] AS VARCHAR(MAX)) [text()]
            FROM #temp_table 
            WHERE SiteId = t.SiteId and [year] = t.[year]
            FOR XML PATH(''), TYPE)
            .value('.','NVARCHAR(MAX)'),1,2,' ') [class_list]
            FROM #temp_table t
            GROUP BY SiteId, [year]
)
select c.SiteId
       , c.[year]
       , case when t.yearbefore = null then null else right(REPLACE(c.class_list, t.class_list, ''), LEN(REPLACE(c.class_list, t.class_list, ''))-1) end as [class_added]
       , case when t.yearbefore = null then null else stuff(REPLACE(c.class_list, t.class_list, ''), 1, charindex('|', REPLACE(c.class_list, t.class_list, '')), '') end as [class_added_using_stuff]
 from temp_cte c
left join temp_cte t on c.[yearbefore] = t.[year]

If you are not using SQL Server (the below works in SQL server as well), then you can use joins (probably need to adjust the queries respective to RDBMS that is in use) as below:

select distinct t1.SiteId, t1.[year]
    , case when t1.[year] = m.[year] then null else t1.[class] end as class_added
    from #temp_table t1
    left join #temp_table t2 on t1.SiteId = t2.SiteId and t1.class = t2.class and t1.[year] -1 = t2.[year]
    left join (select top 1 * from #temp_table order by [year] ) m on t1.[year] = m.[year]
where t2.SiteId is null

UPDATE for MYSQL:

CREATE TABLE test_table(
    SiteId INT NOT NULL,
    year INT,
    class CHAR(1)
);

INSERT INTO 
    test_table(SiteId, year, class)
VALUES
    (1, 2007,   'A'),
    (1, 2007,   'B'),
    (1, 2008,   'A'),
    (1, 2008,   'B'),
    (1, 2008,   'C');
select distinct t1.SiteId, t1.year
    , case when t1.year = m.year then null else t1.class end as class_added
    from test_table t1
    left join test_table t2 on t1.SiteId = t2.SiteId and t1.class = t2.class and t1.year -1 = t2.year
    left join (select * from test_table order by year limit 1) m on t1.year = m.year
where t2.SiteId is null;

MY SQL Fiddle here (http://sqlfiddle.com/#!9/c570d57/1/0)

SQL: Difference Between Current Row of StartTime Column And Previous Row of EndTime Column

CREATE TABLE #Batches
(
    BatchID     INT,
    StartTime   Datetime,
    EndTime     Datetime,
)
INSERT INTO #Batches
VALUES
(100004,'2016-05-16 08:00:00','2016-05-16 08:03:30'),
(100005,'2016-05-16 08:05:00','2016-05-16 08:07:00'),
(100006,'2016-05-16 08:08:40','2016-05-16 08:15:00'),
(32141 ,'2016-05-16 08:18:00','2016-05-16 08:22:45'),
(84230 ,'2016-05-16 08:25:10','2016-05-16 08:33:42'),
(23444 ,'2016-05-16 08:40:00','2016-05-16 08:43:00'),
(100001,'2016-05-16 08:50:00','2016-05-16 08:52:00')

;WITH CTE AS
(
    SELECT
    BatchID,
    StartTime,
    EndTime,
    ROW_NUMBER() OVER (ORDER BY StartTime) AS Seq
    FROM #Batches
)
SELECT
    b.BatchID,
    b.StartTime,
    b.EndTime,
    CONVERT(VARCHAR(20), DATEADD(SECOND,DATEDIFF(SECOND, bl.EndTime,b.StartTime),0),108) AS Diff,
    DATEADD(SECOND,DATEDIFF(SECOND, bl.EndTime,b.StartTime),0) AS DiffDT
FROM CTE AS b
LEFT OUTER JOIN CTE AS bl ON bl.Seq = b.Seq - 1 -- Last batch   
ORDER BY b.StartTime

---

Related Topics