if object_id('Booking_Equipment_Information','u') is not null
begin
drop table Booking_Equipment_Information
end
go
create table Booking_Equipment_Information (
Equipment_VGUID UNIQUEIDENTIFIER NOT NULL,
Startime DATETIME ,
EndTime DATETIME,
BookingStatus Varchar(30) NOT NULL,
BookingUser UNIQUEIDENTIFIER,
CreatedDate DATETIME,
CreatedUser Varchar(30),
ChangeDate DATETIME,
ChangeUser Varchar(30),
VGUID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
LGUID UNIQUEIDENTIFIER
)
INSERT INTO Booking_Equipment_Information
(
Equipment_VGUID,
Startime,
EndTime,
BookingStatus,
BookingUser,
VGUID,
LGUID
)
SELECT '668D98C0-9553-4349-B937-B6810DCD613E','2014-08-05 13:30:00.000','2014-08-05 16:00:00.000',0,'EF273F9D-D777-474D-BF5E-A117F6E1DC25','96228777-E56E-48DA-9464-1A3A8C8EA94D','C9321C79-D8DF-4E25-832F-92D3298514F7'
SELECT '668D98C0-9553-4349-B937-B6810DCD613E','2014-08-05 10:30:00.000','2014-08-05 12:30:00.000',0,'EF273F9D-D777-474D-BF5E-A117F6E1DC25','F1CCEA59-B956-4A4A-BDAD-5AE92C39E5D3','C9321C79-D8DF-4E25-832F-92D3298514F7'
SELECT '668D98C0-9553-4349-B937-B6810DCD613E','2014-08-06 09:00:00.000','2014-08-05 10:00:00.000',0,'EF273F9D-D777-474D-BF5E-A117F6E1DC25','F1CCEA59-B956-4A4A-BDAD-5AE92C39E5D3','C9321C79-D8DF-4E25-832F-92D3298514F7'
如何将开始时间和结束时间拆分为每个小时的时间段,
比如第一条开始时间是'2014-08-05 13:30:00.000',结束时间是'2014-08-05 16:00:00.000'
那么就是13,14,15
第二条就是10,11,12,
第三条就是9
并且将每个小时的时间差写出来
比如第一条开始时间是'2014-08-05 13:30:00.000',结束时间是'2014-08-05 16:00:00.000'
那么就是 0.5, 1,1
第二条就是 0.5,1,0.5
第三条就是 1
------解决方案--------------------
把你要显示的结果列一下，这样才好根据你的需求去写sql
另外，你的时间差怎么计算，有的根据开始时间算，有的根据截止时间算；
------解决方案--------------------
LZ所提供脚本执行报错,
消息 2627，级别 14，状态 1，第 16 行
违反了 PRIMARY KEY 约束 'PK__Booking___550DAB732A4B4B5E'。不能在对象 'dbo.Booking_Equipment_Information' 中插入重复键。
语句已终止。
------解决方案--------------------
/* 假设初始数据为表T1 */
with T1 (Equipment_VGUID, startTime, endTime, BookingStatus, BookingUser, VGUID, LGUID) as
(
SELECT '668D98C0-9553-4349-B937-B6810DCD613E','2014-08-05 13:30:00.000','2014-08-05 16:00:00.000',0,'EF273F9D-D777-474D-BF5E-A117F6E1DC25','93B0EA55-EF56-4216-89BF-2D4CA0102169','C9321C79-D8DF-4E25-832F-92D3298514F7' union all
SELECT '668D98C0-9553-4349-B937-B6810DCD613E','2014-08-05 10:30:00.000','2014-08-05 12:30:00.000',0,'EF273F9D-D777-474D-BF5E-A117F6E1DC25','424531B6-75CB-4DB3-A8AB-547F180C238D','C9321C79-D8DF-4E25-832F-92D3298514F7' union all
SELECT '668D98C0-9553-4349-B937-B6810DCD613E','2014-08-06 09:00:00.000','2014-08-06 10:00:00.000',0,'EF273F9D-D777-474D-BF5E-A117F6E1DC25','D9E38051-A5EB-4C05-9CCF-26D896CB565A','C9321C79-D8DF-4E25-832F-92D3298514F7'
)
, T2 as
(
select Equipment_VGUID, startTime, endTime, BookingStatus, BookingUser, VGUID, LGUID
, hh = datepart(hour,startTime) + v.number - 1
, mm = case when v.number=1 then datepart(minute,startTime)
when v.number=datediff(hour,startTime,endTime) + case datepart(minute, endTime) when 0 then 0 else 1 end then datepart(minute,endTime)
else 60 end
from T1 cross join
master.dbo.spt_values v
where v.type = 'P'
and v.number between 1 and datediff(hour,startTime,endTime) + case datepart(minute, endTime) when 0 then 0 else 1 end
)
, T3 as
(
select Equipment_VGUID, startTime, endTime, BookingStatus, BookingUser, VGUID, LGUID
, hh = convert(varchar,hh)
, mm = convert(varchar, convert(real, mm/60.0) )
from T2
)
, T4 as
(
select Equipment_VGUID, startTime, endTime, BookingStatus, BookingUser, VGUID, LGUID
, [0h] = max(case hh when 0 then hh end), [1h] = max(case hh when 1 then hh end), [2h] = max(case hh when 2 then hh end)
, [3h] = max(case hh when 3 then hh end), [4h] = max(case hh when 4 then hh end), [5h] = max(case hh when 5 then hh end)
, [6h] = max(case hh when 6 then hh end), [7h] = max(case hh when 7 then hh end), [8h] = max(case hh when 8 then hh end)
, [9h] = max(case hh when 9 then hh end), [10h] = max(case hh when 10 then hh end), [11h] = max(case hh when 11 then hh end)
, [12h] = max(case hh when 12 then hh end), [13h] = max(case hh when 13 then hh end), [14h] = max(case hh when 14 then hh end)