84 docs tagged with "TSQL"

SQL Server: Calculate Running Total or Cumulative Sum with Subquery

SQL Server: Transform date and time values into human-readable formats such as yyyy-MM-dd, yyyyMMddHHmmss, perfect for enhancing the user experience on your website or application

SQL Server: The AT TIME ZONE clause in T-SQL enables the conversion of UTC to the Local time zone while seamlessly accounting for Daylight Saving Time changes.

Discover various approaches in SQL Server to detect overlapping date ranges within tables using custom function and stored procedure. Enhance your database skills with practical insights on handling overlapping time periods.

Window Function is the Easiest and Fastest Way to Calculate Running Total in SQL Server

SQL Server: Calculate Running Total or Cumulative Sum with Self Join

LeetCode SQL Solution of Problem 1045. Customers Who Bought All Products. GROUP BY + HAVING Solution.

LeetCode SQL Solution of Problem 1070. Product Sales Analysis III. CTE + GROUP BY Solution.

LeetCode SQL Solution of Problem 1077. Project Employees III. Use RANK() Window Function to rank most experience employees in each project as 1

LeetCode SQL Solution of Problem 1098. Unpopular Books. GROUP BY + HAVING + LEFT JOIN Solution.

LeetCode SQL Solution of Problem 1107. New Users Daily Count. Solved Using Subquery and Count DISTINCT!

LeetCode SQL Solution of Problem 1112. Highest Grade For Each Student. RANK() Window Function Solution + CTE and Group By Solution.

LeetCode SQL Solution of Problem 1126. Active Businesses. AVG() Window Function Solution + Group By CTE Solution!

LeetCode SQL Solution of Problem 1132. Reported Posts II. Implemetation of LEFT JOIN and Count Solution!

LeetCode SQL Solution of Problem 1149. Article Views II. Implementation of Group By, Count DISTINCT, and CTE Solution!

LeetCode SQL Solution of Problem 1158. Market Analysis I. Simple and Efficient LEFT JOIN Solution!

[SOLVED with 3 Different Implementations] LeetCode SQL Solution of Problem [1264. Page Recommendations].

LeetCode SQL Solution of Problem [1445. Apples and Oranges]. 3 Different Approaches - Self Join, Pivot Data with CASE Statements, and Built-In PIVOT Operator.

LeetCode SQL Solution of Problem 175. Combine Two Tables. LEFT JOIN Solution.

LeetCode SQL Solution of Problem 176. Second Highest Salary. Rank() Window Function Solution + Limit Offset Solution.

LeetCode SQL Solution of Problem 177. Nth Highest Salary. Dense_Rank() Window Function Solution + Correlated Subquery Solution.

LeetCode SQL Solution of Problem 178. Rank Scores. Dense_Rank() Window Function Solution + Correlated Subquery Solution.

LeetCode SQL Solution of Problem [180. Consecutive Numbers]. Self Join Approach + Window Function Approach Lag() and Lead() Explained.

Solved with Self Join Technique - LeetCode SQL Solution of Problem 181. Employees Earning More Than Their Managers.

Solved with [Self Join] or [Group By]+[Having] Techniques - LeetCode SQL Solution of Problem 182. Duplicate Emails

LeetCode SQL Solution of Problem 183. Customers Who Never Order.

LeetCode SQL Solution of Problem 184. Department Highest Salary. Multiple Solutions Including RANK() Window Function.

Unlock the Solution to LeetCode Problem 185 - Department Top Three Salaries with These 4 SQL Solutions!

LeetCode SQL Solution of Problem 1919. Leetcodify Similar Friends.

LeetCode SQL Solution of Problem 196. Delete Duplicate Emails.

LeetCode SQL Solution of Problem [1965. Employees With Missing Information]. Combines the results of two queries using the UNION operator.

Two Solultions - Self Join Solution + Lag() Window Function Solution - Problem 197. Rising Temperature.

LeetCode SQL Solution of Problem 1972. First and Last Call On the Same Day. RANK() Window Function Implementation.

LeetCode SQL Solution of Problem [1978. Employees Whose Manager Left the Company]. Using SELF JOIN and LEFT JOIN Techniques.

LeetCode SQL Solution of Problem 2004. The Number of Seniors and Juniors to Join the Company. SUM() Window Function Solution.

Implementation utilizing Self Join, CTE, and Subquery Techniques. LeetCode SQL Solution of Problem [2084. Drop Type 1 Orders for Customers with Type 0 Orders].

LeetCode SQL Solution of Problem [2199. Finding the Topic of Each Post]. Solved using Recursive CTE, CHAR_LENGTH(), REPLACE(), SUBSTRING_INDEX(), and GROUP_CONCAT() functions.

LeetCode SQL Solution of Problem 262. Trips and Users. Solved with CTE, Group By, and CASE WHEN Statement.

LeetCode SQL Solution of Problem #511. Game Play Analysis I. Group By + Min() Solution.

[Solved!!!] First_Value() Window Function Implementation + Group By with Common Table Expression Implementation. LeetCode SQL Solution of Problem 512. Game Play Analysis II.

LeetCode SQL Solution of Problem 534. Game Play Analysis III. Correlated Subquery, Self Join, and SUM() Window Function Solutions.

LeetCode SQL Solution of Problem 550. Game Play Analysis IV. Common Table Expression Implementation.

LeetCode SQL Solution of Problem 569. Median Employee Salary.

LeetCode SQL Solution of Problem 570. Managers with at Least 5 Direct Report. CTE Implementation.

