Get previous record where date is not null - SQL SERVER

Question

I have two tables as below :

table 1:

ID  B   C   D   E   Date

1   b   c   D   E   2018/10/10
1   c   d   A   B   2018/10/14

Table 2 :

ID  B   C   Date
1   b   c   2018/10/10
1   x   y   2018/10/11
1   y   x   2018/10/12
1   p   q   2018/10/13
1   c   d   2018/10/14

Table A has 6 columns, where as table 2 has 4 columns. Using left join the result is :

Select * from Table2 t2 left join table1 t1
on t2.id=t1.id and t2.Date = t1.Date

Left join result is :

ID  B   C   D   E   Date1           ID  B   C   Date2

1   b   c   D   E   2018/10/10      1   b   c   2018/10/10
-   -   -   -   -   -               1   x   y   2018/10/11
-   -   -   -   -   -               1   y   x   2018/10/12
    -   -   -   -   -               1   p   q   2018/10/13
1   c   d   A   B   2018/10/14      1   c   d   2018/10/14

Note :

'-' represnt NULL.
Dates are sorted in left join result -- order by table2.date , table1.date asc
I have taken result of join in cte. not able to generate final result.

Expected Result :

ID  B   C   D   E   Date

1   b   c   D   E   2018/10/10
1   x   y   D   E   2018/10/11
1   y   x   D   E   2018/10/12
1   p   q   D   E   2018/10/13
1   c   d   A   B   2018/10/14

Where Date from table1 is null in left join result, the search for previous non null date of table 1 , which would be just less than current date of table 2.

And fetch values of column D and E from there and keep the values of column B and C from current record where Date1 is null.

Being a newbie in sql, I am stuck in this. Please help.

Thanks in advance.

pi.314 · Accepted Answer · 2018-10-17 07:58:43Z

1

use outer apply with top 1. It will give your result, is fast and very short:

-- create data from example:
-- ------------------------
select * into #Table1 from
(select 1 as ID, 'b' as B, 'c' as C, 'D' as D, 'E' as E, cast('2018-10-10' as date) as [Date]
union all select 1,   'c',   'd',   'A',   'B',   '2018-10-14')t

select * into #Table2 from
(select 1 as ID,   'b' as B,   'c' as C ,   cast('2018-10-10' as date) as [Date]
union all select 1,   'x',   'y',   '2018-10-11'
union all select 1,   'y',   'x',  '2018-10-12'
union all select 1,   'p',   'q',   '2018-10-13'
union all select 1,   'c',   'd',   '2018-10-14')t

-- SOLUTION
-- -------- 
select
    T2.ID,
    T2.B,
    T2.C,
    T1.D,
    T1.E,
    T2.[Date]
from
    #Table2 T2
    outer apply
    (
        select top 1 * from #Table1 T1
        where T1.ID=T2.ID and T1.[Date] <= T2.[Date]
        order by T1.[Date] desc
    ) T1


-- clean everything
-- ----------------
drop table #Table1
drop table #Table2

answered Oct 17, 2018 at 7:58

pi.314

6205 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KMittal Over a year ago

Thank you, this is what I was looking for.

mohabbati · Accepted Answer · 2018-10-17 08:11:18Z

1

If you are using SQL Server 2012 or later, the following query returns the value you expected. I have used CTE and first_value() function and the query is optimized.

with
    cte
as
(
    select
        t2.ID   ,
        t2.B    ,
        t2.C    ,
        t1.D    ,
        t1.E    ,
        t2.[Date]   ,
        sum(case when t1.D is null then 0 else 1 end) over (order by t2.[Date]) as D_partition,
        sum(case when t1.E is null then 0 else 1 end) over (order by t2.[Date]) as E_partition
    from
        Table2 t2
    left join
        table1 t1
    on
        t2.id = t1.id
        and
            t2.[Date] = t1.[Date]
)
select
    cte.ID  ,
    cte.B   ,
    cte.C   ,
    first_value(D) over(partition by D_partition order by D desc)   as D    ,
    first_value(E) over(partition by E_partition order by E desc)   as E    ,
    cte.Date
from
    cte;

edited Oct 17, 2018 at 8:11

answered Oct 17, 2018 at 7:52

mohabbati

1,1662 gold badges13 silver badges33 bronze badges

3 Comments

KMittal Over a year ago

I think this solution wont work , if D and E can have null values as well in table 1 ,otherwise it good and thanks for sharing this .. it was a good learning.

mohabbati Over a year ago

@KMittal Could you explain exactly in what situation wont work?

KMittal Over a year ago

If we replace table1 'D' in first row with null and 'B' in second row with null. Then it gives 'E' for all the rows. though there should be null in one row.

George Menoutis · Accepted Answer · 2018-10-17 07:15:33Z

0

It seems you are left joining table2 to table 1, and you want the values of table1 if they exist, else from table2. This "if" in the level of data is typically implemented with the CASE function. However, in your case, we can use a more specific function, ISNULL(a,b), which returns a when a has a value and b when a is null:

select 
    t2.ID,
    isnull(t1.B,t2.B) as B,
    isnull(t1.C,t2.C) as C,
    isnull(t1.D,t2.D) as D,
    isnull(t1.E,t2.E) as E,
    isnull(t1.[Date],t2.[Date]) as [Date]
from Table2 t2 
left join table1 t1
on t2.id=t1.id and t2.Date = t1.Date

However, are you sure that t2.Date = t1.Date is needed? Ususally, the column named ID is unique/primary key, so that would make the additional join condition on date redundunt. You should remove it if this is the case.

answered Oct 17, 2018 at 7:15

George Menoutis

7,3104 gold badges23 silver badges51 bronze badges

3 Comments

KMittal Over a year ago

No , actually if date of table1 is null which means all its records are null, so i need last record of table1 i.e. previous values of table1 where date1 is lust less than date2. and even if table 2 B and C is null I want values of B and C from table 2 only.

George Menoutis Over a year ago

What you are saying here is different than your example. Eg, in case of table 2 line "1 p q 2018/10/13", in your expected result you have "1 p q D E 2018/10/13". If you wanted what you say here, we would have to take the date of 2018/10/13, find the previous one on table1 which is 2018/10/10, and then return "1 b c D E 2018/10/10" for a second time. IF this is what you want, please edit your question accordingly.

Fahmi Over a year ago

@KMittal, your logic is not clear, could you please explain it in a bit details

Farmer · Accepted Answer · 2018-10-17 09:07:41Z

0

Could you check to see if below is something you are looking for?

Select t2.ID,t2.B,t2.C,t1.D,t1.E, t2.Date from Table2 t2 left join table1 t1
on t2.id=t1.id and (t2.Date >= t1.Date)
where not exists (select 1 from table1 t12 where t2.Date > t1.Date and t2.Date >= t12.Date and t12.Date > t1.Date)

Here we tried to open table 1 two times (t1 and t12) to make sure the date in table 2 must >= a date in table1 and < an other date.

answered Oct 17, 2018 at 9:07

Farmer

192 bronze badges

Collectives™ on Stack Overflow

Get previous record where date is not null - SQL SERVER

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related