Query for Getting Value from Another Record in Same Table and Filter by Difference Greater Than a Gap Threshold

Row Difference along a column in Access

With Allen Browne subquery basics as guide, consider:

SELECT SiteID, NumericResult, CollectDate, 
Abs(NumericResult-(SELECT TOP 1 Dupe.NumericResult FROM GIS_allCL2 As Dupe WHERE Dupe.SiteID=GIS_allCL2.SiteID AND Dupe.CollectDate<GIS_allCL2.CollectDate ORDER BY Dupe.CollectDate DESC)) AS Diff
FROM GIS_allCL2 ORDER BY SiteID, CollectDate;

Suggestion for when a SiteID has multiple same date records. Requires a unique record identifier, such as an autonumber field, here it is called ID:

SELECT SiteID, NumericResult, CollectDate, 
Abs(NumericResult-(SELECT TOP 1 Dupe.NumericResult FROM GIS_allCL2 As Dupe WHERE Dupe.SiteID=GIS_allCL2.SiteID AND Format(Dupe.CollectDate, "yyyymmdd") & Format(Dupe.ID,"0000")<Format(GIS_allCL2.CollectDate,"yyyymmdd") & Format(GIS_allCL2.ID,"0000") ORDER BY Dupe.CollectDate DESC, Dupe.ID DESC)) AS Diff
FROM GIS_allCL2 ORDER BY SiteID, CollectDate, ID;

Access SQL Statement Calculate the Difference From Previous Month

Assuming that you always want to see the data from exactly one month previous (whether or not such data exists), and that the data could be entered on any day in the month, you might consider the following:

select 
    t1.date,
    t1.cumulative_expending_amount, 
    t2.cumulative_expending_amount, 
    t1.cumulative_expending_amount-t2.cumulative_expending_amount
from
    YourTable t1 left join YourTable t2 on 
    t1.date >= dateserial(year(t2.date),month(t2.date)+1,1) and
    t1.date <= dateserial(year(t2.date),month(t2.date)+2,0)

Change YourTable, date, and cumulative_expending_amount to suit the name of your table and fields respectively.

---

Following your comments, it would appear that the following SQL satisfies your requirements:

select 
    t1.itemid,
    t1.date,
    t1.cumulative_expending_amount,
    (
        select top 1 t3.cumulative_expending_amount
        from YourTable t3
        where t3.itemid = t1.itemid and t3.date < t1.date
        order by t3.date desc
    ) as last_cumulative_expending_amount,
    t1.cumulative_expending_amount-last_cumulative_expending_amount as diff
from
    YourTable t1 inner join 
    (
        select t.itemid, max(t.date) as mdate
        from YourTable t
        group by t.itemid
    ) t2 on t1.itemid = t2.itemid and t1.date = t2.mdate
order by 
    t1.itemid,
    t1.date desc

gap between visits of more than 3 months- sql

You can use lag(). date/time functions differ significantly among databases, but the idea is:

select distinct id
from (select d.*, lag(visit) over (partition by id order by visit) as prev_visit
      from data d
     ) d
where prev_visit < visit - interval '3 month';

Here is a db<>fiddle. This uses SQL Server, so the where clause is:

where prev_visit < dateadd(month, -3, visit);

Get data, count and group by DATETIME where date gap greater than X

Is it going to be a very slow query but it is an option:

select    count(e.eresult) as error_count,
          CAST(e.added AS DATE) as error_date
from      st__errors as e
where (
       SELECT  TIME_TO_SEC(TIMEDIFF(e.added, ei.added)) 
       FROM    st__errors as ei WHERE ei.id = e.id - 1
      ) > 10
      and e.eresult = 20
group by date(e.added)
order by e.id desc

SQL Server : find recent consecutive records that are greater than 5

If I follow you correctly, you can do this with window functions like this:

select 
from (
    select t.*, 
        row_number() over(partition by formid order by logdate desc) rn,
        sum(case when value > 5 then 1 else 0 end) over(partition by formid order by logdate desc) grp
    from mytable t
) t
where rn = grp

The idea is to compare the number of values above 5 to a row number, counting from the most recent value. Rows where the two values are equal can be retained.

If value in Field1 is null, return value from Field2, otherwise Return String

Without seeing a sample of the content of your table or linked table, nor knowing the names of either of these tables, I would suggest the following example code:

select * 
from yourlinkedtable t inner join
(
    select iif(t.customid is null, t.itemid, 'CK' & t.customid) as id
    from yourtable t
) q 
on t.itemid = q.id

Here, yourtable represents the table containing the ItemID and CustomID fields, and yourlinkedtable represents the table with which your are looking to match the prefixed ItemID.

---

Related Topics