SQL sum amount for each date

user_9452821 0
Viewed 2712

I have a table called "rentals" in which I store data like the following:

id | rent_id | start_date | end_date  | amount
---------------------------------------------
1  |   54    | 12-10-2019 | 26-10-2019| 100
2  |   54    | 13-10-2019 | 20-10-2019| 150

What do I expect? A result like:

12-10-2019 , amount 100
from 13-10-2019 to 20-10-2019, amount 250
from 21-10-2019 to 26-10-2019, amount 100

Basically I want, for each day , the sum of amount. But I also want to calculate "days between".

So the expected result would be:

    id | rent_id | day        |  amount
    ---------------------------------------------
    1  |   54    | 12-10-2019 | 100
    2  |   54    | 13-10-2019 | 250
    3  |   54    | 14-10-2019 | 250

and so on...

I'm actually running the following sql:

select start_date, ( select sum(amount) from rentals as t2 where t2.start_date <= t1.start_date) as amount from rentals as t1 WHERE rent_id = 54 group by start_date

but the result is not like expected...

I'm using MySQL.

sum date mysql
4 Answers

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9be7f2c2af8ba8ddae6a14cec807ebd2

with rentals(id, rent_id, start_date, end_date, amount) as (
    SELECT 1 as id,  54 as rent_id,  CAST('2019-10-12' AS DATE) as start_date,  CAST('2019-10-26' AS DATE) as end_date, 100 as amount
    union all 
    SELECT 2 as id,  54 as rent_id,  CAST('2019-10-13' AS DATE) as start_date,  CAST('2019-10-20' AS DATE) as end_date, 150 as amount
),
days(i) as (
    select CAST('2019-10-11' as DATE)
    union all 
    select DATEADD(d,1,i) from days where i<= '2019-10-27'
)

select 
    i ,
    (SELECT ISNULL(SUM(amount),0) FROM rentals WHERE start_date<=i and end_date>=i) as amount
from days

The temporary days is having all the days from 11-okt to 27-okt

The MySQL solution (MySQL 8.0):

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=575b2c4ebe1ed9f05883e87f75f888ef

user_7240390

I think this may help using MSSQL

declare @startDate date ='11 Oct 2019'
declare @endDate date ='30 Oct 2019'
declare @rentId nvarchar(50)='54'


select d.Day,d.rent_id,isnull(d.amount,0) as amountPaid, amount = SUM(d.amount) OVER (ORDER BY d.Day) from (
select c.Day,c.id,case when rent_id is null then @rentId else rent_id end as rent_id,c.amount  from (
select * from (
SELECT DATEADD(DAY,number+1,@startDate) [Day]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number+1,@startDate) < @endDate
) a left join (select * from rentals where rent_id=@rentId) b on a.[Day] = b.start_date) c) d
user_65780550 
select start_date,amount,
amount+lag(amount)over(order by start_date) TOTAL
from  rentals order by start_date
user_85432010

A common solution to this question involves first creating a calendar table. That's a table that stores all possible dates that your query needs to deal with. There are solutions available in the wild to create and fill the table, for example you can have a look at this SO post.

So let's assume a table with the following structure and data:

CREATE TABLE all_dates as (my_date DATE PRIMARY KEY);
INSERT INTO all_dates VALUES
    ('12-10-2019'),
    ('13-10-2019'),
    ('14-10-2019'),
    ...
;

Now your question can be solved simply by joining your table with the calendar table and aggregating the results, as follows:

SELECT r.rent_id, d.my_date `day`, SUM(amount) amount
FROM all_dates d
INNER JOIN rentals r ON d.my_date BETWEEN r.start_date AND r.end_date
GROUP BY r.rent_id, d.my_date

Note: your intent for the resulting id column is unclear. You seem to be willing to create a new unique id. If so, one option would be to use ROW_NUMBER(), as follows:

SELECT ROW_NUMBER() OVER(ORDER BY rent_id, `day`) id, x.*
FROM (
    SELECT r.rent_id, d.my_date `day`, SUM(amount) amount
    FROM all_dates d
    INNER JOIN rentals r ON d.my_date BETWEEN r.start_date AND r.end_date
    GROUP BY r.rent_id, d.my_date
) x
user_106767160
Related