Lag function conversion into MS Access SQL

Question

I am trying to convert the lag function to use in access-sql, which simply doesn't have the function. So I have a table A with columns Type (a,b or c),Decider (1 or 2), Dates and Values..I would like to create another column which simply shifts the values down one date, with the first value then being null.

I tried to use this thread below, but I guess I'm doing something wrong still:

Calculating difference in column value from one row to the next

My attempt was

select type, dates, values, 
   (select top 1 test.values 
    from test as m
    where m.dates < test.dates 
    group by test.type, test.values, test.dates
    order by test.values,test.dates desc
   ) as lag
from test
group by test.type, test.dates,test.values
order by test.dates

select top 1 test.values should that no be: select top 1 m.values , and also most other references to test.* (except for the one in the WHERE-clause) — Luuk
– Luuk, Commented Apr 4, 2024 at 17:50
Does this answer your question? Query for getting value from another record in same table and filter by difference greater than a gap threshold — June7
– June7, Commented Apr 4, 2024 at 17:52
the part "select top 1 test.values from test as m where m.dates < test.dates group by test.type, test.values, test.dates order by test.values,test.dates desc ) as lag" I tried to export, because the whole thing was taking too long, but that also took for ever...how can I speed that up ? — Kingsley Obeng
– Kingsley Obeng, Commented Apr 7, 2024 at 15:29

Parfait · Accepted Answer · 2024-04-08 00:52:25Z

1

To follow the linked solution, consider adding a WHERE condition for type column without any need of GROUP BY:

select [type], [dates], [values], 
   (select top 1 m.[values]
    from test as m
    where m.[dates] < test.[dates]
      and m.[type] = test.[type]
    order by m.[dates] desc
   ) as lag
from test
order by test.[type], test.[dates]

Alternatively, consider calculating a rank integer based on dates and store it in a new column. Then run a self join that uses new rank:

ALTER (to be run once or use Table Design)

ALTER TABLE test ADD COLUMN date_rank INTEGER

UPDATE (to be run with every data change)

UPDATE test 
SET date_rank = DCount("*", "test", "[type]='" & test.[type] & "' AND [dates] >= #" & test.[dates] & "#")

SELECT (may not be fool-proof if dates tie)

SELECT t1.[type], t1.[dates], t1.[values], t2.[values] AS lag
FROM test t1
LEFT JOIN test t2
  ON t1.[type] = t2.[type]
  AND t1.[date_rank] = (t2.[date_rank] - 1)

Demo

Input Data

Subquery Solution

Rank + Join Solution

edited Apr 8, 2024 at 0:52

answered Apr 5, 2024 at 3:37

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kingsley Obeng Over a year ago

That works, but it's incredibly slow..I guess I have a lot of data, but are there ways to improve the performance?

June7 Over a year ago

Unfortunately, correlated subqueries can be agonizingly slow. What is nature of your data? Are date values sequential without gaps? Did you review VBA solution in link provided in my other comment?

Parfait Over a year ago

Consider update for alternative solution.

Kingsley Obeng Over a year ago

My desired result is a column with the values shifted down by a date.. The date have gaps due to weekend and holidays

Parfait Over a year ago

Understood. How did the alternative solution not work for you? After testing on random data and changing to a LEFT JOIN, the alternative solution exactly matches subquery solution with weekend gaps are handled. See screenshot of my demo.

|

Collectives™ on Stack Overflow

Lag function conversion into MS Access SQL

1 Answer 1

Demo

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Demo

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related