22,207
社区成员
发帖
与我相关
我的任务
分享
create table t(xh int,zao varchar(2),zhong varchar(2),wan varchar(2))
insert into t
select 1,'甲','乙','丙' union all
select 2,'乙','丙','甲' union all
select 3,'丙','甲','乙'
--计算结果(如果需要显示时间,可以调整具体的数据表结构)
select * From t where xh=(cast(datename(week,'2014-8-20') as int)- cast(datename(week,'2014-8-1') as int))/3
DECLARE @Year int
DECLARE @firstDay datetime
DECLARE @lastDay datetime
DECLARE @daysOfYear int
SET @Year = 2014 --年份
SET @firstDay = Convert(datetime, Convert(varchar(4),@Year)+'-01-01', 120)
SET @lastDay = Convert(datetime, Convert(varchar(4),@Year)+'-12-31', 120)
SET @daysOfYear = DateDiff(day, @firstDay, @lastDay) + 1
;
WITH dayList(date)
AS (
SELECT DateAdd(day, number, @firstDay)
FROM master.dbo.spt_values
WHERE type='p'
AND number < @daysOfYear
),
hourList(id,startHour,endHour)
AS (
SELECT 0,7,15 UNION ALL
SELECT 1,15,23 UNION ALL
SELECT 2,23,31
),
workShifts(startTime, endTime, week, teamId)
AS (
SELECT DateAdd(hour, h.startHour, d.date),
DateAdd(hour, h.endHour, d.date),
DatePart(week, date),
(h.id+DatePart(week, date)-1) % 3
FROM dayList d
, hourList h
),
teamList(id,name)
AS ( -- 这里根据上年最后一班调整甲乙丙次序
SELECT 0,'甲' UNION ALL
SELECT 1,'乙' UNION ALL
SELECT 2,'丙'
)
SELECT w.*,
t.name
FROM workShifts w
JOIN teamList t
ON t.id = w.teamID
ORDER BY w.startTime
startTime endTime week teamId name
----------------------- ----------------------- ----------- ----------- ----
2014-01-01 07:00:00.000 2014-01-01 15:00:00.000 1 0 甲
2014-01-01 15:00:00.000 2014-01-01 23:00:00.000 1 1 乙
2014-01-01 23:00:00.000 2014-01-02 07:00:00.000 1 2 丙
2014-01-02 07:00:00.000 2014-01-02 15:00:00.000 1 0 甲
2014-01-02 15:00:00.000 2014-01-02 23:00:00.000 1 1 乙
2014-01-02 23:00:00.000 2014-01-03 07:00:00.000 1 2 丙
2014-01-03 07:00:00.000 2014-01-03 15:00:00.000 1 0 甲
2014-01-03 15:00:00.000 2014-01-03 23:00:00.000 1 1 乙
2014-01-03 23:00:00.000 2014-01-04 07:00:00.000 1 2 丙
2014-01-04 07:00:00.000 2014-01-04 15:00:00.000 1 0 甲
2014-01-04 15:00:00.000 2014-01-04 23:00:00.000 1 1 乙
2014-01-04 23:00:00.000 2014-01-05 07:00:00.000 1 2 丙
2014-01-05 07:00:00.000 2014-01-05 15:00:00.000 2 1 乙
2014-01-05 15:00:00.000 2014-01-05 23:00:00.000 2 2 丙
2014-01-05 23:00:00.000 2014-01-06 07:00:00.000 2 0 甲
2014-01-06 07:00:00.000 2014-01-06 15:00:00.000 2 1 乙
2014-01-06 15:00:00.000 2014-01-06 23:00:00.000 2 2 丙
2014-01-06 23:00:00.000 2014-01-07 07:00:00.000 2 0 甲
...