I have an application where the user can retrieve a list. The user is allowed to add certain filters. For example: Articles: 123, 456, 789 CustomerGroups: 1, 2, 3, 4, 5 Customers: null ArticleGroups: null … When a filter is empty (or null), the query must ignore that filter. What is the most performant …
Tag: tsql
SQL Server – Delete rows from one side of a JOIN
I have a query that identifies orphaned rows in one table after joining it to another. The query I have for selecting the rows that need to be deleted works fine, and properly selects the rows that …
How to create all possible combinations with splitted items in SQL?
I have data as in below delimited format expected output – is the combination with each element and the element itself I am trying by splitting the data into different items as below any idea how we can create combination with splitted items? Thanks! Answer You have a couple of mistakes in your SQL. @da…
Convert different dates in date column to rows in SQL Server
I want to display different dates as columns from LOGDATE column. Here are the details My query: This query returns this result: Expected output format: I have multiple types of SERVICE_TYPE and SERVICE_NAME, not just the three sown in the sample. How can I get my expected output? Any help would be appreciate…
How to Fix Error Msg 41337, Level 16, State 100 in Create Memory Optimize table in SQL Server
I want to use a memory optimize table and I got below error – how can I fix it? Any idea will be helpful. Msg 41337, Level 16, State 100, Line 1 Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least o…
T-SQL – Count unique characters in a variable
Goal: To count # of distinct characters in a variable the fastest way possible. DECLARE @String1 NVARCHAR(4000) = N’1A^’ ; –> output = 3 DECLARE @String2 NVARCHAR(4000) = N’11’ ; –> output = …
Convert character string into this specific date format?
I am using SQL Server 2014 and I have a table (t1) which contain a column (ReviewDate) in the nvarchar format. An example of a row of this column is given below: I need to extract the “date” component from this character string. To do this, my T-SQL is as follows: This gives me “Oct 2017R…
SQL function delete rows with a specific date and a specific value on another column
I have a question, mostly about syntax. I have a sql table and I need to delete some rows based on the date on a “Date” column as long as they contain a string in a “Client” column My issue is the …
SQL Server Lag and Lead
I’m using SQL Server 2014. I have a table: I have some data: If I perform a LAG and LEAD using the below for one Job, I get the desired result: Result: Notice the NULL for the first FromActionCode and LastToCode, which is correct. However, If I look at all records, it messes it up: What am I missing? An…
Week number and its Sunday
I have a simple question. If I have a week number, how I assigned a Sunday to it? (In t-sql) For example. I have number 15 as input (which is week from 8.4. to 14.4., from monday to sunday), and I will need it to be shown as 14.4. as output. The things is, that I have a column of