Given a date range, I’d like to return all of the Saturdays and Sundays that fall within that range, with these conditions: Include Saturdays only if their ordinal position is either the 1st or 3rd Saturday within the month they fall (not within the entire range). Include all Sundays, along with the ord…
Tag: sql
Order By Month Name – Postgresql
I followed this post Order by using month name in PostgreSQL but not success! I have a query (its working) and I just need to order the results by mont name. This is thr query I am using: I am trying: If i use order by mes_2021 Answer One trick is to use a window function on the date: This,
savepoint in sql
Can I rollback to a savepoint ( which was done before a commit ) in SQL ? Answer You can use the ROLLBACK TO SAVEPOINT statement only in the transaction. In MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/savepoint.html) you find: All savepoints of the current transaction are deleted if you execu…
Query error: Column name ICUSTAY_ID is ambiguous. Using multiple subqueries in BigQuery
Hi, I receive the following query error “Query error: Column name ICUSTAY_ID is ambiguous” referred to the third last line of code (see the following code). Please can you help me? Thank you so much! I am an SQL beginner.. Answer Both t1 and t2 have a column called ICUSTAY_ID. When you join them t…
Check if a chat between 2 people exists
I have a chat system that handles group chats. A conversation can have many participants and the only distinction between group and non group chats is a non group chat will only have 2 participants. I want a query to check if a non group chat exists between 2 users. Conversations table Participants table To c…
Postgresql: How do I use dynamic values when searching jsonb array of objects?
I am currently trying to build a query for finding specfic object within a jsonb array. I have the following query which works fine if I used a hard coded string for the “game” value e.g. However, if I use a dynamic value like I currently do for username, I get invalid json syntax error. e.g. How …
How to get the closing time of day in timing schedule that spans multiple days
Assume I have the following table called Timing: Obviously each row represents a shift in a specific day. A day can have non-overlapping multiple shifts. If a shift spans the next day it will be splitted at midnight, and the second half would have a parent id of the first half (as you can see in row 24 and 31…
How do I add a column that counts unique instances of another columns data
I am an end user that does not work with SQL everyday. However I am sometimes asked to add modify existing queries to provide new data. Currently I have a query that calculates (counts) the occurrences of a column labeled (Qid) and displays the top 5 based on count. It was necessary to use a select statement …
SQL to create data as table when there is no relation in SQL
I have following tables Table 1 i have rates Table 2 i have Dates Table 3 i have data which show price of rates which are applicable between start and end i want to create table 4 with SQL query in which i have every rate (from table 1) + every date (from table 2) + if that date exist
Reading and Writing UTC to TIMESTAMP in Postgresql
I have a Java application that inserts data into a database using prepared statements. In the preparedStamement date is set in UTC format. I want to be sure that when read and write operations execute on the table, the response should ALWAYS be in UTC format. At the below query, when the data is read it will …