CODEWITHSUNDEEP
sqlsql-serverrecord
SimonFreeman
SimonFreeman

Reputation: 205

SQL previous value

I've created a SQL statement:

SELECT
    ROW_NUMBER() OVER (ORDER BY Q2.FUNCTIONAL_LOCATION) AS Rowy,
    Q1.FACT_MEASUREMENT_KEY,
    CONVERT(VARCHAR,Q1.Doc_Time,102) AS TIME
FROM 
    dbo.DIM_PROJECT_TECH_OBJ Q2 
INNER JOIN 
    dbo.FACT_MEASUREMENT Q1 ON (Q1.PROJECT_TECH_OBJ_KEY = Q2.PROJECT_TECH_OBJ_KEY)
WHERE 
    Q1.Measurement_Position  = 'XXX'

Getting this result:

1   16124   08:00:00
2   53969   12:30:00
3   54282   17:15:00
4   55231   18:00:00
5   56196   15:00:00
6   16123   08:00:00
7   55393   12:30:00
8   55423   09:30:00
9   54283   08:00:00

My goal is to obtain the "Record-1" TIME in each row (expecting an error for the first one), like this:

1   16124   8:00:00 
2   53969   12:30:00    8:00:00
3   54282   17:15:00    12:30:00
4   55231   18:00:00    17:15:00
5   56196   15:00:00    18:00:00
6   16123   8:00:00 15:00:00
7   55393   12:30:00    8:00:00
8   55423   9:30:00 12:30:00
9   54283   8:00:00 9:30:00

I've already failed trying to use:

ROW_NUMBER() OVER (PARTITION BY Q1.FACT_MEASUREMENT_KEY ORDER BY Q1.FACT_MEASUREMENT_KEY ) AS RowP

But I got error code 102.

This version of SQL Server does not support LAG statements.

Thanks in advance for any suggestion/help.

Regs

Upvotes: 2

Views: 144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this with a self join on the results of your query:

WITH t as (
      SELECT ROW_NUMBER() OVER (ORDER BY Q2.FUNCTIONAL_LOCATION) AS Rowy,
             Q1.FACT_MEASUREMENT_KEY,
             CONVERT(VARCHAR(255), Q1.Doc_Time, 102) AS TIME
      FROM dbo.DIM_PROJECT_TECH_OBJ Q2 INNER JOIN
           dbo.FACT_MEASUREMENT Q1
           ON Q1.PROJECT_TECH_OBJ_KEY = Q2.PROJECT_TECH_OBJ_KEY
      WHERE Q1.Measurement_Position = 'XXX'
     )
select t.*, tprev.time
from t left join
     t tprev
     on tprev.rowy = t.rowy - 1;

When you upgrade to SQL Server 2012+, you can replace this with lag().

Also, when you use varchar() (and related types in SQL Server), always use a length. SQL Server has different default lengths in different contexts -- and the default might not be good enough in some cases.

Upvotes: 2

Related Questions