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 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)
Can you please give me complete solution
ok please give me some time
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
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
Ok got it sir... thankyou so much..