SQLTeam.com | Weblogs | Forums

How to fill missing dates?

Input:

Localcol id
2021-03-01 1
2021-03-02. 2
2021-03-03. 3
2021-03-07. 4
2021-03-09. 5

And I want the output as shown below:

Localcol id

2021-03-01 1
2021-03-02 2
2021-03-03. 3
2021-03-04. Null
2021-03-05 Null
2021-03-06. Null
2021-03-07 4
2021-03-08. Null
2021-03-09. 5

Pls help me in creating query

hi hope this helps .. this is not the complete solution

idea

.. please let me know if you want complete ..

drop create sample data
drop table SampleData 
go 

create table SampleData 
(
Localcol date , 
id  int
)
go 

insert into SampleData select '2021-03-01', 1
insert into SampleData select '2021-03-02', 2
insert into SampleData select '2021-03-03', 3
insert into SampleData select '2021-03-07', 4
insert into SampleData select '2021-03-09', 5

select * from SampleData
; with tally_cte as 
(
SELECT N=number+1 FROM master..spt_values WHERE type = 'P' and number+1 <= ( select max(DAY(Localcol))  from SampleData)
)
select 'SQL Output', * from tally_cte a left join SampleData b  on a.N = DAY(Localcol) 

1 Like

Can you please give me complete solution

ok please give me some time

1 Like

K sure..thankyou

Please rename the column names meaning fully

; with tally_cte as 
(
	SELECT N=number FROM master..spt_values WHERE type = 'P' 
) , xx_cte as 
( 
	select min(Localcol) as mind  , max(DAY(Localcol)) as maxd  from SampleData 
) , temp_cte as 
(
	select dateadd(dd,N,(select mind from xx_cte)) as OK from tally_cte where N <  ( select maxd from xx_cte) 
)
select 
   a.ok,b.id 
from 
  temp_cte a 
    left join  
  sampledata b 
      on a.OK = b.Localcol

image

1 Like

Thankyou ..I got the output.

But what is that master..spt_values??
I'm not getting that.

master..spt_values .. is system generated values
master .. spt_values . it contains numbers from 0 to 2047
for system's own purpose ..

we are using it create tally table .. tally table is a series of numbers
from begining anything to end anything
example
600 to 10000
23 to 103355667

1 Like

Ok got it sir... thankyou so much..