I’m trying to find the shortest distance between the places and the user’s location and I have two table namely: Users_Location and Places User’s Table: |————|————|————| | …
Running query against all schemas in Postgres
Context We do schema-based multi-tenancy in our Postgres DB. Each and every schema is associated to a different tenant and will have the exact same structure except for one schema named public. To obtain the list of all the relevant schemas, we can use: Problem We need to periodically clean up a specific tabl…
Dynamic/Customized Week Number
Let us say there is a table which stores data for student’s attendance for classes. The student only need to attend each class once weekly. Hinging on the days_diff column, we decide which weeks the student should get credited for. As usual, each week has 7 days and the end of the week (in days) is divi…
SQL: is there a way to count the number of values from a table if there are none?
So i have 2 tables, one its “Movies”(movie,type,lenght) and one “Cast”(title_movie,id_actor) and i have to count in how many “SF” Movies every actor played. I tried this: and it only shows me the actors that has at least 1 SF movie, the ones with 0 movies are not displayed.…
Is there a way to group values getting only the ones that verifies certain condition?
I’m trying to write a query in Oracle SQL that aggregates values by some ids, where I have the following table as an input: ID SOME_DATE RANK_POSITION 301 20211201 1 301 20211202 2 301 20211203 3 649 20211201 1 649 20211202 2 649 20211206 3 649 20211208 4 649 20211211 5 758 20211212 1 758 20211222 2 And…
Concatenate 2 rows into one in SQL
I have a very simple data structure with just 3 tables: requests employees teams Basically, what I need is to concatenate 2 different SQL selects into one (so that the query could return just one row). If there’s an employee associated with a request, then return their name (title) and concatenate it wi…
How to get output parameter and also a table when executing a stored procedure
Scenario I’m working with SQL Server 2017 (not possible to change) I’m using Visual Studio 2019 in C# console and .NET Framework 4.5 (possible to change) I’m using ADO.NET because several years before we couldn’t use Entity Framework, as the system is made to work with a stored procedu…
SQL query based on column condition
I have a sql table in which below are the columns. I’m using sql server. Id, name, device and information on if device is active or not. Need to have a SQL query for below scenario: I want to display firstdevice, seconddevice columns below based on isActive column. If isACTIVE is 1 then, fill the device…
Make table out of different columns and assign values to add them up
I want this: where a win equals 3 points, a win in PV equals 2 points and a lose in PV equals to 1 point, to show like this: I just can’t figure out how to connect the values with SQL-Statements! Any help is appreciated 🙂 Answer You can do: Alternatively, you can filter out rows first and aggregate at…
Trying to extract data form SQL using PS script
I have been trying to get a PS script to work in extracting files (pdf, word, etc.) from an SQL Server database. I came across the PowerShell script below. The script runs and populates the destination folder but all files are 0 bytes and during the script execution. It throws the error: “Exporting Obje…