9 Complex SQL Queries | Answering Your SQL Queries.

 In this post, we are going to be answering 9 Complex SQL Queries which were asked by you.

1) How to trim multiple spaces into a single space?

The trick here is to use the Triple Replace Technique.

declare @str varchar(100);

Set @str = 'This is best      SQL     Learning    Video';

-- Step 1 - Replace a single space with a combination of a space and a pipe. You can choose any other delimiter instead of a pipe and other than a space.

Select Replace(@str,' ', ' |');

-- Step 2 - Replace the reverse combination ( a pipe and a space) by an empty string.

Select Replace(Replace(@str,' ', ' |'),'| ','')

-- Step 3 - Replace the original combination (space and a pipe) by the desired delimiter (single space in this case).

Select Replace(Replace(Replace(@str,' ', ' |'),'| ',''),' |','-')

2 - How to find string values containing special characters like Latin characters?

Hint - Use Regex. 1 - Define the list of valid characters in the Like clause. 2  - Use the ^ (not)  comparison to filter out any values not matching the list of valid characters.

In the below example, we have defined the list of valid values as A-Z, a-z and 0-9.

declare @address varchar(100) = 'ABC ß';

Select 1 

WHERE @address like '%[^A-Za-z0-9]%'

3 - How to dynamically select columns in a SELECT query?

Hint - Define a variable containing the desire columns. Construct a dynamic SQL query using the variable to fetch the column names.

declare @col varchar(100) = 'A, B, C'

declare @sql varchar(250) = 'Select ' + @col + ' from Employees'

--print(@sql)

exec(@sql)

4 - How to select customers who have ordered every single day in the last month?

We are going to use the below sample data for this example.

 Create Table Orders 

 (OrderDate date ,

 CustomerID int)

 INSERT into Orders VALUES 

 ('2023-05-01', 1), ('2023-05-02', 1), ('2023-05-03', 1), ('2023-05-04', 1), ('2023-05-05', 1),

 ('2023-05-01', 2), ('2023-05-03', 2), ('2023-05-01', 3), ('2023-05-02', 3), ('2023-05-03', 3)

Hint - 1 - Find the total number of days in the last month. In the below query, we subtract 1 month from the current date and then use the EOMONTH function to get the last day of the previous month. The last day decides the number of days in the month.

 Select EOMONTH(DATEADD(m,-1,Getdate()))

Hint - 2  - Calculate the number of distinct Order Dates for each customer in the previous month.

Hint - 3 - If the above two counts match, then it means that the Customer placed at least one order each day of the previous month.

Select CustomerID 

  From Orders

  Group by CustomerID

  Having Count(distinct Orderdate) = DAY(EOMONTH(DATEADD(m,-1,Getdate())))

5 - How to find Top 5 and Bottom 5 Employees based on Salary in a single SQL Query?

Hint - Use ROW_NUMBER function. Sort ascending to calculate Bottom 5 and Sort descending to calculate Top 5.

With EmpSAL AS 

(Select * ,

ROW_NUMBER() OVER (Order By Salary ) AS Bottom5 ,

ROW_NUMBER() OVER (Order By Salary Desc) AS Top5 

FROM Employees )

Select *, 

CASE WHEN Bottom5 <= 5 THEN 'Bottom' 

WHEN Top5 <= 5 Then 'Top'

END as TopORBottom

FROM EmpSAL 

WHERE Bottom5 <= 5 OR Top5 <= 5

6 - How to check if a column is masked or not?

Hint -  Check the system table - sys.masked_columns.

--Select * from Membership;

--Select * from sys.masked_columns;

SELECT c.name as ColumnName, tbl.name as table_name, c.is_masked, c.masking_function

FROM sys.masked_columns AS c

JOIN sys.tables AS tbl

    ON c.[object_id] = tbl.[object_id]

WHERE tbl.name = 'Membership' and is_masked = 1;

7 - How to remove NULL values from a LEFT JOIN?

Hint - If you need to filter out rows containing NULL values, then use IS NOT NULL in WHERE clause.

Select Dept.DeptID

FROM Dept LEFT Join Employees Emp 

ON Dept.DeptID = Emp.DeptID 

WHERE EmployeeID IS  NOT NULL

