我想你是想做下面这样的事情。我使用CTE。。。我在其中添加了一个索引号(ROW_number),并按statStart对行进行排序。我在WHERE子句中排除了非正午值。为了消除“间隙”,您可以选择下一行的startstat。我不使用原始的endtime值。
DECLARE @temp TABLE (Item varchar(100), Record int, StatType varchar(100), statStart datetime, statEnd datetime)
INSERT INTO @temp VALUES
('Plow', 8860, 'Operational', '2017-08-29 16:59:48.907', '2017-11-06 15:35:15.290')
,('Plow', 8859, 'Inspection', '2017-08-29 16:56:59.460', '2017-08-29 16:58:48.907')
,('Plow', 8830, 'Operational', '2017-08-23 18:38:45.530', '2017-08-29 16:55:59.460')
,('Plow', 8730, 'Inspection', '2017-07-31 15:34:10.387', '2017-08-23 18:37:45.530')
,('Plow', 8625, 'Operational', '2017-07-17 11:22:00.000', '2017-07-31 15:33:10.387')
,('Plow', 8615, 'Inspection', '2017-07-17 09:22:58.127', '2017-07-17 11:21:00.000')
,('Plow', 8406, 'Operational', '2017-06-05 14:42:32.837', '2017-07-17 09:21:58.127')
,('Plow', 8399, 'Inspection', '2017-06-02 17:02:55.107', '2017-06-05 14:41:32.837')
,('Plow', 8332, 'Operational', '2017-05-23 09:30:37.707', '2017-06-02 17:01:55.107')
DECLARE @startDate datetime = '2017-07-15 12:00:00'
,@endDate datetime = '2017-08-03 12:00:00';
WITH cte AS
(
SELECT ROW_NUMBER() OVER(ORDER BY statStart) [idval]
,T.*
FROM @temp T --your tablename
WHERE (CAST(statStart as time) < '12:00:00' AND CAST(statEnd as time) > '12:00:00'
OR
DATEDIFF(HOUR, statStart, statEnd) >= 24
)
)
,
cte2 AS
(
SELECT @startDate as dateval
UNION ALL
SELECT DATEADD(DAY, 1, dateval)
FROM cte2
WHERE dateval < @endDate
)
SELECT C2.dateval, dT.Item, dT.Record, dT.StatType, dT.statStart, dT.StatEnd
,DATEDIFF(DAY, statStart, StatEnd) [StatusDays]
FROM (
SELECT C.idval, C.Item, C.Record, C.StatType, C.statStart --, C.statEnd --this has the 'gap'
,COALESCE((SELECT MAX(C2.statStart) FROM cte C2 WHERE C2.idval > C.idval AND C2.StatType = C.StatType), C.statEnd) AS [StatEnd]
FROM cte C
) AS dT
INNER JOIN cte2 C2 ON C2.dateval between dT.statStart AND dT.[StatEnd]
