Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

SQL Server - Understanding full power of CASE Expressions

Aug 24 2010 2:10AM by Madhivanan   

Many programming languages like VB6 support IIF function which is used to return a value based on a particular expression/condition. In SQL Server the equivalent is to make use of CASE Expression, which is used to evaluate many conditions and return a single value. The following examples will give you some ideas on how to use CASE expression effectively

Source Data

declare @t table(emp_id int, emp_name varchar(100), sex char(1))
insert into @t
select 1,'Suresh','M' union all
select 1,'John','M' union all
select 1,'Clara','F' 

1 Set Male or Female based on the value of sex

select emp_id,emp_name, 
		case 
			when sex='M' then 'Male' 
			when sex='F' then 'Female' 
		end as sex
from
	@t

Result

emp_id      emp_name           sex
----------- --------------     -----
1           Suresh             Male
1           John               Male
1           Clara              Female

2 CASE expresion can be used to generate a cross-tab report.

Consider the following set of data. If you want to find out total sold amount of each product for each year from 2001 to 2003,you can use CASE expression as shown below

declare @order_details table
(
	order_id int identity(1,1),
	product_id int,
	amount decimal(12,2), 
	order_date datetime
)
insert into @order_details(product_id,amount,order_date)
select 1,7345.50,'20001012' union all
select 1,134.10,'20021110' union all
select 2,56.00,'20030131' union all
select 3,6345.45,'20010503' union all
select 4,244.75,'20040418' union all
select 4,748.00,'20060411' union all
select 5,7454.00,'20040622' 

select 
	product_id,
	sum(case when year(order_date)=2001 then amount else 0 end) as [2001],
	sum(case when year(order_date)=2002 then amount else 0 end) as [2002],
	sum(case when year(order_date)=2003 then amount else 0 end) as [2003]	
from
	@order_details
group by
	product_id


The result is

product_id  2001               2002                  2003
----------- --------------------------------------- -----------------------
1           0.00               134.10                0.00
2           0.00               0.00                  56.00
3           6345.45            0.00                  0.00
4           0.00               0.00                  0.00
5           0.00               0.00                  0.00

3 Dynamic Order by

Suppose you want to sort the data in such a way that product_id 3 should come first followed by others in ascending order. A CASE expression is helpful in this case too

select 
	order_id,
	product_id,
	amount,
	order_date	
from
	@order_details
order by 
	case when product_id=3 then 0 else 1 end,product_id

The result is

order_id    product_id  amount          order_date
----------- ----------- --------------  ------------------------ 
4           3           6345.45         2001-05-03 00:00:00.000
1           1           7345.50         2000-10-12 00:00:00.000
2           1           134.10          2002-11-10 00:00:00.000
3           2           56.00           2003-01-31 00:00:00.000
5           4           244.75          2004-04-18 00:00:00.000
6           4           748.00          2006-04-11 00:00:00.000
7           5           7454.00         2004-06-22 00:00:00.000

To know how to use IF statement effectively, refer this post http://beyondrelational.com/blogs/madhivanan/archive/2010/08/11/various-usages-of-if-else-clause-in-sql-server.aspx

Tags: t-sql, sql_server, sqlserver, tsql, BRH, #TSQL, #SQLServer, CASE, IIF,


Madhivanan
3 · 40% · 12924
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Just a quick note on your last example... it looks like the output was from a CASE expression that checked for Order_ID=4, not 3.

    commented on Aug 24 2010 12:25PM
    Brad Schulz
    107 · 2% · 483
  • Thanks Brad,

    I corrected the code. It should be based on product_id=3

    commented on Aug 25 2010 2:54AM
    Madhivanan
    3 · 40% · 12924

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]