SQL sum amount for each date

Question

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.

Luuk · Answer 1 · 2019-10-12T11:35:09.427

0

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

edited Oct 12 '19 at 11:35

answered Oct 12 '19 at 10:32

Luuk

12,245
5
22
33

I should have mentioned I run MySQL , and this is not compatible...also I do not need extra days with 0 amount – Dreg Korig Oct 12 '19 at 10:34
Above is indeed a solution for MS-SQL. What version of MySQL, because 8+ has WITH (https://dev.mysql.com/doc/refman/8.0/en/with.html) – Luuk Oct 12 '19 at 10:36
If I select MySQL in the db-fiddle it throws me an error – Dreg Korig Oct 12 '19 at 10:37
Incorrect parameter count in the call to native function 'ISNULL', also I see you select manually "1 as id" and "2 as id", but it could have a lot more ids... – Dreg Korig Oct 12 '19 at 10:46

Gnyasha · Answer 2 · 2019-10-12T10:57:06.417

0

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

edited Oct 12 '19 at 10:57

answered Oct 12 '19 at 10:48

Gnyasha

658
11
19

`syntax error near 'declare @startDate date ='11 Oct 2019' declare @endDate date ='30 Oct 2019' decl' at line 1` – Dreg Korig Oct 12 '19 at 10:53
Ok apologies I needed to mention I was using MSSQL – Gnyasha Oct 12 '19 at 10:56

score 0 · Answer 3 · answered Oct 12 '19 at 11:37

0

select start_date,amount,
amount+lag(amount)over(order by start_date) TOTAL
from  rentals order by start_date

answered Oct 12 '19 at 11:37

Kiran Patil

327
1
11

score 0 · Answer 4 · answered Oct 12 '19 at 12:32

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

SQL sum amount for each date

4 Answers4

Linked