If you need to select the NULL record, but replace its value, use a function like COALESCE to replace the NULL value from the LEFT Join.

Select Dept.DeptID, COALESCE(Emp.EmployeeID ,0)

FROM Dept LEFT Join Employees Emp 

ON Dept.DeptID = Emp.DeptID 

8 - How to find monthly sales from first Tuesday of current month to first Thursday of next month?

Hint - 1 - Find first day of current month and first day of next month

declare @fircurrmonth date = DATEADD(dd,1,EOMONTH(getdate(), -1));

declare @firnextmonth date = DATEADD(dd,1,EOMONTH(getdate()));

Hint - 2 - Find first Tuesday / Thursday - First subtract the weekday number of the first of the month from 7.

7 - DATEPART(dw,@fircurrmonth)

- Then add the desired weekday number. For ex - Tuesday is weekday number 3. So add 3 to the result. For Thursday, add 5.

DATEADD(dd, ((7 - DATEPART(dw,@fircurrmonth) + 3)), @fircurrmonth);

DATEADD(dd, ((7 - DATEPART(dw, @firnextmonth) + 5)), @firnextmonth)

- To make sure, we never add more than 6 days(so that we always move forward only within the 7 days), do a %7.

set @startdate = DATEADD(dd, ((7 - DATEPART(dw,@fircurrmonth) + 3)%7), @fircurrmonth);

set @enddate = DATEADD(dd, ((7 - DATEPART(dw, @firnextmonth) + 5)%7), @firnextmonth)

-- Filter on this range of dates derived above. Here is the final SQL Query.

declare @startdate date;

declare @enddate date;

declare @fircurrmonth date = DATEADD(dd,1,EOMONTH(getdate(), -1));

declare @firnextmonth date = DATEADD(dd,1,EOMONTH(getdate()));

set @startdate = DATEADD(dd, ((7 - DATEPART(dw,@fircurrmonth) + 3)%7), @fircurrmonth);

set @enddate = DATEADD(dd, ((7 - DATEPART(dw, @firnextmonth) + 5)%7), @firnextmonth)

print(@startdate);

print(@enddate)

Select * 

FROM (VALUES ('2023-06-01'),('2023-07-08'),('2023-06-10'),('2023-06-21'),('2023-07-06')) as t1(col) 

WHERE col >= @startdate and col <= @enddate

9 - How to dynamically convert row data into dynamic number of columns and split the remaining data into subsequent rows?

Hint - Dynamic Pivot.

Hint - 1 - Define the Column name sin a variable.

Hint - 2 - Create a sequence for the rows. The sequence will cycle over the number of Columns(In our example there are 4 columns). For ex 

A    1

B    2

C    3

D    4

E    1

F    2

G    3

H    4

The below piece of the code creates this sequence. 

ColumnName =  ''C'' + CAST(1 + ( (row_number() over (order by (select null)) - 1) % 4)) AS VARCHAR)

Please watch the video tutorial in the link above to understand this logic.

Hint - 3 - Create an ID sequence to identify the 4 records to be grouped as a single row.

The following piece of code creates this sequence. This is an optional step as the final desired output does not use this sequence ID. 

ColumnName1 =CAST(1 + ( (row_number() over (order by (select null)) - 1) / RIGHT(''' + @collist + ''',1)) AS VARCHAR)

Hint - 4 - Create a PIVOT Query. Replace the hard-coded number of columns(4) with the variables.

Hint - 5 - Create a dynamic SQL Pivot query to obtain the final output.

Here is the final SQL Query.

declare @sql varchar(max);

declare @collist varchar(20) = 'C1,C2,C3,C4';

set @sql = 'SELECT * 

FROM (   SELECT 1 as ID, col, ColumnName =  ''C'' + CAST(1 + ( (row_number() over (order by (select null)) - 1) % RIGHT(''' + @collist + ''',1)) AS VARCHAR)

    ,ColumnName1 =CAST(1 + ( (row_number() over (order by (select null)) - 1) / RIGHT(''' + @collist + ''',1)) AS VARCHAR)

FROM t1 ) As Source_Table 

pivot (MAX(col) FOR ColumnName IN (' + @collist + ' ) ) AS p' 

--print(@sql)

exec(@sql)