Get the date values from previous rows if not available in current

I need to get the logic/Query for the below situation in SQL Server:

Last Updated                              Time
2018-06-21 23:09:21.947                 21-06-2018
2018-06-21 23:09:21.947                 23:09
2018-06-22 00:14:02.877                 23:15
2018-06-22 00:29:13.660                 22-06-2018
2018-06-22 00:29:13.660                 23:15
2018-06-22 00:41:20.773                 23-06-2018
2018-06-22 00:41:20.773                 23:20
2018-06-22 02:58:44.160                 23:22
2018-06-22 02:59:40.097                 23:25
2018-06-22 03:00:07.770                 23:28

I've the data as above and I want to get as below:

Last Updated                            Time    Time/Date
2018-06-21 23:09:21.947            21-06-2018   21-06-2018 23:09
2018-06-22 00:14:02.877               23:15     21-06-2018 23:15
2018-06-22 00:29:13.660              22-06-2018 22-06-2018 23:15
2018-06-22 00:41:20.773              23-06-2018 23-06-2018 23:20
2018-06-22 02:58:44.160                 23:22   23-06-2018 23:22
2018-06-22 02:59:40.097                  23:25  23-06-2018 23:25
2018-06-22 03:00:07.770                 23:28   23-06-2018 23:28

AS above, need to get the Date/time column, if there is no date get the previous date based on last updated column. My first question, Cheers!!

2 answers

  • answered 2018-06-25 06:27 Renish

    This is entirely based on your data. So you may want to change it accordingly.

    declare @RawData as Table
    (
        LastUpdate DateTime,
        Time Varchar(20)
    )
    
    declare @Data as Table
    (
        RowId int,
        LastUpdate DateTime,
        Time Varchar(20)
    )
    
    declare @FinalData as Table
    (
        LastUpdate DateTime,
        Time Varchar(20),
        [DateTime] DateTime 
    )
    
    Insert into @RawData values ('2018-06-21 23:09:21.947','21-06-2018')
    Insert into @RawData values ('2018-06-21 23:09:21.947','23:09')
    Insert into @RawData values ('2018-06-22 00:14:02.877','23:15')
    Insert into @RawData values ('2018-06-22 00:29:13.660','22-06-2018')
    Insert into @RawData values ('2018-06-22 00:29:13.660','23:15')
    Insert into @RawData values ('2018-06-22 00:41:20.773','23-06-2018')
    Insert into @RawData values ('2018-06-22 00:41:20.773','23:20')
    Insert into @RawData values ('2018-06-22 02:58:44.160','23:22')
    Insert into @RawData values ('2018-06-22 02:59:40.097','23:25')
    Insert into @RawData values ('2018-06-22 03:00:07.770','23:28')
    
    insert into @Data
    Select ROW_NUMBER() OVER(ORDER BY LastUpdate ASC), * from @RawData 
    
    Declare @Count INT
    SET @Count = (Select count(*) from @Data)
    
    DECLARE @intFlag INT
    SET @intFlag = 1
    WHILE (@intFlag <=@Count)
    BEGIN
        Declare @Date varchar(10)
        Declare @FinalDate varchar(10)
    
        if((Select Len(trim(time)) from @data where RowId = @intFlag) =10)
        Begin
            SET @Date = (Select Convert(date, time, 104) from @data where RowId = @intFlag)
        end
        else
        Begin 
            if((select Len(trim(time)) from @Data where RowID = @intFlag -1 ) = 10)
            Begin
                insert into @FinalData
                Select LastUpdate, time, Convert(Datetime,@Date + ' ' + time) from @data where RowID = @intFlag
            End
            else
            Begin
                insert into @FinalData
                Select LastUpdate, time, Convert(Datetime,@Date + ' ' + time) from @data where RowID = @intFlag
            End
    
        End 
        SET @intFlag = @intFlag + 1
    END
    
    Select * from @FinalData
    
    GO
    

  • answered 2018-06-25 11:31 Gordon Linoff

    You can do this using set-based operations. Here is one method:

    select t1.last_updated, t1.time,
           (t2.time + ' ' + t1.time) as time_date     
    from t outer apply
         (select top (1) t2.*
          from t2
          where t2.time like '%-%-%'
                t2.last_updated <= t.last_updated
          order by t2.last_updated desc
         ) t2
    where time like '%:%'
    group by last_updated;