LeetCode SQL Solution of Problem 574. Winning Candidate. MySQL LIMIT and SQL Server TOP Implementation.

SQL Solution - The combination of the LEFT JOIN operation and the WHERE conditions ensures that the query retrieves employee names and their corresponding bonus amounts if the bonus is less than 1000 or if no bonus record exists for the employee.

LeetCode SQL Solution of Problem 578. Get Highest Answer Rate Question. Simple CTE Solution!

LeetCode SQL Solution of Problem 580. Count Student Number in Departments. Simple LEFT JOIN Solution.

LeetCode SQL Solution of Problem [584. Find Customer Referee].

LeetCode SQL Solution of Problem 585. Investments in 2016. Subquery and CTE with LEFT JOIN Solutions

SQL Solution - Group the orders based on customer numbers, count the number of orders for each customer, order the groups in descending order by the order count, and then limit the result to only one row.

LeetCode SQL Solution of Problem 601. Human Traffic of Stadium. Row_Number() Window Function Solution + Cross Join Solution.

LeetCode SQL Solution of Problem 602. Friend Requests II: Who Has the Most Friends. Subquery + UNION ALL Solution.

LeetCode SQL Solution of Problem [603. Consecutive Available Seats].

LeetCode SQL Solution of Problem 608. Tree Node. Self Join + Union Solution.

LeetCode SQL Solution of Problem 612. Shortest Distance in a Plane. Solution Using MIN(), SQRT(), POWER(), and CAST() Functions.

LeetCode SQL Solution of Problem 614. Second Degree Follower. Simple INNER SELF JOIN Solution.

LeetCode SQL Solution of Problem 626. Exchange Seats. SELF LEFT JOIN Implementation.

This T-SQL solution efficiently expands date range into multiple individual rows using recursive Common Table Expressions (CTE). By employing this method, the process of breaking down date range into multiple distinct rows is streamlined, providing a more effective and manageable approach to handling date-related data within the context of a SQL database.

Both approaches serve the same purpose of expanding date ranges in SQL, but they have different characteristics and considerations.

T-SQL Learn how to efficiently transform time strings in the format HH:mm:ss into either the equivalent time data type, the total number of minutes, or the total number of seconds using SQL Server's T-SQL.

T-SQL Stored Procedure to Dynamically PIVOT Rows to Columns with Built-In PIVOT Operator.

Explore the performance differences between the HAVING and WHERE clauses by looking into the order in which filtering is applied.

In general, for smaller datasets or when you need a simple data structure, table variables can be a good choice. For larger datasets or when you need more advanced features like indexing and better statistics, temporary tables might be more appropriate.

Microsoft SQL Server TSQL Scripts: PIVOT Rows to Columns with Built-In PIVOT Operator or multiple CASE Statements

In this article, We'll check and validate email addresses using the LIKE clause in combination with the built-in PATINDEX function.

SQL Server: UNPIVOT Columns to Rows with Built-In UNPIVOT Operator. Alternatively, Use UNION ALL Statements to UNPIVOT to Convert Columns to Rows.

SQL Server: UNPIVOT Columns to Rows with Built-In UNPIVOT Operator. Alternatively, Use UNION ALL Statements to UNPIVOT to Convert Columns to Rows.

Microsoft SQL Server TSQL Scripts: Numbers Generator like generate_series in PostgreSQL! Using Recursive CTE or Cross Join SQL techniques to generate a virtual numbers table for any range of number. Print 1 to 100. Print 1 to 1001.

With a magical LEFT JOIN, you can effortlessly capture all rows from your primary table and unlock valuable information from the secondary table, even if there are no direct matches.

With a magical RIGHT JOIN, you can effortlessly capture all rows from your primary table and unlock valuable information from the secondary table, even if there are no direct matches.

A cross join combines all rows from two or more tables, creating a Cartesian product that explores every possible combination of data.

Confused about INNER JOIN in SQL? Our practical example simplifies the syntax and demonstrates how this powerful operation can be used to merge datasets efficiently.

SQL Server: The FORMAT function provides a more flexible way to format datetime values in T-SQL.

Demonstrate Using SELF-JOIN Technique to Find and Check Overlapping Date Ranges in SQL: A User Subscription Scenario

SQL Server: Solving a sreal-world scenario using the UPDATE statement in combination with JOIN and WHERE clauses.

SQL Server: Leveraging the power of the MERGE statement's OUTPUT clause to effectively track and manage data modifications. Dive into real-world examples demonstrating how to capture and preserve altered data into a regular table, temporary table, or table variable, gaining valuable insights into the before and after states of your records.

SQL Server: Leveraging the power of the UPDATE statement's OUTPUT clause to effectively track and manage data modifications. Dive into real-world examples demonstrating how to capture and preserve altered data into a regular table, temporary table, or table variable, gaining valuable insights into the before and after states of your records.

SQL Server: Learn how to easily create a comma-separated list from multiple rows using the powerful STRING_AGG() function!

SQL Server: Learn how to utilize OPENJSON with CROSS APPLY to navigate through nested JSON structures. Explore practical examples showcasing OPENJSON's flexibility and effectiveness in dealing with arrays and nested JSON objects

SQL Server: When performing UPDATE with JOIN, using common table expression (CTE) can be very intuitive

SQL Server: When using table aliases in an UPDATE statement, you assign shorter and more convenient names to tables, which can make the statement easier to read and write.

SQL Server: Learn how to effectively use the ORDER BY CASE-WHEN statement to perform advanced multi-column sorting in your SQL queries.

Discover how FULL JOIN combines records from two tables, even with NULL values on both sides, ensuring no data is left behind!