I have a dataset with date information in MsSQL. The type of this data is registered as datetime. For example I have a date value like ‘2022-12-12 06:59:00’. Here, if the time is before 07 am, I want it to write ‘2022/12/11’ as the date. If the date value ‘2022-12-12 07:01:00R…
Tag: tsql
SQL Is it possible to incorporate a SELECT with a REPLACE?
I’m using MS SQL Server 2019 I have a string in a table (tblJobCosts) that has its own ID like this: How do I update the value stored in the brackets based on the value from another table? The end result would look like this: I thought I could incorporate a SELECT with a REPLACE but this does not seem
Iterate through SQL result set adding subquery results to result set
I have a table [Robots] containing Robot IDs, and a [TestResults] table which has a FK to [Robots] of [RobotId] What I am aiming to get is a result set of each robotID in the [Robots] table and for each ID a total of all entries in the [TestResults] of Status ‘Active’, ‘Failed’ and tot…
Check in T-SQL whether certain value sets exist in a related table
I have following tables: User – userId, userName, … Settings – settingId, userId, settingKey, settingValue for example for userId = 123, I might have: Then for example how can I query for all users that have settingValue of “0” corresponding to settingKey of “allowClient…
How to split a string which contains delimiter into multiple rows using OPENJSON function
I am trying to split the below string using OPENJSON function in SQL Server 2019: But the result is returned as: I want to display it as this: Thanks Answer This is where my thinking brings me. Note: Unexpected results of the “‘s are NOT balanced. Example or dbFiddle Results
Calculate date difference between dates based on a specific condition
I have a table History with the columns date, person and status and I need to know what is the total amount of time spent since it started until it reaches the finished status ( Finished status can occur multiples times). I need to get the datediff from the first time it’s created until the first time i…
What can I use instead of “Set Identity Insert On”?
I have stored procedure in SQL Server. In this procedure, I delete duplicate records and insert one unique records to table. When I insert new unique record I am using below script. Everthing is okey with this script but in the production SET IDENTITY_INSERT command needs to ALTER permission. Giving this perm…
Difference between dm_exec_sql_text vs dm_exec_input_buffer
As per Microsoft docs, dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle while dm_exec_input_buffer returns information about statements submitted to an instance of SQL Server. I used DBCC INPUTBUFFER for getting the last executed SQL. But it seems both dm_exec_…
SQL query to retrieve column name among 3 columns in table whose values are mutually excluse ie.one among them will be ‘Y’ whose col name to be found
I have a query in which multiple joins and select from various tables are used. One table structure is as follows: Only one column among 3 will have the value ‘Y’. Table employee : id valid invalid non-scope 001 Y null null 002 null Y null 003 null null Y The o/p of the select statement for the mu…
How to divide SQL results into equal parts using where clause and/or case statement?
Context: I am preparing this dataset to be used for an SSRS report, my goal is to split the list into about multiple parts (equally) for employees to receive. It will be used as a “task list” of sorts. I have tried two different ways, both of which are causing issues. Attempt #1 – Error is &…