LAG Function, to compare two rows

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  February 4, 2019 at 8:28 am

  #409483

  I need to have SQL look at the previous row and comare the previous row is a "Y" or an "N"
  in the eligibility column.  I need to know WHERE the current row is a "N", but the row immediately above is a "Y" for the same   concat(compressed_Prod_ID,'-',D.SUPPLIER_ID.
  The code below is pulling the correct row numbers.
  As always, thanks 

  select * into #TempSTP
from
(select distinct
ROW_NUMBER() OVER (PARTITION BY concat(compressed_Prod_ID,'-',D.SUPPLIER_ID) ORDER BY response_Receipt_date desc) AS [RowNum]
,[COMPRESSED_PROD_ID]
  , [PROD_ID]
     ,concat(compressed_Prod_ID,'-',D.SUPPLIER_ID) as PS
  , [ELIGIBILITY_FLG]
  , D.[SOLICITATION_TYPE]
  , cast(RESPONSE_RECEIPT_DATE as date) as [Response Date]
,max(D.RESPONSE_RECEIPT_DATE) as [Max Response]
  , [CTRY_ORIGIN]

• pietlinden

  SSC Guru

  Points: 63436

  More actions

  February 4, 2019 at 9:31 am

  #2021050

  jeffshelix - Monday, February 4, 2019 8:28 AM

  I need to have SQL look at the previous row and comare the previous row is a "Y" or an "N"
  in the eligibility column.  I need to know WHERE the current row is a "N", but the row immediately above is a "Y" for the same   concat(compressed_Prod_ID,'-',D.SUPPLIER_ID.
  The code below is pulling the correct row numbers.
  As always, thanks 

  select * into #TempSTP
from
(select distinct
ROW_NUMBER() OVER (PARTITION BY concat(compressed_Prod_ID,'-',D.SUPPLIER_ID) ORDER BY response_Receipt_date desc) AS [RowNum]
,[COMPRESSED_PROD_ID]
  , [PROD_ID]
     ,concat(compressed_Prod_ID,'-',D.SUPPLIER_ID) as PS
  , [ELIGIBILITY_FLG]
  , D.[SOLICITATION_TYPE]
  , cast(RESPONSE_RECEIPT_DATE as date) as [Response Date]
,max(D.RESPONSE_RECEIPT_DATE) as [Max Response]
  , [CTRY_ORIGIN]

  LAG() and LEAD() have the same portioning syntax a ROW_NUMBER() and will return NULL if there is no value within the partition for the previous/next record.  Also, there's no need to use CONCAT here. If you use PARTITION, you can group by as many columns as you like. If that doesn't clarify it enough for you to solve the problem you're having, please post some consumable data. (CREATE TABLE/INSERTs)

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 4, 2019 at 9:48 am

  #2021055

  You can, however, specify a default value for LEAD/LAG if the row reference would be "out of range". LEAD/LAG has 3 parameters, of which only the 1st is mandatory. The first is a Scalar Expression, which is to be evaluated using the "other" row. The next is the offset, which defaults to 1, which is the number of rows to look forward/backward. Then you have the Default Value, which according to the documentation, is described as "The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned." I find that a little inaccurate, as that infers that if the value of the Scalar Expression is NULL then the default value is returned. This isn't the case:

  WITH VTE AS(

    SELECT 1 AS ID, 1 AS N

    UNION ALL

    SELECT 2,1

    UNION ALL

    SELECT 3,NULL

    UNION ALL

    SELECT 4,2)

  SELECT ID,

     N,

     LEAD(N, 1, 0) OVER (ORDER BY ID) AS L

  FROM VTE;

  If you run the above, you'll note that for ID 2, the value of L is NULL, not 0. The Default Value is actually only returned if an "out of range" row is referred to. In the SQL above, that's at ID 4, as there is no rows with a higher value for ID.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk