displaymonthasacalendarusingsql

---sql server 2005 顯示一個月的數據，如果沒有空的也要顯示出來declare @T table([geovinddate] Datetime,[workcontent] nvarchar(8),[worker] nvarchar(50))Insert @Tselect '2012-8-11',N'遲到','geovindu' union allselect '2012-8-12',N'早退','geovin

---sql server 2005 顯示一個月的數據，美国空间，如果沒有空的也要顯示出來 declare @T table([geovinddate] Datetime,[workcontent] nvarchar(8),[worker] nvarchar(50)) Insert @T select '2012-8-11',N'遲到','geovindu' union all select '2012-8-12',N'早退','geovin' ;with Date as (select cast('2012-08-01' as datetime) Date union all select Date+1 from Date where Date+1<'2012-9-1') select cast(a.Date as varchar(50))+' '+ DATENAME(weekday, a.Date), [workcontent]=isnull([workcontent],''),[worker]=isnull([worker],'') from Date a left join @T b on a.Date=b.[geovinddate] ---sql server 2005:display current month as a calendar using sql ;with monthDates as ( select DATEADD(month, datediff(month, 0, getdate()),0) as d ,DATEPART(week, DATEADD(month, datediff(month, 0, getdate()),0)) as w union all select DATEADD(day, 1, d) ,DATEPART(week, DATEADD(day, 1, d)) from monthDates where d < DATEADD(month, datediff(month, 0, getdate())+1,-1) ) select max(case when datepart(dw, d) = 1 then datepart(d,d) else null end) as [Sun] ,max(case when datepart(dw, d) = 2 then datepart(d,d) else null end) as [Mon] ,max(case when datepart(dw, d) = 3 then datepart(d,d) else null end) as [Tue] ,max(case when datepart(dw, d) = 4 then datepart(d,d) else null end) as [Wed] ,max(case when datepart(dw, d) = 5 then datepart(d,d) else null end) as [Thu] ,max(case when datepart(dw, d) = 6 then datepart(d,d) else null end) as [Fri] ,max(case when datepart(dw, d) = 7 then datepart(d,d) else null end) as [Sat] from monthDates group by w --- DECLARE @Year int, @Month int, @LastDay int; SET @Year = 2013; SET @Month = 5; SET @LastDay = DAY(DATEADD(m, 1, CAST(@Year AS varchar) + '-' + CAST(@Month AS varchar) + '-01') - 1); WITH dates AS ( SELECT *, DOW = DATEPART(WEEKDAY, Date), WN = DATEPART(WEEK, Date) FROM ( SELECT Date = CAST(CAST(@Year AS varchar) + '-' + CAST(@Month AS varchar) + '-' + CAST(number AS varchar) AS datetime) FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND @LastDay ) s ) SELECT Sun = MAX(CASE days.DOW WHEN 1 THEN dates.Date END), Mon = MAX(CASE days.DOW WHEN 2 THEN dates.Date END), Tue = MAX(CASE days.DOW WHEN 3 THEN dates.Date END), Wed = MAX(CASE days.DOW WHEN 4 THEN dates.Date END), Thu = MAX(CASE days.DOW WHEN 5 THEN dates.Date END), Fri = MAX(CASE days.DOW WHEN 6 THEN dates.Date END), Sat = MAX(CASE days.DOW WHEN 7 THEN dates.Date END) FROM (SELECT DISTINCT DOW FROM dates) days CROSS JOIN (SELECT DISTINCT WN FROM dates) weeks LEFT JOIN dates ON weeks.WN = dates.WN AND days.DOW = dates.DOW GROUP BY weeks.WN ---sql server 2000 顯示一個月的數據，如果沒有空的也要顯示出來 declare @d table(geovinddate datetime) declare @date datetime set @date='2012-08-01' while @date<'2012-09-1' begin insert @d select @date set @date=dateadd(dd,1,@date) end declare @t table(geovinddate datetime,workcontent varchar(20)) insert @t select '2012-8-11','遲到' insert @t select '2012-8-12','早退' select cast(d.geovinddate as varchar(50))+' '+ DATENAME(weekday, d.geovinddate) as '發生日期', isnull(t.workcontent,'') as '備注' from @d d left join @t t on d.geovinddate=t.geovinddate --空值不顯示 ，香港服务器租用，服务器空间