Thanks for helping. I have my table CONVERSATIONS structured in columns like this : [ ID , JSON_CONTENT ] In the column ID i have a simple id in Varchar In the column JSON_CONTENT i something like this : I would like to query and get the id and the last element of exchanges : [ ID , LAST_ELT_IN_EXCHANGE_IN_JS…
Tag: tsql
Dynamic TSQL Pivot without aggregate function
I have a table like this (‘ExternalPersonRelationTable’) PersonId SubjectCode 4187 3 4187 278 4429 3 4429 4 4463 99 4464 174 4464 175 I want to rotate the data so that every person in the table gets a column and a TRUE/FALSE value for each subject code, i.e. a table like this: Code 4187 4429 4463 …
Formatting the surrogate key based on other columns
I have a table in SQL Server I want my registerNumber to be of type batch+branch+id eg a row in student id:1, batch:17, branch:BIT then the registerNumber should be 17BIT1 I tried to do this by using a default value in create table but it does not allow me to reference the columns of the same table edit thank…
Query to select the manager and total salary of employees under the manager
I need to write a query to select the manager and total salary of employees under the manager. The salary of employees should roll up to Manager based on hierarchy. Mgrid references to empid in the same table. Table : Employee Output should be ManagerName and Salary In the above example : M2 has M3, M4, M6 di…
Merge several rows into one if they have a gap less than a 5 seconds
I’m trying to find a SQL query that lets me to merge some rows into one from a table that have a gap less than 5 seconds. For example, I have a table like the following: So, I want to have a result like this: For John there are two rows that have a gap less than 5 seconds, so
Merge not inserting new values
I’m trying to use MERGE to insert new values to a table only if they don’t already exists in the same table. This is the query I am using: MERGE [dbo].[TARGET_TABLE] AS Target USING (SELECT [NAME] …
T-SQL: How to return a result set that excludes values based on an Exclusion table
I’m using SQL Server 2016 and I want to return a result set which has been filtered based on an “Exclusion” table. The rows in exclusion table could contain a [Make] code, a [Color] code or a combination of [Make] & [Color] codes. The data table has approx 450K rows. NULL values in the a…
Performing subtraction in SQL/finding the balance
SQL table here: http://sqlfiddle.com/#!9/abe1da/9 Current Table: Year Month Type Accounts Amount 2021 1 Actual abc 20 2021 1 Actual def 30 2021 1 Actual ghi 40 2021 1 Actual X 7 2021 1 Actual Y 3 2021 1 Actual final 105 Expected Year Month Type Accounts Amount 2021 1 Actual abc 20 2021 1 Actual def 30 2021 1 …
Calculating a plug/balance in SQL
My current table has a few accounts which always have minimal values (example, X & Y for actual and AA, BB, CC & DD for plan) in current table. Accounts with minimal values can extend beyond these accounts. I need to categorize such accounts into proxy account called “balance” which would …
Sum the results of a DateDiff [closed]
I have a table with 3 columns: SessionRole (varchar(100)), Start Date (datetime2(7)) and End Date (datetime2(7)). I need to calculate a value that is the sum of End Time – Start Time for all sessions …