I’m creating a PostgreSQL query and I’d like to have the missing records for each day filled in automatically. It occurred to me that I could generate a table that contained zero values and then join a source table to it. So I created this query, but the result still does not contain missing days,…
oracle sql – get difference between 2 dates (sysdate minus the datevalue of a other table)
I want to know the difference betweeen 2 dates. I need something like this: “sysdate minus the datevalue of a other table” Currently I have something like this, but it’s not working The 2nd select statement prints the date of the latest export. Example “27-JUL-21”. thanks for you…
Complicated group by – windows
Can you please help with the following grouping: Name can have states that can be repeated several times and it is essential in output to have all the state changes over time and to have the starting date of the state Here is the input data: Desired output Answer Assuming you want to find the start date per c…
How to get text among in blanket [] (Presto)
How to get the result ‘xxx’ from blanket [ ] in presto. I can’t find the proper regexp to fix my problem Could you help me out? Thank you Answer If you have strings like this: and want to extract substring between square brackets, use this:
How to view hourly results during the time specified in Postgres
I want to create a query that returns a list of timezones as a result when the following conditions are specified. How can I get this? Answer Use generate_series to create the records from the time interval, and jsonb_build_objebt with jsonb_agg to create your json document: Demo: db<>fiddle
sqlite3 INSERT INTO UNION SELECT
Table columns: pk is the auto-incrementing primary key An error occurs when I execute this SQL DB Error: 1 “table table1 has 4 columns but 3 values were supplied” Answer You should explicitly specify which columns in table1 your insert is targeting: When you omit the select list entirely, SQLite w…
Convert table data and reformat
I have table named Table id Level1 Level2 Level3 1 US CA 13000 2 FR PA 30000 3 US CA 24000 4 US LA 10000 5 UK LN 500 6 UK LN 600 7 FR PA 888 8 FR DF 1000 and I would like to convert it to the (First Conversion) below format which will sum Level2 and column
Rails – Relation passed to #or must be structurally compatible. Incompatible values: [:joins]
I’d like to merge two queries in my rails app. Each one is not very complex but I can’t merge them. user.rb package.rb I’m running into this error: Relation passed to #or must be structurally compatible. Incompatible values: [:joins] Answer I think what you’re looking for is a subselec…
Display records that are not present in a table and a given date range
Let’s say I have two tables – Student and Student Attendance tables. The Student table displays basic information about the student: Student# FirstName LastName 201710 John Smith 201711 John Doe 201712 Anna Sy 201713 Chris Dy While the Student Attendance table displays the TimeIn and TimeOut of th…
designing a follower/following schema between two tables in SQL?
suppose we have two tables: Brands Users i want to design a system where: Brands can follow Brands Brands can follow Users Users can follow Brands Users can follow Users this is what i have done so far: ed_id: user_id of the followed er_id: user_id of the follower note: the two tables are different but i have…