Suppose I have the following table where there are two sets of observation dates (2015-01-01, 2016-01-01) in the first column. For each observation date, there are associated item_date and item_value. observation_date item_date item_value 2015-01-01 2012-12-31 0 2015-01-01 2013-03-31 1 2015-01-01 2013-06-30 2 2015-01-01 2013-09-30 3 2015-01-01 2013-12-31 4 2015-01-01 2014-03-31 5 2015-01-01 2014-06-30 6 2015-01-01 2014-09-30 7 2016-01-01 2013-09-30 8
Tag: sqlite
How to retrieve all grand parents at once
I have two simple tables in SQLITE3 (family bonds): “persons” (id, fname, lname) “relationships” (parent/child) I would like to get each grand children along with all their grand parents (from 1 to 4 of them depending on the grand child) so that 1 row result = Thanks to Caius Jard, I’ve been able to get each child and its grand
Find two values of one Column based on value of another column per ID
I have a sqlite db with one table that called Loan. This table with sample data is here: Loan Table at sqlfiddle.com This table contains below Columns: Now, I need a query to show desired result, contain [empid],[Codepayid],[Lval-1],[Lval-2],[Sum(Lint)],[Lrmn-1],[Lrmn-2], With this Conditions: For example: Result: EmpID CodepayID Lval1 Lval2 Sum(Lint) Lrmn1 Lrmn2 12450400 649 405480 405485 270320 337900 202740 Answer Use
Exclude all from attributes where employee table does not have connection with in the pivot table
I need to exclude all attributes that have no connection with employee1 in Pivot table. in this case attribute3. What is the SQL query for that? Answer Use not exists:
fetchall() not working even with values in database
I am working in python with sqlite3 and I am trying to create a database. I created a table: c.execute(“CREATE TABLE persoane (nume text, varsta integer, salariu integer)”) Then I added the values into the table: c.execute(“INSERT INTO persoane VALUES(‘David’, 16, 1000)”) When I try to get the values out of the database with fetchall() I get an empty list
Convert Date format in SQLite Database
I have a SQLite Database and I mistakenly loaded a lot of data with mismatching date formats. There are dates formatted as: MM/DD/YYYY and dates formatted as: DD-Month Abbr. – YY. I would really like them all to be YYYYMMDD. Is there a way to reformat all of the dates to match the preferred format? Thanks! Answer If you have
Finding date-streak in SQL
I got my sqlite table with entries having a DateTime field. I want to check if there are entries with n consecutive days. Like a streak. Now I could just query all entries ordered by the date-field and iterate over them and check by myself. I am just wondering if there is some more efficient way to do this. Like
What’s the scope of table aliases?
This question is in the context of Sqlite but I wonder in general what’s the rule for aliases. Suppose I have the following query: The question is, will the two t1’s and t2’s clash? in my experience with multiple database vendors they won’t clash, but, could it confuse the optimizer to the point of producing a buggy result? I have
SQL Filter by dateTime
day is a integer variable. If I set the day value to 1, database is filtering for 24 hours. But if I set the day value to 1, I only want to see the logs of the new day. How can I do that? Thank you for your help. Answer
SQLite update data from one table to another
I’m trying to insert some data from one SQLite database to another. I have 2 tables called “members” and “attendance.” What’s supposed to happen is, everytime a new member is added into the member table (or removed), the name and ID of the specific member should also be added to the attendance table. Picture below shows what I’m trying to