display month as a calendar using sql

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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