CODEWITHSUNDEEP
sqlsql-servert-sql
Liam neesan
Liam neesan

Reputation: 2551

How to get the date in my case using SQL Server?

I don't know how ask this question.

I have two table Transaction and Missing

Transaction

id    Date
---------------
1     1-10-2016
2     2-10-2016
3     2-10-2016
6     5-10-2016
7     6-10-2016
8     7-10-2016
10    21-10-2016
16    23-10-2016
17    24-10-2016

Missing (This is missing table it is having which is not in Transaction Table

ID
-----------
4
5
9
11
12
13
14
15

Now I want to show Missing.ID and Date like below

id    Date
---------------
4     2-10-2016  //(this date is date of Transaction.id 3's date)
5     2-10-2016  //(this date is date of Transaction.id 3's date)

9     7-10-2016  //(this date is date of Transaction.id 8's date)

11    21-10-2016  //(this date is date of Transaction.id 10's date)
12    21-10-2016  //(this date is date of Transaction.id 10's date)
13    21-10-2016  //(this date is date of Transaction.id 10's date)
14    21-10-2016  //(this date is date of Transaction.id 10's date)
15    21-10-2016  //(this date is date of Transaction.id 10's date)

If i want to join both table on which basis i can join?. There is no link between this table

Upvotes: 2

Views: 50

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

you could combine your both table data (Transaction...Missing) using union all and use the combined result set to replace the null values with previous date column values based on id... And do the join operation as you want to display missing ids with date as follow

;with cte as
(
    select id, [Date] from Transaction
    union all
    select id, null [Date] from Missing 
)
select m.id, t.[Date] from Missing m
join 
(
      select id, case when c.[Date] is null then 
      (select top 1 [Date] from cte WHERE id < c.id and [Date] is not null order by id desc)
      else c.[Date] end [Date]   
      from cte C 
) t on m.id = t.id

Result :

id  date
4   2-10-2016
5   2-10-2016
9   7-10-2016
11  21-10-2016
12  21-10-2016
13  21-10-2016
14  21-10-2016
15  21-10-2016

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35563

I suggest using an apply operator for this:

select id, ca.date
from missing
cross apply (
   select top(1) Transaction.date from Transaction
   where Transaction.id < missing.id
   order by Transaction.date DESC
   ) ca (date)

Upvotes: 3

Related Questions