SIMPLE SENORIO_1

Inputs
--------
Column 1 Column 2
01-Jan-2015 20
02-Jan-2015 30
04-Jan-2015 40
05-Jan-2015 10
07-Jan-2015 10

Write a Query to Display All Input values (Column A & Column B) + All missing values without using any TEMP table.

Output Expected
-----------------------
Column 1 Column 2
01-Jan-2015 20
02-Jan-2015 30
03-Jan-2015 Null
04-Jan-2015 40
05-Jan-2015 10
06-Jan-2015 Null
07-Jan-2015 10

ANS:

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='sample_b')

begin

drop table sample_b

end

create table sample_b(column1 date,column2 int)

insert into sample_b values('01-Jan-2015',20),('02-Jan-2015',30),('04-Jan-2015',40),('05-Jan-2015',10),('07-Jan-2015',10)

declare @min_date date

 select @min_date=MIN(column1) from sample_b

--print @min_date

if object_id('tempdb..#tempe') is not null

begin

drop table #tempe

end

create table #tempe(dat date,id int)

declare @max_date date 

select @max_date=MAX(column1) from sample_b

declare @colummn int

--select @date

while (@min_date < = @max_date)

begin

   select @colummn=column2 from sample_b where column1= @min_date

insert into #tempe (dat,id)

select distinct  @min_date,@colummn 

 select @min_date=(select dateadd(dd,1,@min_date))

end

--select * from sample_b

select DISTINCT A.DAT,b.COLUMN2 from #tempe a

left join 

   sample_b b

   on b.column1=a.dat

   --where b.column2 is  null