display month as a calendar using sql

---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 --空值不顯示

 

---sql server 2000 
DECLARE @Month AS INT  --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT  --Set the YEAR for which you want to generate the Calendar.

DECLARE @StartDate AS DATETIME 
DECLARE @EndDate AS DATETIME 
set  @Month = 5
set  @Year = 2013
set @StartDate= CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
set @EndDate= DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate));

SELECT
SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 1
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Sunday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 2 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Monday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 3 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Tuesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 4 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Wednesday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 5 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Thursday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 6 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Friday
,SUM(CASE WHEN DATEPART(DW, DATEADD(DD,number,@StartDate)) = 7 
THEN DATEPART(DAY, DATEADD(DD,NUMBER,@StartDate)) END) AS Saturday
FROM master.dbo.spt_values v
WHERE DATEADD(DD,number,@StartDate) BETWEEN @StartDate 
AND DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
AND v.type = 'P'
GROUP BY DATEPART(WEEK, DATEADD(DD,number,@StartDate))