I have a below table which has multiple rows with same executionid and different status. How can I get the row which status is running, rows will be exclude if executionid associated with both running and completed status? Below image is the sample data : Expected result should be: Answer Using String_AGG() t…
Tag: sql
Oracle SQL: check amount of active users on given date (check closest date of grouped field)
Have a table given, holding the status history of a user: ID USERID MODIFIED STATUS 1 1 01.01.2020 inactive 2 1 01.07.2020 active 3 2 04.08.2020 active 4 2 04.06.2020 active 5 2 01.08.2020 inactive 6 2 01.10.2020 active 7 3 01.09.2020 inactive I want to provide a date, i.e. 01.07.2020, and understand how many…
Convert Raw SQL to Bookshelf/Knex
I’m trying to convert a simple raw SQL to use Bookshelf/Knex in JavaScript: Original SQL: select * from o where o.id = 1 or o.id = 2 and (o.is_a = true or o.is_b = true) and o.status = ‘good’; I’ve tried to rewrite it in multiple ways using .orWhere .andWhere but cannot get the same re…
How do I merge OUTER JOIN and UNION results?
I have two tables, and I want ALL the data from both. If the tables have a matching AssetID, then join them on one row. If not, then on separate rows. A full outer join sounds like the right approach but I have a problem in how to select the keys depending on which table it comes from. Goal: Produces
SQL switch latitude and longitude positions in the polygon string
I have a text that contains polygon information Example data: it there any way that could swap “lat” and “lng” in this string in SQL Server? Expect outcome: Any help appreciate! Answer What you have here is JSON. You can break out the info using OPENJSON, swap it round, and rebuild it …
How to delete only Top 100 rows which are older than 30 days from current date in C#? [closed]
Closed. This question needs debugging details. It is not currently accepting answers. Want to improve this question? Update the question so it’s on-topic for Stack Overflow. Closed 6 months ago. Improve this question When I execute following query , it is executed properly in SSMS but same query in code…
How to count rows by date for each of two values?
I have a large dataset in SQL Server that contains two date fields and a foreign key field (among others): I need a query that can count the number of rows for each day, but to count them for each distinct value in the type_id column. The closest I can wrap my brain around right now is returning the total
sql: Calculate the correlations and convert rows into columns
So my current table has more than 100 fields and I am trying to calculate the correlations between the input variables and output variable, and then convert all those columns into rows. For example, my current table looks like this: input_1 input_2 output 3 6 5 4 7 5 6 4 4 6 9 3 7 10 5 9 9
T-SQL stored procedure: update local variable inside IF/ELSE statement
I have an item table that has a foreign key constraint customer_id that references a customer table. The customer_id column is of type uniqueidentifier which needs to be generated on insert (there is no value we could use for this provided by the client). I am created a T-SQL (using SQL Server Express) stored…
Presto – Convert Table into Map
I would like to convert the below table into a map with multiple key/value pairs label_a label_b 1 2 3 4 5 6 into {‘label_a’ -> 1, ‘label_b’ -> 2} {‘label_a’ -> 3, ‘label_b’ -> 4} {‘label_a’ -> 5, ‘label_b’ -> 6} What wo…