时间戳

首页 > 解决方案 > 重复天数时将持续时间计算为分数

sql - 重复天数时将持续时间计算为分数

问题描述

我有以下测试数据，它是通过以下查询生成的：

select 
    ID,
    SequenceID,
    [Sub Status Start Date],
    [Sub Status End Date],
    DateDiff(d,[Sub Status Start Date],[Sub Status End Date]) as [Duration Days]
from SampleData

我不知道如何去做的是，在相同的开始日期和结束日期多次出现的情况下，将持续时间计算为除以重复次数的分数。这显示在屏幕截图中，对于 ID 288，有 4 个重复项，因此将持续时间分成 4 到 0.25 个。对于 ID 404，有 3 个，因此每个都为 0.33。但是，对于 ID 239，只有 1，所以它保持为 0。

在此处输入图像描述

样本数据

CREATE TABLE [dbo].[SampleData](
    [ID] [int] NOT NULL,
    [SequenceID] [int] NOT NULL,
    [Sub Status Start Date] [datetime] NULL,
    [Sub Status End Date] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 1, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 2, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 3, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 4, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-06T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 5, CAST(N'2019-08-06T00:00:00.000' AS DateTime), CAST(N'2019-08-13T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 6, CAST(N'2019-08-09T00:00:00.000' AS DateTime), CAST(N'2019-08-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 7, CAST(N'2019-08-13T00:00:00.000' AS DateTime), CAST(N'2019-10-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (288, 8, CAST(N'2019-10-03T00:00:00.000' AS DateTime), CAST(N'2019-10-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 1, CAST(N'2019-11-02T00:00:00.000' AS DateTime), CAST(N'2019-11-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 2, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 3, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 4, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 5, CAST(N'2019-11-28T00:00:00.000' AS DateTime), CAST(N'2019-11-29T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (404, 6, CAST(N'2019-11-29T00:00:00.000' AS DateTime), NULL)
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 1, CAST(N'2019-06-25T00:00:00.000' AS DateTime), CAST(N'2019-06-25T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 2, CAST(N'2019-06-25T00:00:00.000' AS DateTime), CAST(N'2019-06-26T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 3, CAST(N'2019-06-26T00:00:00.000' AS DateTime), CAST(N'2019-06-28T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 4, CAST(N'2019-06-28T00:00:00.000' AS DateTime), CAST(N'2019-08-04T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[SampleData] ([ID], [SequenceID], [Sub Status Start Date], [Sub Status End Date]) VALUES (239, 5, CAST(N'2019-07-01T00:00:00.000' AS DateTime), CAST(N'2019-08-04T00:00:00.000' AS DateTime))
GO

标签： sqlsql-servertsql

解决方案

尝试这个

小提琴演示

SELECT ID,SequenceID,[Sub Status Start Date],[Sub Status End Date], DATEDIFF(d,[Sub Status Start Date],[Sub Status End Date]) AS [Duration Days],
       CASE WHEN Counts > 1 THEN CAST(1.0 / X.Counts AS DECIMAL(18,2)) 
            WHEN Counts = 1AND X.[Duration Days] > 0 THEN X.[Duration Days]
       ELSE 0 END [What The Result Should Be]
FROM
(
    SELECT ID,SequenceID,[Sub Status Start Date],[Sub Status End Date], DATEDIFF(d,[Sub Status Start Date],[Sub Status End Date]) AS [Duration Days],       
    Counts = COUNT(*) OVER (PARTITION BY ID,[Sub Status Start Date],[Sub Status End Date] ORDER BY ID)
    FROM SampleData
)X ORDER BY ID,SequenceID

推荐阅读