SQL Datediff - Find Datediff Between Rows

Date Difference between consecutive rows

SELECT  T1.ID, 
        T1.AccountNumber, 
        T1.Date, 
        MIN(T2.Date) AS Date2, 
        DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff
FROM    YourTable T1
        LEFT JOIN YourTable T2
            ON T1.AccountNumber = T2.Accountnumber
            AND T2.Date > T1.Date
GROUP BY T1.ID, T1.AccountNumber, T1.Date;

or

SELECT  ID,
        AccountNumber,
        Date,
        NextDate,
        DATEDIFF("D", Date, NextDate)
FROM    (   SELECT  ID, 
                    AccountNumber,
                    Date,
                    (   SELECT  MIN(Date) 
                        FROM    YourTable T2
                        WHERE   T2.Accountnumber = T1.AccountNumber
                        AND     T2.Date > T1.Date
                    ) AS NextDate
            FROM    YourTable T1
        ) AS T

Date Diff Between Rows in SQL

Demo on Lead and CTE using row_number

This solution is not dependent on the IDCODE as an identifier but as the
sequence, if IDCODE is irrelevant then Lead and Row_number order by mydatetime

declare @mytable table (idcode int, mydatetime datetime)

insert into @mytable
values
(1      ,'02/03/2011 08:00'),-----10
(2        ,'02/03/2011 08:10'),-----13
(3      ,'02/03/2011 08:23'), ----- 2
(4       ,'02/03/2011 08:25'), -----  60
(5       ,'02/03/2011 09:25'),  -----      44
(6       ,'02/03/2011 10:20'),    -----    14
(7       ,'02/03/2011 10:34')

-- using Lead
select
mydatetime,
lead(mydatetime,1,null) over (order by idcode) [mynextdatetime],
datediff(minute,mydatetime,lead(mydatetime,1,null) over (order by idcode)) [DatediffMinute]
from @mytable

-- using CTE

;with myDateList as (
  select row_number() over (order by idcode) rn,
  idcode,
  mydatetime
  from @mytable
 ) ,
myNextDate as
(
select  
  row_number() over (order by idcode) nextrn,
  idcode,
  mydatetime [nextdate]
  from
  myDateList
  where rn > 1
)
 select d.idcode,d.mydatetime, n.nextdate,
 datediff(minute,d.mydatetime,n.nextdate) datediffbyminutes
  from myDateList D
 left outer join myNextDate N
   on d.rn = n.nextrn 
 ;

SQL: Difference between consecutive rows

You can use lag() to get the date of the previous order by the same customer:

select o.*,
    datediff(
        order_date,
        lag(order_date) over(partition by member_id order by order_date, order_id)
    ) days_diff
from orders o

When there are two rows for the same date, the smallest order_id is considered first. Also note that I fixed your datediff() syntax: in Hive, the function just takes two dates, and no unit.

I just don't get the logic you want to compute num_orders.

I need to get datediff on each row based on previous row

Given this sample data:

CREATE TABLE dbo.TheTable(Rownr int, ID int, SalesDate date, result tinyint);

INSERT dbo.TheTable(Rownr, ID, SalesDate, result) VALUES
(1, 1, '20210105', NULL),
(2, 1, '20210205', NULL),
(3, 2, '20210203', NULL),
(4, 2, '20210304', NULL),
(5, 3, '20210504', NULL),
(6, 4, '20210703', NULL),
(7, 4, '20210905', NULL);

This query updates the result column with 1 if the customer hasn't been before or their last purchase was more than a month ago, and 0 if their previous purchase was last month. Note that if a new customer places two orders in the same month, only the first one will be marked as such.

;WITH cte AS 
(
  SELECT *, Prev =
    COALESCE(LAG(SalesDate, 1) 
    OVER (PARTITION BY ID ORDER BY SalesDate), SalesDate)
  FROM dbo.TheTable
  WHERE result IS NULL
)
UPDATE cte SET result = CASE
  WHEN DATEDIFF(MONTH, Prev, SalesDate) = 1
  THEN 0 ELSE 1 END;

• Example db<>fiddle

Calculate Datediff between rows in one column (with more than 2 records)

Change the correlated subquery so that it returns the greatest of the earlier Pstng_Date values for T1.Customer

This query returned what you want when tested with your sample data in Access 2007.

SELECT
    AllDays.Customer,
    AllDays.Pstng_Date,
    Nz(DateDiff("d",[PreviousDate],[Pstng_Date]), 0) AS Days_Between
