CODEWITHSUNDEEP
sql-servert-sql
jz3
jz3

Reputation: 521

transforming/manipulating result of a tsql join

I have a dataset like the following:

dataset1:
 **columns:** id  type  loc  x_qty  y_qty  z_qty  j_1   j_2   date
 **row: **   1   fe    32   292    394    112    NULL  NULL  2012-09-21
 **row: **   2   fd    30   298    394    112    NULL  NULL  2012-09-22
 **row: **   3   fc    31   343    394    112    NULL  NULL  2012-09-23

and I'm left joining dataset2 onto dataset1 on type, loc, and date to get j_1 and j_2

dataset2:
**columns:** id  type  loc  x_qty  y_qty  z_qty  j_1   j_2   date
**row: **    1   fe    32   NULL   NULL   NULL   239   349   2012-09-21
**row: **    2   fe    31   NULL   NULL   NULL   209   319   2012-09-23

My problem is that dataset1 has a row for every single day, while dataset2 only has rows for days that j_1 and j_2 have values. So for the ending dataset, there will be a NULL in j_1 and j_2 for every date that does not exist in dataset2.

My question: Is there a way, to put the value for the previous date in the column if there is no date. For example, using the datasets above. 9-22 does not exist in dataset2, so there will be a the 9-22 row in the final result, with NULLs in j_1 and j_2, is there a way to use the previous date's values of j_1 and j_2 and put them in that row?

Thanks in advance for any help!

SQL SERVER 2008

Upvotes: 0

Views: 68

Answers (1)

d89761
d89761

Reputation: 1434

Can you use something like this to join with the current or previous date?

SELECT * FROM dataset1 t1 INNER join dataset2 t2 ON t2.date = (select MAX(t3.date) from dataset2 t3 where t3.date <= t1.date)

Upvotes: 2

Related Questions