开发者社区> geovindu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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
+关注继续查看
---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)) 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SAP UI5的calendar 日历控件
Created by Wang, Jerry, last modified on Oct 09, 2015
51 0
Setting Up a Server Cluster for Enterprise Web Apps – Part 1
In this three-part tutorial, we will discover how to set up a server cluster using Alibaba Cloud ECS and WordPress.
11928 0
How to use kingshard building a MySQL cluster
How to use kingshard building a MySQL cluster   https://github.com/flike/kingshard/blob/master/doc/KingDoc/how_to_use_kingshard_EN.
926 0
ubuntu/wireshark: There are no interfaces on which a capture can be done.故障解决
[转载]http://blog.csdn.net/ccwwff/article/details/6697258 在ubuntu安装wireshark, 在启动程序启动wireshark.  点captrue->interface, 出现: There are no interfaces on which a capture can be done.
804 0
Tomcat 6 Missing Host Header Internal IP Address Disclosure
信息泄漏漏洞会向攻击者泄漏有关系统或 Web 应用程序的敏感信息。尝试在未经授权的情况下进行访问时,攻击者可以使用这些信息了解有关系统的更多信息。
898 0
+关注
geovindu
读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.
151
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载