FROM
    (
        SELECT
            Customer,
            Pstng_Date,
            (
                SELECT Max(Pstng_Date)
                FROM SAPData AS T2
                WHERE
                        T2.Customer = T1.Customer
                    AND T2.Pstng_Date < T1.Pstng_Date
            ) AS PreviousDate
        FROM SAPData AS T1
    ) AS AllDays;

If you will be running the query from outside an Access session, the Nz() function will not be available. In that case, you could use an IIf() expression instead.

IIf([PreviousDate] Is Null, 0, DateDiff("d",[PreviousDate],[Pstng_Date]))

Pull a DATEDIFF between Rows with Distinct value and WHERE Clause

You can use conditional aggregation:

select packageid,
       datediff(day,
                max(case when statusid = 1 then rowdatemodified end),
                max(case when statusid = 2 then rowdatemodified end)
               )
from t
group by packageid

Calculate Date difference between multiple rows SQL

I am not proud of this solution, but I think it works for you. My approach was to create a table of days and then look at which project the vehicle was assigned to each day. Finally, aggregate by month and year to get the results. I had to do this as a script since you can use aggregate functions in the definitions of recursive CTEs, but you may find a way to do this without needing a recursive CTE.

I created a table variable to import your data so I could write this. Note, I added an extra assignment to test assignments that spanned months.

DECLARE @Vehicles AS TABLE
(
    [VehicleID]      INT     NOT NULL,
    [Project]        CHAR(2) NOT NULL,
    [InspectionDate] DATE    NOT NULL
);

INSERT INTO @Vehicles
(
    [VehicleID],
    [Project],
    [InspectionDate]
)
VALUES
(1, 'P1', '2021-08-20'),
(1, 'P1', '2021-09-05'),
(1, 'P2', '2021-09-15'),
(1, 'P3', '2021-09-20'),
(1, 'P2', '2021-10-10'),
(1, 'P1', '2021-10-20'),
(1, 'P3', '2021-10-21'),
(1, 'P2', '2021-10-22'),
(1, 'P4', '2021-11-15'),
(1, 'P4', '2021-11-25'),
(1, 'P4', '2021-11-30'),
(1, 'P1', '2022-02-05');

DECLARE @StartDate AS DATE, @EndDate AS DATE;

SELECT @StartDate = MIN([InspectionDate]), @EndDate = MAX([InspectionDate])
FROM   @Vehicles;

;WITH [seq]([n])
AS (SELECT 0 AS [n]
    UNION ALL
    SELECT [n] + 1
    FROM   [seq]
    WHERE  [n] < DATEDIFF(DAY, @StartDate, @EndDate)),
      [days]
AS (SELECT DATEADD(DAY, [n], @StartDate) AS [d]
    FROM   [seq]),
      [inspections]
AS (SELECT [VehicleID],
           [Project],
           [InspectionDate],
           LEAD([InspectionDate], 1) OVER (PARTITION BY [VehicleID]
                                           ORDER BY [InspectionDate]
                                          ) AS [NextInspectionDate]
    FROM   @Vehicles),
      [assignmentsByDay]
AS (SELECT [d].[d], [i].[VehicleID], [i].[Project]
    FROM   [days] AS [d]
           INNER JOIN [inspections] AS [i]
               ON [d].[d] >= [i].[InspectionDate]
                  AND [d] < [i].[NextInspectionDate])

SELECT   [assignmentsByDay].[VehicleID],
         [assignmentsByDay].[Project],
         MONTH([assignmentsByDay].[d]) AS [month],
         YEAR([assignmentsByDay].[d]) AS [year],
         COUNT(*) AS [daysAssigned]
FROM     [assignmentsByDay]
GROUP BY [assignmentsByDay].[VehicleID],
         [assignmentsByDay].[Project],
         MONTH([assignmentsByDay].[d]),
         YEAR([assignmentsByDay].[d])
ORDER BY [year], [month], [assignmentsByDay].[VehicleID], [assignmentsByDay].[Project]
OPTION(MAXRECURSION 0);

And the output is:

CREATE VIEW Table1_vw 
AS
WITH cte AS (
    SELECT  *, 
            ROW_NUMBER() OVER (ORDER BY RecordTime) AS rn
    FROM    dbo.Table1
)
SELECT  mc.RecordTime,
        mc.Running,
        mc.Fault,
        DATEDIFF(second, mc.RecordTime, mp.RecordTime) Diff
FROM cte mc
LEFT JOIN cte mp
ON mc.rn = mp.rn - 1

CREATE VIEW Table1_vw 
AS
SELECT  RecordTime,
        Running,
        Fault,
        DATEDIFF(second,RecordTime,LEAD(RecordTime,1,NULL) OVER (ORDER BY RecordTime ASC) ) as Diff
FROM Table1
GO