CODEWITHSUNDEEP
sqlsql-serverjoinselect
David Waldron
David Waldron

Reputation: 162

TSQL get the previous and any proceeding records based on recordset provided

I have the following query:

SELECT 
    fs.FILOID currentfilo,
    ISNULL(LEAD(af.FILOID) OVER (ORDER BY StartTransactionTimeUTC), fs.Next_FILOID) Nextfilo,
    ISNULL(LAG(af.FILOID) OVER (ORDER BY StartTransactionTimeUTC), fs.Previous_FILOID) lf 
FROM 
    [DataWarehouseCore].[DWC_FILOSummary] fs 
JOIN 
    [AttendanceCore].[AC_FILO] af ON af.FILOID = fs.FILOID
WHERE 
    fs.Employee_ID = 15049
    AND Client_ID = 306

Which returns the following results:

currentfilo Nextfilo    lf
----------------------------
5           2           NULL
2           3           5
3           6           2
6           7           3
7           1           6
1           NULL        7

I only want to have to see currentfilo 3, 6, 7 , 1 if ID 6 was passed in. So instead of returning what I have in my result set just the previous and next records which are actually based on a date field.

Here is some examples of data and what I expect to see this is the parent table: AC_FILO

FILOID  ClientID    EmployeeID  StartTransactionTimeUTC
-----------------------------------------------------------
5       306         15049       2021-08-29 02:53:00.0000000
2       306         15049       2021-09-01 06:46:00.0000000
3       306         15049       2021-09-02 07:50:00.0000000
6       306         15049       2021-09-06 08:56:00.0000000
7       306         15049       2021-09-10 07:58:00.0000000
1       306         15049       2021-09-15 07:45:00.0000000

What I want in my new table is an ordered list of the current FILOID, the next FILOID and previous FILOID is based on the StartTransactionTimeUTC

For example when FILOID 6 was inserted I would want to see the previous FILOID to FILOID 6 followed by all proceeding FILOID's based on the date!

For example I would want to see

CurrentFilo NextFilo LastFilo
-----------------------------
3           6        2
6           7        3
7           1        6
1           NULL     7

Upvotes: 0

Views: 74

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

It's not totally clear what you're after, but given the final desired results and input value of 6, I take it to mean you want the (CurrentFilo, NextFilo,LastFilo) sets ordered by StartTransactionTimeUTC starting with the point where a given NextFilo occurs, since the 6 input matches the 6 in that place in the expected result.

As a first step, start with not caring about anything passed in and only create the next/last result for the entire table. We can use the LAG() and LEAD() window functions to do this.

You've done most of this work already in the question's first code listing, but let's also include the StartTransactionTimeUTC field for now, so we will be able to reproduce the correct order in later steps. I'm also using a simplified schema right now so I can reproduce this on SQL Fiddle:

SELECT  FILOID as CurrentFILO,
    LEAD(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as NextFILO,
    LAG(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as LastFILO,
    StartTransactionTimeUTC
FROM AC_FILO

Now for a given NextFILO value like 6 we can find the desired StartTransactionTimeUTC time of that record by using a Common Table Expression (CTE), like this:

WITH FILOTree AS (
    SELECT  FILOID as CurrentFILO,
        LEAD(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as NextFILO,
        LAG(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as LastFILO,
        StartTransactionTimeUTC
    FROM AC_FILO
)
SELECT StartTransactionTimeUTC
FROM FILOTree
WHERE NextFILO = 6

We could also use a nested inner SELECT instead of the CTE. The important thing is we need a layer of indirection around the initial query in order to use a WHERE clause with a window function like LAG() or LEAD(). But I prefer the CTE in this case for reasons that will become clear later.

Once we know the correct StartTransactionTimeUTC we can get the final results using the same technique:

WITH FILOTree AS (
    SELECT  FILOID as CurrentFILO,
        LEAD(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as NextFILO,
        LAG(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as LastFILO,
        StartTransactionTimeUTC
    FROM AC_FILO
)
SELECT CurrentFILO, NextFILO, LastFILO
FROM FILOTree
WHERE StartTransactionTimeUTC >= '2021-09-02 07:50:00'
ORDER BY StartTransactionTimeUTC

This produces the correct results, but from the wrong input. However, now we can use the same CTE (aha!) to put the two pieces together into the same query without repeating code for the initial current/next/last projection, like this:

WITH FILOTree As (
   SELECT  FILOID as CurrentFILO,
       LEAD(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as NextFILO,
       LAG(FILOID) OVER (ORDER BY StartTransactionTimeUTC) as LastFILO,
       StartTransactionTimeUTC
   FROM AC_FILO
)
SELECT CurrentFILO, NextFILO, LastFILO
FROM FILOTree 
WHERE StartTransactionTimeUTC >= (
   SELECT StartTransactionTimeUTC 
   FROM FILOTree
   WHERE NextFILO = 6
)
ORDER BY StartTransactionTimeUTC

See it work here:

http://sqlfiddle.com/#!18/24979/2

Finally, as I said before, these examples use a simplified schema from the actual production system. So let's plug the original query into the CTE:

WITH FILOTree As (
    SELECT fs.FILOID currentfilo 
        , ISNULL(LEAD(af.FILOID) over (order by StartTransactionTimeUTC), fs.Next_FILOID) Nextfilo
        , ISNULL(LAG(af.FILOID) over (order by StartTransactionTimeUTC), fs.Previous_FILOID) LastFILO
        , StartTransactionTimeUTC
    FROM [DataWarehouseCore].[DWC_FILOSummary] fs 
    JOIN [AttendanceCore].[AC_FILO] af ON af.FILOID = fs.FILOID
    WHERE fs.Employee_ID = 15049
        AND Client_ID = 306
)
SELECT CurrentFILO, NextFILO, LastFILO
FROM FILOTree 
WHERE StartTransactionTimeUTC >= (
    SELECT StartTransactionTimeUTC 
    FROM FILOTree
    WHERE NextFILO = 6
)
ORDER BY StartTransactionTimeUTC

The only things I had to change from that initial SQL command were adding StartTransactionTimeUTC to the SELECT list and the alias name for the LastFILO column.

Note this will run the CTE twice and it will not cache any results between the two runs, so be aware of that as a performance concern.

Upvotes: 2

Related Questions