Add row and last found value if row is missing

Transact-SQL
1

I would really, really appreciate if somebody could help me to solve this by posting a complete query, I am soo stuck...

I have a table "Mydb.MyTable" with fields like (Swedish date format):

MyDate      MyPartNo     MyValue
2017-10-01     A01         10
2017-10-02     A01         -3
2017-10-05     A01          6
2017-10-01     BX4         12
2017-10-06     BX4         -2

What I want to achieve is a table with all dates from the first to the last date. For each date that is "missing" for a part, I want to use the last known value. So I would like the result of the above table to look like this:

MyDate      MyPartNo     MyValue
2017-10-01     A01         10
2017-10-02     A01         -3
2017-10-03     A01         -3
2017-10-04     A01         -3
2017-10-05     A01          6
2017-10-01     BX4         12
2017-10-02     BX4         12
2017-10-03     BX4         12
2017-10-04     BX4         12
2017-10-05     BX4         12
2017-10-06     BX4         -2

The query doesn't need to find the first and last dates.

If it helps, I also have a table for dates "Mydb.CALENDAR".

2

Post create table script with inserts please.

3

I can’t, I’m creating a report from a database which I cannot manipulate.

4

No ideas, anyone?

5

Try this:

declare @mindate date;
declare @maxdate date;

select @mindate=min(mydate)
      ,@maxdate=max(mydate)
  from mydb.mytable
;

with cte(dt)
  as (select top(datediff(day,@mindate,@maxdate)+1)
             dateadd(day,row_number() over(order by (select null))-1,@mindate)
        from             (values(104),(116),(116),(112),( 58),( 47),( 47),(102),(111),(114),(117)) as tally1(n)
             cross apply (values(109),(115),( 46),(115),(113),(108),(116),(101),( 97),(109),( 46)) as tally2(n)
             cross apply (values( 99),(111),(109),( 47),(116),( 47),( 49),( 49),( 53),( 57),( 49)) as tally3(n)
     )
select a.dt as mydate
      ,b.mypartno
      ,b.myvalue
  from (select a.dt
              ,max(b.mydate) as mydate
          from cte as a
               left outer join mydb.mytable as b
                            on b.mydate<=a.dt
         group by a.dt
        ) as a
        inner join mydb.mytable as b
                on b.mydate=a.mydate
;