Count/Sum a field based on dates in one table from start/end dates in another

English
#1

Some background…

I’ve used FileMakerPro for a goodly number of years and a couple of databases with historical (but ongoing) data I want to track. My MacBookPro was getting quite old and cranky, and I recently upgraded to a new model. Unfortunately, my old FileMakerPro won’t run on the new laptop.

An updated version, even the “Individual use” version is, um, pricey. So I wondered if I could re-implement my old databases in LibreOffice Base. LibreOffice is my “production” choice for word processing and spreadsheets.

FileMakerPro is quite a powerful tool and does a lot of stuff for you. I’m sure you can do a lot of the same stuff in Base, but I’m struggling. I’ve worked my way through a lot of things, but I’ve run up against something that I haven’t figured a way around.

My Setup
My file tracks historical production of our solar panels. There are two tables. One is simple, just a date and the number of kWH produced by the panels that day. So always one row per day. The other comes from our monthly bill. Each row has two dates representing the beginning and end dates (bills aren’t generated on the same date each month, so a “month” is the dates between bills), then the total kWH sent to the grid (while production exceeds current usage) and total from the grid (when it’s dark or usage is really high).

From those two monthly totals and the total generated during the covered period I compute the actual usage for the month, and from that the average daily usage.

And Here’s Where I’m Stuck
I don’t know how much it’s my very rusty SQL and how much is not understanding how to do things in Base, but I haven’t figured out how to do the “total generation in a month” calculation.

And of course Base just unhelpfully says “There’s an error in your query.”

Here’s my currently query. And I know, this is incomplete, it will sum all production after the start of the month. At this point I’m just trying to get something that works at all.

SELECT SUM( “ProdTab”.“Production” ) AS “TotProd” FROM “ProdTab” WHERE “ProdTab”.“Date” > “Bills”.“StartDate”;

I’m putting the above in the “Field” row of the query and I get a response that the field isn’t recognized. When I define a simpler calculation in the query (no select, just a calculation on another field), that works OK.

What am I missing?

#2

The second table is missing in SQL-Code. If there is only one value something like this could work:

SELECT SUM( "ProdTab"."Production" ) AS "TotProd" FROM "ProdTab" 
WHERE "ProdTab"."Date" > SELECT MAX("StartDate") FROM "Bills")

If there are many values in “Bills” there should be a connection between the table “Bills” and table “ProdTab”:

SELECT SUM( "ProdTab"."Production" ) AS "TotProd" FROM "ProdTab", "Bills" 
WHERE "ProdTab"."Date" > "Bills"."StartDate" AND ...
#3

for monthly production:

SELECT "StartDate", "EndDate", SUM( "Production" ) AS "MonthlyProd" FROM "ProdTab", "Bills" WHERE "Date" BETWEEN "StartDate" AND "EndDate" GROUP BY "StartDate", "EndDate

for total sum per month

SELECT "EndDate", SUM( "Production" ) AS "TotProd" FROM "ProdTab", "Bills" WHERE "Date" <= "EndDate" GROUP BY "EndDate"
#4

Basic query should be

SELECT SUM("Production") AS "TotProd" FROM "ProdTab"
WHERE "Date" BETWEEN :bsd AND :bed

where bsd/bed stands for “bill start date”/“end date” parameters.
As these must match bills, I think you can use the “Standard Base” design of
Main Form > Sub Form.
 

  1. Create a Form and put on it the Bills Table.
  2. Via Forms Navigator insert on it a Subform.
    Source = the above sum query.
  3. Drag into the Subform a TextBox
    Data source = TotProd (the only of course) field.
  4. Link the parameters to the controls start + end of Main Form.
    See @Villeroy’s instruction to me in

About Filter Table Method

 
This way you can select a bill and subform should display the sum.
I THINK :thinking: this would work. Don’t have how to check it myself for now.

#5

Update:
Thanks for all of the suggestions, but I’m still stuck, even after looking at some generic SQL examples that do similar things. And I’m not sure everyone completely understood my use case. So here’s a simplified example: Assume two tables, Dates and Production.

  • DatesTbl has two fields, StartDate and EndDate which define an arbitrary date range.
  • DataTbl has two fields Date and kWH.

In reality there will be other fields, but stick with me. And note that StartDate, EndDate and Date are unique within their own tables. Also note that it’s impractical to put some kind of identifier in DataTbl to match rows between the two tables.

The task is to generate a new table with three fields: StartDate, EndDate, and TotalkWH, where that last field is the total of the kWH fields from DataTbl that have Date in the given range.

This is what I came up with that seems like it should work:

