如何在SQL中获取3天范围内的记录分组日期? [英] How to get the records grouping dates for a span of 3 days in SQL?

问题描述

每 5 天对记录进行分组和显示变得越来越困难.

It's getting difficult to group and display records every 5 days.

这是我的数据:

FLIGHT_DATE LANDINGS    PILOTID COPILOTNAME MONT    DPT

11/16/2013  1   A   B   11  5.5
11/17/2013  1   A   B   11  13
11/19/2013  1   A   B   11  12.55
11/19/2013  1   A   B   11  4
11/21/2013  1   A   B   12  6
11/24/2013  1   A   B   12  6.03
11/25/2013  1   A   B   11  5.5
11/26/2013  1   A   B   11  13
11/26/2013  1   A   B   11  12.55
11/30/2013  1   A   B   11  4
12/1/2013   1   A   B   12  6
12/2/2013   1   A   B   12  6.03        

我想显示如下:

Week Start  Week End    DPT         
11/17/2013  11/21/2013  35.55           
11/22/2013  11/26/2013  37.08           
11/27/2013  12/1/2013   6           
12/2/2013   12/6/2013   6.03

推荐答案

这是我提出的解决方案:

Here it is my proposed solution:

DECLARE @MinDate AS DATETIME = (SELECT MIN(flight_date) FROM flights);

WITH cte
AS
(
    SELECT
        flight_date, DATEDIFF(DAY, @MinDate, flight_date) AS NoDays,
        DATEDIFF(DAY, @MinDate, flight_date)/5 AS NoGroup,
        DPT
    FROM flights
)
SELECT  
    DATEADD(DAY, NoGroup*5, @MinDate) AS [Week Start],
    DATEADD(DAY, NoGroup*5+4, @MinDate) AS [Weed End],  
    SUM(DPT)
FROM cte
GROUP BY NoGroup;

我们的想法是形成 5 天的组，然后根据除法与 5 将记录关联到特定组.NoDays 表示从 MinDate 到 Flight_Date 花费的天数.

The idea is to form groups of 5 days, then associate a record to a specific group based on division with 5. NoDays represents the days spent from MinDate to Flight_Date.

这篇关于如何在SQL中获取3天范围内的记录分组日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！