表 ID Startdate(起始時間) enddate(結束時間)
1 2009-10-01 2011-01-01
2 2009-01-01 2010-01-01
3 2008-01-01 2009-02-01
4 2009-03-01 2011-01-01當查詢條件為2010-02-02時候,
滿足條件的ID 有1、4.
現在需要的是按照月份從當月第一天一直到當月最後一天統計
結果如下:天 1 2 3 4 ......28
ID 1 1 1 1 ......1
ID 4 4 4 4 ......4
以上只是簡單的舉例, 下步還要與另外一個表連接,求該ID 2月份1-28號的數據。
該怎么實現?
1 2009-10-01 2011-01-01
2 2009-01-01 2010-01-01
3 2008-01-01 2009-02-01
4 2009-03-01 2011-01-01當查詢條件為2010-02-02時候,
滿足條件的ID 有1、4.
現在需要的是按照月份從當月第一天一直到當月最後一天統計
結果如下:天 1 2 3 4 ......28
ID 1 1 1 1 ......1
ID 4 4 4 4 ......4
以上只是簡單的舉例, 下步還要與另外一個表連接,求該ID 2月份1-28號的數據。
該怎么實現?
set @startdate='2009-09-28'
set @enddate='2009-10-5'select convert(varchar(10),dateadd(day,number,@startdate),120)
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>=0
and type='p'
/*----------
2009-09-28
2009-09-29
2009-09-30
2009-10-01
2009-10-02
2009-10-03
2009-10-04
2009-10-05(8 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Startdate] datetime,[enddate] datetime)
insert [tb]
select 1,'2009-10-01','2011-01-01' union all
select 2,'2009-01-01','2010-01-01' union all
select 3,'2008-01-01','2009-02-01' union all
select 4,'2009-03-01','2011-01-01'
declare @sql varchar(8000)select @sql = isnull(@sql+',
','')+'max(case when '''+date+''' between [Startdate] and [enddate] then [ID] else '''' end) as ['+ltrim(day(date))+']'
from
(select convert(varchar(10),dateadd(day,number,convert(varchar(8),getdate(),120)+'01'),120) as [date]
from master..spt_values where type = 'P'
and datediff(month,dateadd(day,number,convert(varchar(8),getdate(),120)+'01'),getdate())=0
) tselect @sql = 'select '+@sql+'
from tb where convert(varchar(10),getdate(),120) between [Startdate] and [enddate]
group by [ID]'exec(@sql)-------------------------------------------
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4