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 way to build your query so
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. @data had no closing “‘”, and
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 appreciated. Answer You can do conditional aggregation to
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 one container. Code: Answer you
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 2017”. Now, I want to
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? Answer
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