3,256 answered market questions

SELECT
    toString(s.d)                                                                                                    AS trade_date,
    concat(formatDateTime(s.d, '%a'), ', ', formatDateTime(s.d, '%b'), ' ', toString(toDayOfMonth(s.d)))              AS traded_on,
    concat(formatDateTime(s.next_open, '%a'), ', ', formatDateTime(s.next_open, '%b'), ' ', toString(toDayOfMonth(s.next_open))) AS settles_on,
    dateDiff('day', s.d, s.next_open)                                                                                AS calendar_days_to_settle
FROM
(
    SELECT
        d,
        leadInFrame(d) OVER (ORDER BY d ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_open
    FROM
    (
        SELECT d
        FROM
        (
            SELECT today() + arrayJoin(range(0, 45)) AS d
        )
        WHERE toDayOfWeek(d) BETWEEN 1 AND 5
          AND d NOT IN
          (
              SELECT date
              FROM global_markets.stocks_market_holidays
              WHERE status = 'closed'
          )
    )
) AS s
WHERE s.next_open > s.d
ORDER BY s.d
LIMIT 15