I wish to fetch all users from “members” table but also check if the member_id from members table and user_id exist in”login” table and then see if column “activity” (current_timestamp) is less than 3600 seconds in login table than order those users on top rest users if don…
How to implement OPENJSON Having JSON code as a text inside a column. How do i use OPENJSON on the column of a table in AZURE SQL Dataware House?
Eg: RAW DATA Want to apply OPENJSON on Discounts and have below OUTPUT: Answer I’ve updated my answer, please add ; after the insert statement. Create table and insert two rows: Then we can use following sql to query the data. 3.The result is as follows:
PostgreSQL equivalent of Pandas outer merge
I am trying to do in Postgres the equivalent of Pandas outer merge, in order to outer merge two tables. Table df_1 contains these data: Table df_2 contains these data: So Table df_1 has one extra column (random_id) than df_2. Also, job_id 1711418 and worker_id 45430 exist in both df_1 and df_2. If I use the &…
Is is possible to create a view based on a table in a different server?
My client have an azure service app MyServiceApp, its database called MyCloudServer (its data is taken from a legacy MyPremiseServer). The MyPremiseServer is a server located on my client’s site, because the cloud’s service app MyServiceApp can’t access it, They created the MyCloudServer wit…
SQL procedure group by parameter value
I want to use group by functionality with dynamic value. Ex: When I code like this I get the error == > Each GROUP BY expression must contain at least one column that is not an outer reference. Any workaround for this? Answer You need to be very careful with this type of code as it is prone to SQL
SQL: How do I find records that are within X days of one another?
Suppose I have a database called employees which looks like this: I want to return the employees that were hired within 2 days of one another. Here’s the closest that I’ve been able to get: The code above gives me the employees that were hired within 2 days of on another but only for their respect…
Count values between double quotes and brackets
How can I group by and count the values separated by double quotes between the brackets? I have 400K rows, so I’m also concerned about performance. Desired output: Answer Do you mean something like this? (The with clause is only for testing – remove it, and use your actual table and column names i…
SQL date scenario for getting previous month’s data to-date on the 1st of month, then drop last month
I’m trying to recreate an existing View in Snowflake (originally in SQL Server). I need to get last month’s data which is easy enough, but here’s the scenario: On 3-1-21, it should retrieve 2-1-21 to 3-1-21, but then starting on 3-2-21, it should only show 3-1-21 to-date, as our month end re…
(Error) ORA-02270: no matching unique or primary key for this column-list
I have the code below and I’m running it step by step: CREATE TABLE registration ( constraint pkks primary key (cod_class, number_rgm), cod_class NUMBER(8), number_rgm …
SQL Snowflake Column Condition Check
Basically, I am trying to write a query against a table such like the following. Here we have Plans of either Free, Plus or Premium (in the example below I only show free and plus plans). Is it possible to return records where an ID that was original a ‘Free’ plan has changed to either a ‘pl…