---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))
