如何生成一个简易年(月)历?

[playyuer]

今天下午看了回了一个"已经结帐"的帖子:

[难题]如何统计出一个月中没有出现的天的次数？
http://expert.csdn.net/Expert/topic/2884/2884968.xml?temp=.509762

/*
select * into dates
from
(
select getdate() as d
union all
select getdate() + 1
union all
select getdate() + 1
) T
*/

select dateadd(day,N.i,dateadd(day,1-day(d),d) )
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
union all
select 31
) N
,
(select min(d) as d
from dates
group by datediff(month,0,d)) M

where datediff(month,dateadd(day,N.i,dateadd(day,1-day(d),d)) ,d)=0
and not exists (select 1
from Dates
where datediff(day,dateadd(day,N.i,dateadd(day,1-day(m.d),m.d)) ,d)=0)

虽然答非所问,但觉得比较有用,以前做过类似"人事软件考勤"处理!

另外再贴一个生成一个简易年(月)历的SQL,(原来用于考勤记录生成):

declare @ datetime
set @ = getdate() --'2004-03-25'
select dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))) as [Date]
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@)))))))-1 as weekday
,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))))) as dayofyear
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
where datediff(month,dateadd(day,1-day(@),dateadd(month,1-month(@),@)),dateadd(day,d.i,dateadd(month,m.i,dateadd(day,1-day(@),dateadd(month,1-month(@),@))))) = m.i
order by [Date]

欢迎批评指教!

[xiangdafang]

认真学习

[shuichangliu]

收藏

[pbsql]

顶+收藏

[DreamManor]

学习，顺便提问题：

寻求计算“个人所得税”，“年假”，“员工保险”，“工资计算公式” ，“考勤计算公式”的最佳解决方案。（如何最简单的实现上述功能，但又需要具备很强的通用性和灵活性？）

以上工作非常烦琐，花去了我很多时间，搞得我焦头烂额。我知道有很多解决办法，但希望大家能提出一些最优的解决方案。

http://expert.csdn.net/Expert/topic/2751/2751870.xml?temp=.7578546

[ygghost]

接分来了

[playyuer]

--2004-04-04 修订
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)
--注意 datename 的值会因 SQL Server 语言版本或日期格式有所差异!
--本测试环境为: SQL Server 2000 简体中文版 + Windows 简体中文版


declare @ datetime
set @ = '1995-02-25 11:00:50' -- 1995-01-01 正好是个星期日


select @ as 日期
,dateadd(year,datediff(year,0,@),0) as 所在年的第一天
,dateadd(year,1+datediff(year,0,@),0)-1 as 所在年的最后一天
,dateadd(quarter,datediff(quarter,0,@),0) as 所在季的第一天
,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as 所在季的最后一天
,dateadd(month,datediff(month,0,@),0) as 所在月的第一天
,dateadd(month,1+datediff(month,0,@),0)-1 as 所在月的最后一天
,dateadd(week,datediff(week,0,@),0) as 所在周的第一天
,dateadd(week,1+datediff(week,0,@),0)-1 as 所在周的最后一天


select dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))) as [Date]
,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDayName]
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDay]
,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as WeekOfYear
,datediff
(
week
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))) = '星期日'
then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))
else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))
end

,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end
)
+ 1 as MyWeekOfYear


,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1 as WeekOfMonth
,datediff
(week
,case when datename(weekday,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))= '星期日'
then dateadd(day,-1,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))))
else dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
end
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
end

)
+1 as MyWeekOfMonth

,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as DayOfYear

into D

from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) M
,
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
union all
select 13
union all
select 14
union all
select 15
union all
select 16
union all
select 17
union all
select 18
union all
select 19
union all
select 20
union all
select 21
union all
select 22
union all
select 23
union all
select 24
union all
select 25
union all
select 26
union all
select 27
union all
select 28
union all
select 29
union all
select 30
) d
where datediff(month,dateadd(year,datediff(year,0,@),0),dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))) = m.i
order by [Date]


select month(min(date)) as 月份
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期日' and datediff(month,min(a.date),date) = 0) as 星期日
, (select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期一' and datediff(month,min(a.date),date) = 0) as 星期一
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期二' and datediff(month,min(a.date),date) = 0) as 星期二
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期三' and datediff(month,min(a.date),date) = 0) as 星期三
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期四' and datediff(month,min(a.date),date) = 0) as 星期四
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期五' and datediff(month,min(a.date),date) = 0) as 星期五
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期六' and datediff(month,min(a.date),date) = 0) as 星期六

from d a
group by datediff(month,0,date),datepart(week,date)

[j9988]

再次COPY

[31737951]

收起来先

[「已注销」]

好强啊 先踩个脚印

[huangder]

先把程序复制下来

[shuichangliu]

收藏起来 ，
多谢拉

[vileboy]

学习＋收藏

[bhdxia]

这么长啊.

[zhangzs8896]

study

[playyuer]

up

[zheninchangjiang]

up

[playyuer]

修订版:
http://www.csdn.net/Develop/Read_Article.asp?Id=26083

[j9988]

先COPY下来!

[welyngj]

UP

[lovvver]

先收藏，再接分！好吗？