CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110103

Calculate billing usage for current month

I am trying to calculate storage usage for the current month. Here is how the table would look:

stored_on              deleted_on          amount_in_gb    rate_per_gb_per_month
2014-01-01 12:00:00    2014-05-09 00:00:00   20            0.05 
2015-01-01 00:00:00    NULL                  4.2            0.05 
2015-01-01 12:00:00    2015-01-09 00:00:00   7.2            0.05 
2016-01-01 12:00:00    NULL                  100            0.05

To get the amount of usage for the month of January, 2015, it would be:

  $0.05 * 20 * 0        = $0.00 (this item was deleted before the start of the month)
+ $0.05 * 4.2 * 1       = $0.21 (for the second line item, the item is stored for a full month)
+ $0.05 * 7.2 * (~8/31) = $0.09 (stored for about 8 of 31 days in the month)
+ $0.05 * 100 * 0       = $0.00 (this item was added after this month)
---------------------------------
TOTAL                   = $0.30

How would I do the above in SQL? Basically, given a particular month, to calculate the usage for that month, taking into effect the fact that the stored_on value may be before the month starts, during the month, or after the month; same with the deleted_on value.

Upvotes: 4

Views: 106

Answers (2)

David542
David542

Reputation: 110103

The following is a bit more verbose than Gordon's answer but is a bit easier for me to follow (and also for me to fix syntax errors) -- 

SELECT 
    start_date_of_storage_on_month,
    end_date_of_storage_on_month, 
    (1 + datediff(end_date_of_storage_on_month, start_date_of_storage_on_month)) num_days,
    ((1 + datediff(end_date_of_storage_on_month, start_date_of_storage_on_month)) / DAY(LAST_DAY('2015-01-01'))) * amount_in_gb * rate_per_gb_per_month total_in_usd

    FROM (select

    CASE
        when stored_on >= '2015-31-01' then NULL
        when deleted_on <= '2015-01-01' then NULL
        else date(greatest('2015-01-01', stored_on))
    END start_date_of_storage_on_month,

    CASE
        when deleted_on is null then '2015-01-31'
        when deleted_on >= '2015-31-01' then '2015-01-31'
        else date(deleted_on)
    END end_date_of_storage_on_month,
    billing.*

from billing) t

This gives the correct value of $0.31, though it will show each by line item -- to get the sum just do SUM(...) of the total_in_usd value.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This should calculate the pro-rated amount in January:

select sum( rate_per_gb_per_month * amount_in_gb *
        greatest(1 +
                 datediff(least('2015-01-31', coalesce(deleted_on, '2015-01-31')) ,
                          greatest('2015-01-01', stored_on)
                         ), 0
             ) / DAY(LAST_DAY('2015-01-01'))  
      ) as usage_cost

from t;

Here is a SQL Fiddle.

Upvotes: 2

Related Questions