CODEWITHSUNDEEP
sqlsql-serverlagpartition
Maelito Hartle
Maelito Hartle

Reputation: 11

How to use Dynamic Lag function to avoid joining a table to itself to retrieve date value

I'm currently writing code in SQL to add the column in red to the following table:

Example Table

The logic is the following:

For every row:

I've found a way to do this by joining the table to itself but I would like to avoid doing that as the size of the table is pretty massive.

What I have

select b.*, a.date, from table a left join table b on a.party=b.party where a.flag =1

Someone told me I could use the lag function, the partition over function and a case when to return the value I'm after but I haven't been able to figure it out.

Can someone help? Thank you so much!

Upvotes: 1

Views: 1517

Answers (4)

Sandip Chavan
Sandip Chavan

Reputation: 39

try this :->

select 
  b.*, 
  a.date, 
from 
  table a 
  left join table b on a.party = b.party 
where 
  a.flag = CASE WHEN a.flag = 1 THEN a.date WHEN a.flag = 0 THEN (
    SELECT 
      date 
    FROM 
      (
        SELECT 
          TOP 1 row_number() OVER (
            ORDER BY 
              a.date DESC
          ) rs, 
          a.date 
        FROM 
          a 
        WHERE 
          a.flag = 1 
        GROUP BY 
          a.date
      ) s
  ) END

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

Yes it can be done by joining table, if written properly.

@Sahi query is also good and simple.

Since you were asking for Dynamic LAG()

This query may or may not be very performant,but it certainly worth learning.

Test this with various sample data and tell me for which scenario it do not work. So that I correct my script accordingly.

 DECLARE @tab1 TABLE(PARTY CHAR(1),DATE DATE,Flag bit)
INSERT INTO @tab1
SELECT 'A','7-24-2018',1 Union ALL
SELECT 'A','7-28-2018',0 Union ALL
SELECT 'A','7-29-2018',0 Union ALL
SELECT 'A','7-29-2018',0 Union ALL
SELECT 'B','7-13-2018',1 Union ALL
SELECT 'B','7-17-2018',0 Union ALL
SELECT 'B','7-18-2018',0 Union ALL
SELECT 'C','7-8-2018',1 Union ALL
SELECT 'C','7-13-2018',0 Union ALL
SELECT 'C','7-19-2018',0 Union ALL
SELECT 'C','7-19-2018',0 Union ALL
SELECT 'C','7-20-2018',0; 

WITH cte 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    partition BY party 
                    ORDER BY flag DESC, [date] DESC ) rn 
         FROM   @tab1) 
SELECT *, 
       CASE 
         WHEN flag = 1 THEN [date] 
         ELSE Lag([date], (SELECT TOP 1 a.rn - a1.rn 
                           FROM   cte a1 
                           WHERE  a1.party = a.party)) 
                OVER ( 
                  ORDER BY party ) 
       END 
FROM   cte a

Upvotes: 0

Sahi
Sahi

Reputation: 1484

try this

    DECLARE @tab1 TABLE(PARTY CHAR(1),DATE DATE,Flag bit)
    INSERT INTO @tab1
    SELECT 'A','7-24-2018',1 Union ALL
    SELECT 'A','7-28-2018',0 Union ALL
    SELECT 'A','7-29-2018',0 Union ALL
    SELECT 'A','7-29-2018',0 Union ALL
    SELECT 'B','7-13-2018',1 Union ALL
    SELECT 'B','7-17-2018',0 Union ALL
    SELECT 'B','7-18-2018',0 Union ALL
    SELECT 'C','7-8-2018',1 Union ALL
    SELECT 'C','7-13-2018',0 Union ALL
    SELECT 'C','7-19-2018',0 Union ALL
    SELECT 'C','7-19-2018',0 Union ALL
    SELECT 'C','7-20-2018',0


    select t.*,
           max(case when flag = 1 then date end) over (partition by PARTY order by date) as [Last Flag On Date]
    from @tab1 t

enter image description here

Upvotes: 5

Squirrel
Squirrel

Reputation: 24793

use CROSS APPLY() to obtain the latest row with flag 1

SELECT *
FROM   yourtable t
       CROSS APPLY
       (
           SELECT TOP 1 x.Date as [Last flag on date]
           FROM   yourtable x
           WHERE  x.Party = t.Party
           AND    x.Flag = 1
           ORDER BY x.Date desc
       ) d

Upvotes: 0

Related Questions