I have the following database-schema: I have the following example data: CREATE TABLE computermapping ( ComputerMappingID int NOT NULL, PrinterGUID char(36) NOT NULL, ComputerGUID char(36) NOT …
Tag: tsql
split data in a single row into multiple records
I need to split a comma separated field into multiple records based on ‘,’ Answer Youll want to use string_split like this:
Getting previous month data not working in sql server
I am trying to get the previous month data according to the datetime in the column dynamically for example I have data like this Now I want the records previous than Opendt. The problem is that I am getting the record after Opendt but I am getting empty when I am getting the record before 1 month or 2 month
Microsoft SQL Server – Convert column values to list for SELECT IN
I have this (3 int columns in one table) I would like to run such query with another someTable: where 1,2,3 are list of INTs converted to a list that I can use with SELECT * NOT IN statement Any suggestions how to achieve this without stored procedures in Micorosft SQL Server 2019 ? Answer If you want rows in
Order by 6 digit to 7 digit value then from lowest to highest
This code attempts to sort my values by number of digits, from 6 to 7, then from lowest to highest numerically. I have sets of data in my table and I always get: ID 992425 992199 1001111 I expect result ID 992199 992425 1001111 How to sort by number of digits, from 6 to 7, then ID from lowest to
Structure query builder generated SQL statement? Multiple joins to the same table?
Here’s the use case for this: Get me a list of heats where element #18 is between 95 and 100 AND element #2 is between 0.0 and 0.5. This works just fine (not sure it’s the best SQL/approach, but it works). The question/potential problem is that the number of element/range conditions is variable. T…
Break periods at the end of the month
SQL Server 2017 CREATE TABLE [TABLE_1] ( PLAN_NR decimal(28,6) NULL, START_DATE datetime NULL, ); INSERT INTO TABLE_1 (PLAN_NR, START_DATE) VALUES (1,’2020-05-01′), (2,’2020-08-01′); …
Merge lines over timespan in SCD2 table
I’m having the following table sourced from a SCD2 table. From this source table, I have selected only a few columns, which reults in several lines looking exactly similar. I want to remove the unnecessary lines, those that contain the same data, and have the ValidFrom column showing the first value and…
What would be the T-SQL equivalent of the Oracle JSON_EQUAL Condition?
I have a table with a column that contains a JSON string. I need to query that table to find rows that match a given input JSON string. I would like to ignore order and formatting. ‘{ “foo”: “bar” }’ should equal ‘{“foo”:”bar”}’ ‘{ …
Query optimization for multiple inner joins and sub-query
I need help regarding query optimization of the below query. Since there are duplicate joins in the main query and sub query, is there any way to remove those joins in the subquery? Answer Since, as you clarified, your sub-query is almost identical to your main query you might be able to use the window functi…