Sql 根据开始日期和结束日期的差异计算每天的计费

最后，我为我的问题写了以下查询-

WITH toolsdatastats AS  --gets the min and max date among all records
    (
        SELECT trunc(min(start_date)) earliestdate,  trunc(max(end_date)) latestdate
        FROM billing
    ),
    dates AS  --gets the full date from system between the min and max dates found earlier
    (
        SELECT *
        FROM date_d
        WHERE fulldate >= (SELECT earliestdate FROM toolsdatastats)
            AND fulldate <= (SELECT latestdate FROM toolsdatastats)
    ),
    dailydata AS
    (
        SELECT fulldate full_date, account_id, amount, start_date, end_date,
            CASE
                WHEN td.start_date < dates.fulldate
                    THEN dates.fulldate
                ELSE td.start_date
            END AS usage_start_for_the_day,
            CASE
                WHEN td.end_date > DATEADD(day, 1, dates.fulldate)
                    THEN DATEADD(day, 1, dates.fulldate)
                ELSE td.end_date
            END AS usage_end_for_the_day
        FROM billing td
        JOIN dates on dates.fulldate>=trunc(td.start_date) and dates.fulldate<=trunc(td.end_date)
        ORDER BY account_id, fulldate
    ),
    dailycost as 
    (
      select full_date, account_id, usage_start_for_the_day, usage_end_for_the_day, start_date, end_date,
        CASE 
          WHEN (full_date = trunc(start_date) and trunc(usage_start_for_the_day) = trunc(usage_end_for_the_day))
          THEN amount
          ELSE amount/(datediff(day, start_date, end_date) + 1) 
        END AS amount
        from dailydata
    )
    select full_date, account_id, sum (amount) from dailycost group by full_date, account_id order by full_date;