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

playyuer 2004-03-25 10:13:45
今天下午看了回了一个"已经结帐"的帖子:

[难题]如何统计出一个月中没有出现的天的次数?
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]

欢迎批评指教!


...全文
51 51 打赏 收藏 举报
写回复
51 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lovvver 2004-04-04
先收藏,再接分!好吗?
  • 打赏
  • 举报
回复
welyngj 2004-04-04
UP
  • 打赏
  • 举报
回复
j9988 2004-04-04
先COPY下来!
  • 打赏
  • 举报
回复
playyuer 2004-04-04
修订版:
http://www.csdn.net/Develop/Read_Article.asp?Id=26083
  • 打赏
  • 举报
回复
zheninchangjiang 2004-04-04
up
  • 打赏
  • 举报
回复
playyuer 2004-04-04
up
  • 打赏
  • 举报
回复
zhangzs8896 2004-04-04
study
  • 打赏
  • 举报
回复
bhdxia 2004-04-04
这么长啊.
  • 打赏
  • 举报
回复
vileboy 2004-04-04
学习+收藏
  • 打赏
  • 举报
回复
shuichangliu 2004-04-04
收藏起来 ,
多谢拉
  • 打赏
  • 举报
回复
huangder 2004-04-04
先把程序复制下来
  • 打赏
  • 举报
回复
「已注销」 2004-04-04
好强啊 先踩个脚印
  • 打赏
  • 举报
回复
31737951 2004-04-04
收起来先
  • 打赏
  • 举报
回复
j9988 2004-04-04
再次COPY
  • 打赏
  • 举报
回复
playyuer 2004-04-04
--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)
  • 打赏
  • 举报
回复
ygghost 2004-03-29
接分来了
  • 打赏
  • 举报
回复
DreamManor 2004-03-29
学习,顺便提问题:

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

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

http://expert.csdn.net/Expert/topic/2751/2751870.xml?temp=.7578546
  • 打赏
  • 举报
回复
pbsql 2004-03-29
顶+收藏
  • 打赏
  • 举报
回复
shuichangliu 2004-03-29
收藏
  • 打赏
  • 举报
回复
xiangdafang 2004-03-29
认真学习
  • 打赏
  • 举报
回复
加载更多回复
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
申请成为版主
帖子事件
创建了帖子
2004-03-25 10:13
社区公告
暂无公告