How to group the recipe base with the datetime wise-CodePudding

enter image description hereWe have the below table data format .. we have some recipe need output current recipe before one recipe data which is all sorting datetime There is same recipe for B & D but i need recipe (B) set data only ..

    declare @tm_variab table (Timecreated datetime,Recipe_Name varchar(80))




    insert into @tm_variab select '2022-10-18 16:50:47' , 'TR1674FSHY'
    insert into @tm_variab select '2022-10-18 16:50:42' , 'TR1674FSHY'
    insert into @tm_variab select '2022-10-18 16:50:37' , 'TR1674FSHY'
    insert into @tm_variab select '2022-10-18 16:50:32' , 'TR1674FSHY'
    insert into @tm_variab select '2022-10-18 16:50:27' , 'TR1674FSHY'  ----- current recipe (A)
    insert into @tm_variab select '2022-10-18 16:50:22' , 'TR1674FSHY'
    insert into @tm_variab select '2022-10-18 16:50:17' , 'TR1674FSHY'
    insert into @tm_variab select '2022-10-18 16:50:12' , 'TR1674FSHY'

    insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'
    insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk' ---   recipe (B)
    insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'
    insert into @tm_variab select '2022-10-18 16:50:07' , 'TR1674FSkk'

    insert into @tm_variab select '2022-10-18 16:49:47' , 'TR19556ECDRE'
    insert into @tm_variab select '2022-10-18 16:49:42' , 'TR19556ECDRE'
    insert into @tm_variab select '2022-10-18 16:49:37' , 'TR19556ECDRE'
    insert into @tm_variab select '2022-10-18 16:49:32' , 'TR19556ECDRE' ----   recipe(c)
    insert into @tm_variab select '2022-10-18 16:49:27' , 'TR19556ECDRE'
    insert into @tm_variab select '2022-10-18 16:49:22' , 'TR19556ECDRE'
    insert into @tm_variab select '2022-10-18 16:49:17' , 'TR19556ECDRE'

    insert into @tm_variab select '2022-10-18 16:48:07' , 'TR1674FSkk'  ---   recipe (D)
    insert into @tm_variab select '2022-10-18 16:48:07' , 'TR1674FSkk'





    ;WITH CTE AS
    (
    SELECT 
     Timecreated
     ,Recipe_Name
     , ROW_NUMBER() OVER(PARTITION BY Recipe_Name ORDER BY Timecreated DESC) AS rn
     FROM @tm_variab
     )




     , newfinal as (
    SELECT TOP(3)
     Timecreated,
     Recipe_Name
     --, ROW_NUMBER() OVER(order BY Timecreated) AS rnii
     FROM CTE WHERE rn=1
     --ORDER BY Timecreated DESC
     )



     select Timecreated, Recipe_Name
     ,ROW_NUMBER() OVER(order BY Timecreated) AS rnii
     into #final 
     From newfinal 
     order by Timecreated desc


     select * from #final mm inner join @tm_variab kk on  kk.Recipe_Name=mm.Recipe_Name and kk.Recipe_Name ='TR1674FSkk'  
     drop table #final

There is same recipe for B & D but i need recipe (B) set data only ..

CodePudding user response：

I think you're looking for gaps in your data based on Timecreated.

Here's a query to detect these gaps:

SELECT tm.*
    FROM @tm_variab tm
    INNER JOIN (
        SELECT *,rn = ROW_NUMBER() OVER(ORDER BY Timecreated DESC) 
        FROM (
            SELECT *,LAG(Recipe_Name) OVER (order by Timecreated ) AS PRECEDENT
            FROM @tm_variab
        )a 
        WHERE Recipe_Name <> PRECEDENT
    )a ON a.Recipe_Name = tm.Recipe_Name AND a.Timecreated = tm.Timecreated AND rn = 2