跨月时长的后续月份标记及基于订单与日历表的SQL实现咨询

First, let's complete the calendar table creation code since your CTE was cut off — this will generate all dates from 2018-01-01 to 2019-12-31:

create table #orders ( 
    order_id int identity(1,1) primary key ,
    start_date date ,
    end_date date 
) 

insert into #orders ( start_date ,end_date )
values
('2018-01-05','2019-02-04'),
('2018-03-15','2019-07-14') 

create table #calendar_dates ( [date] date ) 

;WITH calendar AS (
    SELECT dates = CONVERT(date, '2018-01-01' ) 
    UNION ALL 
    SELECT dates = DATEADD(DAY, 1, dates) 
    FROM calendar 
    WHERE dates <= CONVERT(date, '2019-12-31')
)
INSERT INTO #calendar_dates ([date])
SELECT dates FROM calendar
OPTION (MAXRECURSION 0); -- Required for recursions over 100 days

Now, here's the SQL solution to mark subsequent months for orders that run longer than one month:

WITH order_months AS (
    -- Get all unique months covered by each order, plus key metadata
    SELECT 
        o.order_id,
        o.start_date,
        o.end_date,
        -- First day of the current date's month (for grouping)
        DATEFROMPARTS(YEAR(c.date), MONTH(c.date), 1) AS month_start,
        -- First day of the order's starting month
        DATEFROMPARTS(YEAR(o.start_date), MONTH(o.start_date), 1) AS first_month,
        -- Flag if the order exceeds one month (handles both day count and cross-month cases)
        CASE 
            WHEN DATEDIFF(DAY, o.start_date, o.end_date) > 30 
                OR DATEFROMPARTS(YEAR(o.start_date), MONTH(o.start_date), 1) <> DATEFROMPARTS(YEAR(o.end_date), MONTH(o.end_date), 1)
            THEN 1 
            ELSE 0 
        END AS is_over_one_month
    FROM #orders o
    JOIN #calendar_dates c 
        ON c.date BETWEEN o.start_date AND o.end_date
    GROUP BY 
        o.order_id,
        o.start_date,
        o.end_date,
        DATEFROMPARTS(YEAR(c.date), MONTH(c.date), 1),
        DATEFROMPARTS(YEAR(o.start_date), MONTH(o.start_date), 1)
)
SELECT 
    order_id,
    month_start,
    first_month,
    is_over_one_month,
    -- Mark subsequent months only if the order is over one month and not the first month
    CASE 
        WHEN is_over_one_month = 1 AND month_start <> first_month 
        THEN 1 
        ELSE 0 
    END AS is_subsequent_month
FROM order_months
ORDER BY order_id, month_start;

How This Works: #

1. CTE order_months:

   • Joins your orders and calendar tables to map every date covered by an order, then groups by month to get unique months per order.
   • Calculates first_month to identify the starting month of each order.
   • The is_over_one_month flag accounts for two common scenarios:
     • Orders that span more than 30 days
     • Orders that cross calendar months (even if the day count is under 30, like 2024-01-31 to 2024-02-28)
   • Adjust this condition if your definition of "exceeding one month" is stricter (e.g., only use DATEDIFF(DAY, ...) > 30).

2. Final Query:

   • Uses the is_over_one_month flag and compares month_start to first_month to mark all months after the first as subsequent.

Example Output Snippet: #

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