SQLTeam.com | Weblogs | Forums

SQL select to Fill in missing dates

sql2012

#1

Hello,
I´m trying to insert those dates that are missing in #table1 for each codigo. For the dates I´m using the calendar function. This is what I have so far but it does not work... can you help pls?
many thanks,

create table #Table1
(
    fecha date,  codigo char(20), precio decimal(15,5) , descripcion char(40)
)

INSERT INTO #Table1  values ('01.01.2014', 'A', 2,'aa')
INSERT INTO #Table1  values ('01.05.2014', 'A', 3,'aa')
INSERT INTO #Table1  values ('01.06.2014', 'B', 1,'aa')
INSERT INTO #Table1  values ('01.08.2014', 'A', 2,'aa')

;WITH CTE_fechas_trading
 AS
 (   select CAST(date AS DATE)  as fecha_buena FROM [dbo].[GenerateCalendar] (getdate(),datediff(day,getdate(),'2011-12-01'))
where wKDNo in(2,3,4,5,6) 
 )

insert into #Table1
(fecha, codigo, precio, descripcion)

select  fecha, a.codigo,0,''
from  #Table1 a
left join CTE_fechas_trading b
    ON b.fecha_buena  = a.fecha  

where fecha is null

#2

I think you should join the other way around - i.e., left join to #Table1

select  fecha, a.codigo,0,''
from  CTE_fechas_trading b
left join #Table1 a
    ON b.fecha_buena  = a.fecha  
where a.fecha is null