Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
If you like the videos you can support me on Patreon, / beardeddev
In this video I talk about how to use additive CASE statements in SQL Server.
For an introduction to CASE statements check out this video: • SQL Tutorial - CASE St...
If you are new to CTEs check out this video: • SQL TUTORIAL - CTEs Pa... , there is also a playlist on my channel
It is important to note that in the first case statement:
CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 END
This will cause any product categories that are not Bikes to be NULL, if this is not the desired result you can edit the query by adding ELSE:
CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 ELSE 0.00 END
CASE Statements were introduced to SQL Server in 2008. They are used to evaluate conditions and return a result based on those conditions. CASE Statements can be used in SELECT, UPDATE, DELETE, WHERE, HAVING.
In this CASE Tutorial we look at a common use of CASE statements within SELECT and also the benefit and common downfalls when writing CASE queries.
CASE Statement Syntax:
CASE WHEN [condition] THEN [value if true] ELSE [value if fales] END
SQL Queries in this video:
SELECT
Cat.EnglishProductCategoryName
, Subcat.EnglishProductSubcategoryName
, Prod.EnglishProductName
, CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 END
+ CASE WHEN Subcat.EnglishProductSubcategoryName = 'Road Bikes'
THEN 0.10 ELSE 0.00 END
+ CASE WHEN Subcat.EnglishProductSubcategoryName = 'Road Bikes'
AND (Prod.EnglishProductName LIKE '%Red%'
OR Prod.EnglishProductName LIKE '%Yellow%')
THEN 0.05 ELSE 0.00 END
AS Discount
FROM dbo.DimProductCategory AS Cat
INNER JOIN dbo.DimProductSubcategory AS Subcat
ON Cat.ProductCategoryKey = Subcat.ProductCategoryKey
INNER JOIN dbo.DimProduct AS Prod
ON Subcat.ProductSubcategoryKey = Prod.ProductSubcategoryKey
WHERE Cat.EnglishProductCategoryName = 'Bikes'
--AND Subcat.EnglishProductSubcategoryName = 'Road Bikes'
Please feel free to post any comments.
24 июл 2024