SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


SQL CASE Statement in WHERE Clause


CASE Statement In SQL Select WHERE Clause

In the following t-sql sample SELECT code, you can see the SQL Server CASE statement in WHERE clause.
The sql CASE statement is applied over PerAssemblyQty column value of BillOfMaterials table in AdventureWorks2008R2 sample database.
If you check the CASE expression, you will see that according to the unit of measure used in a bill of material of a product, the filtering argument quantity has different values.

select
 bom.*,
 u.Name
from Production.BillOfMaterials bom
inner join Production.UnitMeasure u on u.UnitMeasureCode = bom.UnitMeasureCode
where
 PerAssemblyQty >=
 CASE u.Name
  WHEN 'Boxes' THEN 1
  WHEN 'Each' THEN 30
  WHEN 'Ounces' THEN 9
  WHEN 'Inch' THEN 40
  ELSE 0
 END
Code

Using sql AdventureWorks sample database on Microsoft SQL Server 2008 R2 database instance, you can run the t-sql select statement examples.





SQL CASE Statement In WHERE Clause with BETWEEN

The following t-sql script has sql CASE in WHERE clause.
The WHERE condition filter applied for this sql sample is on OrderDate column.
If the SalesPersonId is null which means not known only a single date orders is requested.
But if SalesPersonId is not null which means the sales person is identified within the order header, then all orders between specific dates is selected.

select
 SalesOrderID,
 SalesPersonID,
 OrderDate,
 TotalDue
from Sales.SalesOrderHeader
where
 OrderDate between (case when SalesPersonID is null then
 '2006-05-31' else '2006-03-01' end)
 and '2006-05-31'
Code

Note that, the first part of sql BETWEEN clause is defined using a sql CASE conditional statement.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.