申根区180天/90天停留规则SQL计算方案问询

欧盟申根协议允许约50个国家的游客在任意180天周期内最多停留90天。现假设2023年2月1日入境申根区，停留至2023年5月1日（共90天），需计算2023年底前每日可再次入境的停留天数。

已知Excel计算结果：2023年9月1日返回时，过去180天（起始日为2023年3月5日）内累计停留59天，该数值将保持至2023年10月30日，之后停留天数均来自本次9月1日的入境。

现有实现通过硬编码日期创建了包含2年日期的临时表#Vaca，标记每日境内外状态，但需要通用SQL语句支持以下需求：

• 规划最早返回日期（如2023年8月1日）
• 给定指定返回日，计算要求的离境日期

现有硬编码实现代码 #

DROP TABLE IF EXISTS #Vaca
CREATE TABLE #Vaca
(
    VacationDate date
    ,[Location] nvarchar(100)
    ,InEu int
)

DECLARE @n int = 0
        ,@StartDate date = N'2022/06/01'

WHILE @n < 730
BEGIN
    INSERT INTO #Vaca
    (
        VacationDate
        ,[Location]
        ,InEu
    )
    VALUES (@StartDate
            ,CASE WHEN @StartDate BETWEEN N'2023/02/01' and N'2023/05/01'THEN N'EU'
                    WHEN @StartDate BETWEEN N'2023/09/01' and N'2023/11/29'THEN N'EU'
                    ELSE N'Outside EU'
            END
            ,CASE WHEN @StartDate BETWEEN N'2023/02/01' and N'2023/05/01'THEN 1
                    WHEN @StartDate BETWEEN N'2023/09/01' and N'2023/11/29'THEN 1
                    ELSE 0
            END
    )

    SET @n += 1
    SET @StartDate = DATEADD(DAY, 1, @StartDate)
END

SELECT  v.[Location]
        ,v.VacationDate
        ,SUM(v.InEu) OVER    (   ORDER BY    v.VacationDate ASC
                                    ROWS    179 PRECEDING
                                ) AS Test

FROM    #Vaca AS v

WHERE   1 = 1

GROUP BY    v.VacationDate
            ,v.[Location]
            ,v.InEu

ORDER BY    2 ASC

通用化SQL解决方案 #

1. 参数化存储停留记录与动态生成日期表 #

替换硬编码逻辑，用临时表存储已知停留区间，动态生成所需分析日期范围：

-- 清理临时表
DROP TABLE IF EXISTS #KnownStays;
DROP TABLE IF EXISTS #DateRange;

-- 存储已知的申根区停留记录（可灵活添加/修改）
CREATE TABLE #KnownStays (
    StayStart DATE,
    StayEnd DATE
);

-- 插入示例停留记录
INSERT INTO #KnownStays (StayStart, StayEnd)
VALUES 
    ('2023-02-01', '2023-05-01'),  -- 第一次90天停留
    ('2023-09-01', '2023-11-29');  -- 第二次停留（按需调整）

-- 定义分析日期范围：从最早停留前180天到2023年底
DECLARE @AnalysisStart DATE = (SELECT DATEADD(DAY, -180, MIN(StayStart)) FROM #KnownStays);
DECLARE @AnalysisEnd DATE = '2023-12-31';

-- 递归生成连续日期表
WITH DateCTE AS (
    SELECT @AnalysisStart AS VacationDate
    UNION ALL
    SELECT DATEADD(DAY, 1, VacationDate)
    FROM DateCTE
    WHERE VacationDate < @AnalysisEnd
)
SELECT 
    VacationDate,
    CASE 
        WHEN EXISTS (SELECT 1 FROM #KnownStays ks WHERE dc.VacationDate BETWEEN ks.StayStart AND ks.StayEnd) 
        THEN 'EU' 
        ELSE 'Outside EU' 
    END AS Location,
    CASE 
        WHEN EXISTS (SELECT 1 FROM #KnownStays ks WHERE dc.VacationDate BETWEEN ks.StayStart AND ks.StayEnd) 
        THEN 1 
        ELSE 0 
    END AS InEu
INTO #DateRange
FROM DateCTE
OPTION (MAXRECURSION 0); -- 支持超过100天的递归生成

2. 计算每日180天周期内停留数据 #

用窗口函数计算每个日期的累计停留天数及剩余可停留天数：

SELECT 
    VacationDate,
    Location,
    InEu,
    SUM(InEu) OVER (
        ORDER BY VacationDate ASC
        ROWS BETWEEN 179 PRECEDING AND CURRENT ROW
    ) AS TotalStayIn180Days,
    90 - SUM(InEu) OVER (
        ORDER BY VacationDate ASC
        ROWS BETWEEN 179 PRECEDING AND CURRENT ROW
    ) AS RemainingAllowedDays
FROM #DateRange
ORDER BY VacationDate ASC;

3. 查询指定日期后最早可返回日期 #

例如，查找2023年8月1日之后最早可入境且有剩余停留额度的日期：

SELECT TOP 1 
    VacationDate AS EarliestReturnDate,
    RemainingAllowedDays
FROM (
    SELECT 
        VacationDate,
        90 - SUM(InEu) OVER (
            ORDER BY VacationDate ASC
            ROWS BETWEEN 179 PRECEDING AND CURRENT ROW
        ) AS RemainingAllowedDays
    FROM #DateRange
    WHERE VacationDate >= '2023-08-01'
) t
WHERE RemainingAllowedDays > 0
ORDER BY VacationDate ASC;

4. 给定返回日期，计算最晚离境日期 #

假设指定返回日为2023-09-01，计算符合规则的最晚离境日期：

DECLARE @ReturnDate DATE = '2023-09-01';

WITH StaySimulation AS (
    SELECT 
        VacationDate,
        -- 模拟从返回日开始入境，标记当日及后续为境内状态
        CASE WHEN VacationDate >= @ReturnDate THEN 1 ELSE InEu END AS SimulatedInEu,
        -- 计算模拟后的180天累计停留天数
        SUM(CASE WHEN VacationDate >= @ReturnDate THEN 1 ELSE InEu END) OVER (
            ORDER BY VacationDate ASC
            ROWS BETWEEN 179 PRECEDING AND CURRENT ROW
        ) AS SimulatedTotalStay
    FROM #DateRange
    WHERE VacationDate >= @ReturnDate
)
SELECT MAX(VacationDate) AS LatestDepartureDate
FROM StaySimulation
WHERE SimulatedTotalStay <= 90;

优化说明 #

• 参数化配置：所有停留记录和分析范围通过临时表/参数定义，无需修改核心逻辑
• 高效日期生成：递归CTE比WHILE循环插入更高效，支持任意长度的日期范围
• 灵活查询：快速支持最早返回日、最晚离境日等多样化需求
• 可维护性：逻辑清晰，新增停留记录只需插入#KnownStays即可

内容的提问来源于stack exchange，提问作者Doran