I have three flask-sqlalchemy-models. Books – unique entries by admin: class Book(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) book_name = db.Column(db.String(20)…
Query to get the Average of ONLY the first occurrences
I have 2 tables like these: Table Info Table A I need to take the first occurrence (occurrency_date ordered asc) of each Info and subtract from when the Info was created (created_date), after that take the average between them Something like this: I made many attempts but none were successful, how could I get…
Oracle SQL REGEXP to fetch the last numeric value in a string
I have the below string value in a column and I need to extract the last numeric value. I have used SUBSTR(ColumnA, -1, 1), but this is extracting only the last digit. ColumnA 12_23_AB245-F5 66_78_HJ378-G5567 55_16_GC761-B99898 Below is the expected result ColumnA 5 5567 99898 Answer Use REGEXP_SUBSTR here wi…
How to change a column based on the difference between dates within a group?
This is probably a simple problem but I am quite a noob in SQL. I am using Impala. So I have data like this: New_ID Date Old_ID 1 2020-11-14 12:41:21 0 1 2020-11-14 12:50:40 1 2 2020-10-14 15:22:00 1.5 2 2020-12-18 11:31:05 2 3 2020-11-14 12:42:25 3 Assuming that I group by New_ID, I need to check that the di…
Laravel Eloquent Equivalent for MYSQL DISTINCT query multiple columns
Is there an equivalent query for this in eloquent? Answer Try something like this:
Calculate working hours between exit date and 3 months before exit date bigquery sql
I’m trying to calculate the total working hours between two dates in bigquery sql: The dates being between MAX(date) and DATE_SUB(MAX(date), interval 3 month). In other words, I want to know the sum of working hours between the exit date and 3 months prior to the exit date. The current table is somethin…
Query nanoseconds from first entry
I have a data set keyed with a TestID and a datetime2 value where there are about 8000 entries per TestID, each is about 100 nanoseconds apart. I’d like to query all rows for a given data set (#40 in this partial example) and return the nanoseconds from the first record. Like this: I have a query to do …
How to perform Many to Many Relationship Filter Query
I want to perform a query in MySQL based on a filter. First of all, the two tables that will be used in the query are the following: Equipment: Field Type Id PK, Integer Name Varchar(50) Recipe: Field Type Id PK, Integer Name Varchar(50) The relationship between the two tables is Many-to-Many, so there is a p…
Why does the query not return any data?
I want to retract objects within the dates Here are all the objects: And here is the query: However, if I change the last date to ‘2020-10-10’ everything works Answer What is the data type of the column StartDateTime? If it’s datetime, your where clause needs to look like this
Calculate returns and insert into another table
I have a table MF_NAVs which holds daily NAVs of mutual funds. I want to calculate the absolute returns over 1 day, 7 day, 15 days, 1 month, 3 month, 6 month, 1 year, 3 year, 5 year & since …