SELECT "DatesTbl"."StartDate", "DatesTbl"."EndDate" FROM "DatesTbl",
(
  SELECT SUM( "DataTbl"."kWH" ) AS "TotalkWH" FROM "DataTbl" WHERE "DataTbl"."Date" = "DatesTbl"."StartDate")
)

All Base will tell me is “Syntax error in SQL statement.”

My SQL chops are at least six years idle, and I was only an occasional user then. And Base has its own quirks, like wanting quotes around everything, which is throwing me. (And since this is a simplified version of what I was actually doing I might have introduced a typo or two here.)

So maybe someone can point out how I’m murdering SQL (or Base’s version of SQL) here?

#6

You want a View.
For aggregate (sum) you need a BETWEEN.
Try this way with a small sample of records – create 2 small auxiliary tables to avoid an eventual cartesian product – and let’s see what happens :thinking:

SELECT "DatesTbl"."StartDate", "DatesTbl"."EndDate", 
(
  SELECT SUM( "DataTbl"."kWH" ) FROM "DataTbl" WHERE "DataTbl"."Date" BETWEEN "DatesTbl"."StartDate" AND "DatesTbl"."EndDate"
) AS "TotalkWh"
FROM "DatesTbl"

By the way: how was this query in FileMakerPro?

#7

Might be something like this:

SELECT SUM( "DataTbl"."kWH" ) AS "TotalkWH", 
"DatesTbl"."StartDate", "DatesTbl"."EndDate" 
FROM "DatesTbl", "DataTbl"
WHERE "DataTbl"."Date" BETWEEN "DatesTbl"."StartDate" AND "DatesTbl"."EndDate"
GROUP BY "DatesTbl"."StartDate", "DatesTbl"."EndDate"
#8

One general hint: There is no “Base’s version of” SQL. Base will send your SQL (ok, sometimes a bit changed) to the database. One can completly switch off interpretaion by Base with the rightmost icon in SQL-view. It then depends on the databases, if they understand the syntax.

#9

Subselect does work, but I think @RobertG’s should be much more SQL correct (= efficient).
 
DataTbl
DatesTbl

Query
Query787×270 26.2 KB

#10

As a side exercise with the Firebird Embedded, simulating MS Access DSum…
 

CREATE FUNCTION PKHW(bsd DATE, bed DATE) RETURNS INTEGER
AS
DECLARE VARIABLE p INTEGER;
BEGIN
SELECT SUM(KWH) FROM PRODUCTION
WHERE DIA BETWEEN :bsd AND :bed
INTO :p;
RETURN :p;
END;

Yes: a typo :grimacing:
 
SumFunction

#11

FileMakerPro generally doesn’t expose SQL to users. Rather than creating views, you can just define new fields in a table that do calculations on other fields, including aggregate functions (sum, max, etc) across records in other tables that match via defined joins. (You can have different joins between the same two tables.)

#12

:astonished:
I think @RobertG’s = the right solution.

#13

OK, this turned out to be much simpler than I thought. Rather than do everything in one query I realized I could use one query to create the totals I wanted, then pull the result of that query into the main table with the other information for the monthly bill.

I had a more human-readable query for the first example, only to find that when you type a query into Base and saves it it rewrites your query in a form it likes, and then you can edit it in the design view.

So first query to generate an EndDate->Total production mapping I ended up with:

SELECT “TabBills”.“EndDate” AS “BillDate”, SUM( “TabProd”.“kWHProd” ) AS “kWHProd”
FROM “TabBills”, “TabProd”
WHERE “TabProd”.“Date” BETWEEN “TabBills”.“StartDate” AND “TabBills”.“EndDate”
GROUP BY “TabBills”.“EndDate”, “TabBills”.“StartDate”

And then I can use the design view to pull in that and all of the fields from the main table I want.

My suspicion is that in my earlier attempts doing everything in one query, I wasn’t including all of the fields in the “GROUP BY.” Since the date fields are unique that really shouldn’t be necessary, but I guess SQL doesn’t know that.

Anyway, thanks everyone for the responses. None of the suggestions quite worked, but I think that was partly because they were addressing the simple case I used for my example, and I didn’t understand how to expand that to my larger case.

Hopefully things will get simpler.

#14

Of course answers can only be based on what you informed :grimacing:
And as long as i can understad you: yes – create views to decompose the problem.

#15

Guilty. I was trying to simplify my slightly more complex case down to a very simple example. And I apparently didn’t describe even the simple case well. And then when I tried to extrapolate to my more complex case it didn’t work.

That and learning to navigate around how Base tables and queries work together.

I did look at a lot of videos and documents, but found nothing that clued me on how to break this problem down. And my SQL knowledge is really quite stale. FileMakerPro hides SQL-like concepts down under a more hand-holding interface.

I’m bumbling along, learning things. I still have a lot to learn.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.