Sum values by date-range

[faroinsua]

Hello, I have gone through a few topics regarding the total of columns but I could not find the way to sum the values of a column between two date values.
Each row has a date and amount. Is it difficult to sum al the amounts between two dates? I was thinking about a new form and new table where these sums are registered. Each row would contain
From-date, to-date and sum. The problem is the filtering by date range and summing values from another table. Maybe it is possible through reports...

[kev1n]

Can you perhaps illustrate your plan with a few screenshots / excel table etc. so that it is less abstract?

[faroinsua]

What I want to create is Form2. It sums values from Form1 depending on which date values I enter in Form2.

sum.PNG

[kev1n]

Are the From-Booking-date and To-Booking-date entered in another table / form?

[faroinsua]

Yes. One table for each form.
The story behind this is one booking table an another table where I get the sum of the bookings shown by quarters 1.1.2020-31.03.2020 or whatever start and enddate I choose.
But if there is an easier solution than with a separate form and table, it wouldn´t matter.

[kev1n]

Something like that?

https://streamable.com/ypdkn

booking_sum.PNG

Code used in PHP BB (Before Browse):

Code: Select all

$sql = "

SELECT form_2.*,
       sum(amount) AS SUM
FROM form_2,
     form_1
WHERE booking_date BETWEEN from_booking_date AND to_booking_date
GROUP BY 1, 2

";

$sql  = "CREATE TABLE #TABLE_ID# ".$sql;

nuRunQuery("$sql");

Browse SQL:

Code: Select all

SELECT * FROM #TABLE_ID# 

Table structure I used for this demo:

TABLE `form_1`
`booking_date` date NOT NULL,
`amount` int(11) NOT NULL

with data:

INSERT INTO `form_1` (`booking_date`, `amount`) VALUES
('2020-01-01', 200),
('2020-02-15', 200),
('2020-03-10', 600),
('2020-04-09', 700);

TABLE `form_2`
`form_2_id` varchar(25) NOT NULL,
`from_booking_date` date NOT NULL,
`to_booking_date` date NOT NULL

with data:

INSERT INTO `form_2` (`form_2_id`, `from_booking_date`, `to_booking_date`) VALUES
('1', '2020-01-01', '2020-02-29'),
('2', '2020-03-01', '2020-04-30');

[faroinsua]

Yes, thank you, you answer so fast. I will try.

[faroinsua]

When I try to save the "Before Browse" code I get "...no access to nuapi.php and 404 error:

error.PNG

[kev1n]

This might help:

https://stackoverflow.com/questions/108 ... his-server