Question 304803 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Calculate billing for per day based on difference between start and end date

I have data in table as below:

Account Id Amount Start_date End_date
123 10 2023-11-16 17:00:00 2023-11-16 18:00:00
123 10 2023-11-16 02:00:00 2023-11-17 02:00:00
123 20 2023-11-17 17:00:00 2023-11-17 18:00:00
123 30 2023-11-18 02:00:00 2023-11-20 02:00:00
123 10 2023-11-18 17:00:00 2023-11-18 18:00:00
123 20 2023-11-19 02:00:00 2023-11-20 02:00:00

I need to calculate the amount for per day for one Cloud Id.
For ex – For Cloud Id 123, in the first row, the start date and end date has interval of 1 so amount of 10 will be included for date of 2023-11-16. The second row, the difference between start date and second day is 2, so the amount of 10 will be calculated for date of 16 as 10/2(difference) i.e. 2 and for date of 17 as 10/2 = 5.

11-16 10+5 = 15
11-17 5+20 = 25
11-18 10+10= 20
11-19 10+10= 20

I need to write a query to get the results in above way.

so far I have tried the following query but I am getting syntax error –

select start_date, end_date, account_id, amount, datediff(day, start_date, end_date) as interval into #temp 
from billing where account_id = '123';

select * from #temp;
drop table if exists #result;
select account_id, start_date, end_date, interval, 
  CASE WHEN interval = 0 THEN amount ELSE amount/(interval + 1) END AS per_day_cost
INTO #result
UNION ALL
SELECT  account_id, start_date, end_date, interval, 
  CASE WHEN interval = 0 THEN amount ELSE amount/(interval + 1) END AS per_day_cost
FROM (VALUES (1), (2), (3)) as v(n) CROSS JOIN
  (SELECT account_id, start_date, end_date, interval, amount FROM #temp) s
where v.n <= interval
order by account_id;

select * from #result;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up writing the following query for my question -

    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;

  2. 0

    You can change a little bit your query (without using temporary tables)

    SELECT  account_id, start_date, end_date
   ,datediff(day, start_date, end_date) interval
   ,amount/(datediff(day, start_date, end_date) + 1)AS per_day_cost
   ,Id, amount as pAmount  -- only for debug
FROM (VALUES (0),(1), (2), (3)) as v(n) 
CROSS JOIN
  (SELECT account_id, start_date, end_date
     ,datediff(day, start_date, end_date) interval
     ,amount ,Id
   FROM billing
  ) s
where v.n <= datediff(day, start_date, end_date)
order by account_id;

    I’ll add Id column to test table for clarity only.
    For test data

    create table billing (Id int,Account_Id int,Amount float,Start_date datetime,   End_date datetime);
insert into test values
 (1,123,    10,'2023-11-16 17:00:00','2023-11-16 18:00:00')
,(2,123,    10,'2023-11-16 02:00:00','2023-11-17 02:00:00')
,(3,123,    20,'2023-11-17 17:00:00','2023-11-17 18:00:00')
,(4,123,    30,'2023-11-18 02:00:00','2023-11-20 02:00:00')
,(5,123,    10,'2023-11-18 17:00:00','2023-11-18 18:00:00')
,(6,123,    20,'2023-11-19 02:00:00','2023-11-20 02:00:00')
;

    result is

    account_id start_date end_date interval per_day_cost Id pAmount
    123 2023-11-16 17:00:00.000 2023-11-16 18:00:00.000 0 10 1 10
    123 2023-11-16 02:00:00.000 2023-11-17 02:00:00.000 1 5 2 10
    123 2023-11-16 02:00:00.000 2023-11-17 02:00:00.000 1 5 2 10
    123 2023-11-17 17:00:00.000 2023-11-17 18:00:00.000 0 20 3 20
    123 2023-11-18 02:00:00.000 2023-11-20 02:00:00.000 2 10 4 30
    123 2023-11-18 02:00:00.000 2023-11-20 02:00:00.000 2 10 4 30
    123 2023-11-18 02:00:00.000 2023-11-20 02:00:00.000 2 10 4 30
    123 2023-11-18 17:00:00.000 2023-11-18 18:00:00.000 0 10 5 10
    123 2023-11-19 02:00:00.000 2023-11-20 02:00:00.000 1 10 6 20
    123 2023-11-19 02:00:00.000 2023-11-20 02:00:00.000 1 10 6 20

    You can join table and "series" query v(n) in direct order

    SELECT  billing.* 
   ,datediff(day, start_date, end_date) interval
   ,amount/(datediff(day, start_date, end_date) + 1)AS per_day_cost
   ,amount as pAmount  -- only for debug
FROM billing
left join (select 0 n union all select 1 union all select 2 union all select 3)v
   on v.n <= datediff(day, start_date, end_date)
order by account_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search