create table A
(
name varchar(20),
needComeTime varchar(10),
)
insert A
select 'chen','8:22' union
select 'li','9:20' union
select 'zhang','12:00'
go
create procedure p
@year int,
@month int
as
declare @tb table(dt varchar(10))
declare @tmp datetime
set @tmp=convert(varchar,@year)+'-'+convert(varchar,@month)+'-1'
while datediff(month,@tmp,dateadd(day,1,@tmp))=0
begin
insert @tb select convert(varchar(10),@tmp,120)
set @tmp=dateadd(day,1,@tmp)
end
insert @tb select convert(varchar(10),@tmp,120)
select A.*,T.* from @tb T left join A on 1>0 order by A.Name,T.dt
go-- 测试
exec p 2005,1exec p 2005,2--删除测试环境
drop table A
drop procedure p
(
name varchar(20),
needComeTime varchar(10),
)
insert A
select 'chen','8:22' union
select 'li','9:20' union
select 'zhang','12:00'
go
create procedure p
@year int,
@month int
as
declare @tb table(dt varchar(10))
declare @tmp datetime
set @tmp=convert(varchar,@year)+'-'+convert(varchar,@month)+'-1'
while datediff(month,@tmp,dateadd(day,1,@tmp))=0
begin
insert @tb select convert(varchar(10),@tmp,120)
set @tmp=dateadd(day,1,@tmp)
end
insert @tb select convert(varchar(10),@tmp,120)
select A.*,T.* from @tb T left join A on 1>0 order by A.Name,T.dt
go-- 测试
exec p 2005,1exec p 2005,2--删除测试环境
drop table A
drop procedure p
@sdate datetime,
@edate datetime
set @date = '200501'
set @sdate = convert(datetime,@date+'01')
set @edate = dateadd(month,1,@sdate)
create table #t1(name varchar(10),n varchar(10),y varchar(10))
while @edate > @sdate
begin
insert into #t1
select t.name,t.n,convert(varchar(10),@sdate,120)
from t
set @sdate = dateadd(day,1,@sdate)
end
select * from #t1 order by name,y
drop table #t1