SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

July 17, 2007

This article is as per request from Application Development Team Leader of my company. His team encountered code where application was preparing string for ORDER BY clause of SELECT statement. Application was passing this string as variable to Stored Procedure (SP) and SP was using EXEC to execute the SQL string. This is not good for performance as Stored Procedure has to recompile every time due to EXEC. sp_executesql can do the same task but still not the best performance.

Previously:
Application:
Nesting logic to prepare variable OrderBy.
Database:
Stored Procedure takes variable OrderBy as input parameter.
SP uses EXEC (or sp_executesql) to execute dynamically build SQL.

This was taking big hit on performance. The issue was how to improve the performance as well as remove the logic of preparing OrderBy from application. The solution I came up was using multiple CASE statement. This solution is listed here in simple version using AdventureWorks sample database. Another challenge was to order by direction of ascending or descending direction. The solution of that issue is also displayed in following example. Test the example with different options for @OrderBy and @OrderByDirection.

Currently:
Database only solution:
USE AdventureWorks GO DECLARE @OrderBy VARCHAR(10) DECLARE @OrderByDirection VARCHAR(1) SET @OrderBy = 'State' ----Other options Postal for PostalCode, ---- State for StateProvinceID, City for City SET @OrderByDirection = 'D' ----Other options A for ascending, ---- D for descending SELECT AddressID, City, StateProvinceID, PostalCode FROM person.address WHERE AddressID < 100 ORDER BY CASE WHEN @OrderBy = 'Postal' AND @OrderByDirection = 'D' THEN PostalCode END DESC, CASE WHEN @OrderBy = 'Postal' AND @OrderByDirection != 'D' THEN PostalCode END, CASE WHEN @OrderBy = 'State' AND @OrderByDirection = 'D' THEN StateProvinceID END DESC, CASE WHEN @OrderBy = 'State' AND @OrderByDirection != 'D' THEN StateProvinceID END, CASE WHEN @OrderBy = 'City' AND @OrderByDirection = 'D' THEN City END DESC, CASE WHEN @OrderBy = 'City' AND @OrderByDirection != 'D' THEN City END GO
Above modified query has improved performance for our stored procedure very much. Let me know if you are implementing similar logic using any other method.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts

SQL SERVER – Microsoft White Papers – Analysis Services Query Best Practices – Partial Database Availability

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

126 Comments. Leave new

Dereck
October 12, 2009 9:22 pm
Great concept!
Reply
Manish
December 10, 2009 4:52 pm
hi sir,
i want to make sp
and dynamically set the table name with select statement in sqlserver 2005.
plz help me……….
Reply
- Brian Tkatch
  December 17, 2009 8:07 pm
  @Manish
  Dyanimc TABLE names usually require dynamic SQL.
  Reply
tweety
December 17, 2009 3:34 am
Hello Pinal
I have gone through a lot of posts of yours and all have proved to be very useful. thanks a lot.
Can you please help me with a small issue I am struck with. I need to use order by for 3 columns.
hello there,
I have 3 columns which I have to sort.
My data is
– Albany 22 194
– Anchorage 24 194
– Canada 38 34
– Carbondale 40 194
– China 51 40
– Des Moines 61 194
– England / UK 307 193
– France 69 66
– Hong Kong 83 84
– Mexico 308 123
– Nashville 119 194
– Russia 310 153
– Singapore 172 165
I want the data to be sorted as
Albany 22 194
Anchorage 24 194
Carbondale 40 194
Des Moines 61 194
Nashville 119 194
Canada 38 34
China 51 40
England / UK 307 193
France 69 66
Hong Kong 83 84
Mexico 308 123
Russia 310 153
Singapore 172 165
I tried to use the order by CASE but could not achieve it.
thanks in advance.
Reply
- Brian Tkatch
  December 17, 2009 8:12 pm
  @tweety
  What is exactly is the order? Cities before countries? Do you have a TABLE that lists either?
  To just list them,
  ORDER BY
  CASE PLACE
  WHEN ‘Albany’ THEN 1
  WHEN ‘Anchorage’ THEN 2
  WHEN ‘Carbondale’ THEN 3
  WHEN ‘Des Moines’ THEN 4
  WHEN ‘Nashville’ THEN 5
  WHEN ‘Canada’ THEN 6
  WHEN ‘China’ THEN 7
  WHEN ‘England / UK’ THEN 8
  WHEN ‘France’ THEN 9
  WHEN ‘Hong Kong’ THEN 10
  WHEN ‘Mexico’ THEN 11
  WHEN ‘Russia’ THEN 12
  WHEN ‘Singapore’ THEN 13
  END
  Reply
Pinal Dave
December 17, 2009 6:34 pm
Hello Tweety,
You can get the desired result by using ROW_NUMBER() OVER (ORDER BY … PARTITIONED BY) clause.
Regards,
Pinal Dave
Reply
Tweety
December 17, 2009 8:57 pm
Hello Pinal,
Thank you for the reply. I got the required output.
Tweety
Reply
Carl Soto
January 28, 2010 12:33 am
Try this:
ORDER BY
CASE
WHEN @col=”name” AND @sort=”asc” THEN (RANK() OVER (ORDER BY Name, Status))
WHEN @col=”name” AND @sort=”desc” THEN (RANK() OVER (ORDER BY Name DESC, Status))
WHEN @col=”status” AND @sort=”asc” THEN (RANK() OVER (ORDER BY Status, Name))
WHEN @col=”status” AND @sort=”desc” THEN (RANK() OVER (ORDER BY Status DESC, Name))
WHEN @col=”” THEN (RANK() OVER (ORDER BY Name, Status))
END
Reply
- Sparky
  May 3, 2013 5:52 am
  Brilliant! worked like a charm.
  Reply
Madhuri
February 26, 2010 2:47 pm
hi Pinal ,
i have a table abc with 16 columns . i am uploading excel file in that table so that all the data of the excel sheet is getting transferred in the SQL table . all the data is either ‘yes’ or ‘no ‘.
now i want to calculate the percentage of ‘yes’ in each column and i want to transfer that calculated percentage value in another table xyz for all the 16 columns. the first column, that is date that stores the date in “2010-01-01 00:00:00.000 ” form . and i need to get only month and year in the first column of the new table .
i don’t kn how to write store procedure for this .
please help me ….. :(
Reply
- Brian Tkatch
  March 16, 2010 9:26 pm
  @madhuri
  Please give an example of the TABLE.
  Reply
Madhuri
February 26, 2010 3:13 pm
if anyone have solution for my problem …
please do the favor for me …
Reply
P.Pandiarajan
March 16, 2010 7:26 pm
Hi Every body,
I have one doubt in case statement. my table have one record found. that record have one field ,that field have ‘1,2,3’ this value have convert into Sunday , Monday format how its possible………
regards
P.pandiaRajan
Reply
- Brian Tkatch
  March 16, 2010 9:28 pm
  @P.Pandiarajan
  CASE field
  WHEN 1 THEN ‘Sun’
  WHEN 2 THEN ‘Mon’
  WHEN 3 THEN ‘Tues’
  WHEN 4 THEN ‘Wednes’
  WHEN 5 THEN ‘Thurs’
  WHEN 6 THEN ‘Fri’
  WHEN 7 THEN ‘Satur’
  END + ‘day’
  Reply
P.Pandiarajan
March 17, 2010 10:16 am
Hi Brian Tkatch,
that field have combine in ‘1,2,3’. this is one record.
how to split the record in select query. then how to use the case statement.
I need the Sun,Mon,Tue
regards
P.pandiaRajan
Reply
Madhu
March 17, 2010 10:31 am
Date Overall Satisfaction staff availability Ease in locating Product availability Payment process Helpful staff After sales Cleanliness Store layout & signage Staff knowledge Parking facilities Price competitiveness Range Display of products Promotions
29/12/09 yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes
30/12/09 yes
31/12/09 yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes
1/1/2009 yes yes
1/1/2010 yes yes yes yes yes yes yes yes yes yes yes yes yes yes yes
1/1/2010 yes yes
2/1/2010 no yes yes yes
2/1/2010 yes
2/1/2010
2/1/2010 yes yes yes yes yes yes
3/1/2010 yes yes yes
3/1/2010 yes yes
4/1/2010 yes yes yes yes yes yes yes yes yes yes
4/1/2010 yes yes yes yes yes yes yes yes
5/1/2010 yes yes
7/1/2010 yes
7/1/2010 yes yes yes yes
Reply
Madhu
March 17, 2010 10:33 am
columns are … Date , overall satisfaction , Staff availability etc .
Reply
- Brian Tkatch
  March 17, 2010 6:56 pm
  @Madhu
  Here’s the answer. The format may be hard though:
  WITH
  Data
  (
  [Date],
  [Overall Satisfaction],
  [staff availability],
  [Ease in locating Product],
  [availability],
  [Payment process],
  [Helpful staff],
  [After sales],
  [Cleanliness],
  [Store layout & signage],
  [Staff knowledge],
  [Parking facilities],
  [Price competitiveness],
  [Range],
  [Display of products],
  [Promotions]
  )
  AS
  (
  SELECT CONVERT(SMALLDATETIME, ’29/12/09′, 3), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ’30/12/09′, 3), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ’31/12/09′, 3), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2009’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘no’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘3/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘3/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘4/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘4/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘5/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘7/1/2010’, 103), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘7/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
  )
  SELECT
  YEAR([Date]),
  MONTH([Date]),
  COUNT(CASE [Overall Satisfaction] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Overall Satisfaction]), 0) [Overall Satisfaction],
  COUNT(CASE [staff availability] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([staff availability]), 0) [staff availability],
  COUNT(CASE [Ease in locating Product] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Ease in locating Product]), 0) [Ease in locating Product],
  COUNT(CASE [availability] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([availability]), 0) [availability],
  COUNT(CASE [Payment process] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Payment process]), 0) [Payment process],
  COUNT(CASE [Helpful staff] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Helpful staff]), 0) [Helpful staff],
  COUNT(CASE [After sales] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([After sales]), 0) [After sales],
  COUNT(CASE [Cleanliness] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Cleanliness]), 0) [Cleanliness],
  COUNT(CASE [Store layout & signage] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Store layout & signage]), 0) [Store layout & signage],
  COUNT(CASE [Staff knowledge] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Staff knowledge]), 0) [Staff knowledge],
  COUNT(CASE [Parking facilities] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Parking facilities]), 0) [Parking facilities],
  COUNT(CASE [Price competitiveness] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Price competitiveness]), 0) [Price competitiveness],
  COUNT(CASE [Range] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Range]), 0) [Range],
  COUNT(CASE [Display of products] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Display of products]), 0) [Display of products],
  COUNT(CASE [Promotions] WHEN ‘yes’ THEN 1 END) / NULLIF(COUNT([Promotions]), 0) [Promotions]
  FROM
  Data
  GROUP BY
  YEAR([Date]),
  MONTH([Date])
  ORDER BY
  YEAR([Date]),
  MONTH([Date]);
  Reply
- Brian Tkatch
  March 17, 2010 7:02 pm
  Oops, forgotr to make it decimal:
  WITH
  Data
  (
  [Date],
  [Overall Satisfaction],
  [staff availability],
  [Ease in locating Product],
  [availability],
  [Payment process],
  [Helpful staff],
  [After sales],
  [Cleanliness],
  [Store layout & signage],
  [Staff knowledge],
  [Parking facilities],
  [Price competitiveness],
  [Range],
  [Display of products],
  [Promotions]
  )
  AS
  (
  SELECT CONVERT(SMALLDATETIME, ’29/12/09′, 3), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ’30/12/09′, 3), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ’31/12/09′, 3), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2009’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘no’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘3/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘3/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘4/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘4/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘5/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘7/1/2010’, 103), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘7/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
  )
  SELECT
  YEAR([Date]),
  MONTH([Date]),
  COUNT(CASE [Overall Satisfaction] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Overall Satisfaction]), 0) [Overall Satisfaction],
  COUNT(CASE [staff availability] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([staff availability]), 0) [staff availability],
  COUNT(CASE [Ease in locating Product] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Ease in locating Product]), 0) [Ease in locating Product],
  COUNT(CASE [availability] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([availability]), 0) [availability],
  COUNT(CASE [Payment process] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Payment process]), 0) [Payment process],
  COUNT(CASE [Helpful staff] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Helpful staff]), 0) [Helpful staff],
  COUNT(CASE [After sales] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([After sales]), 0) [After sales],
  COUNT(CASE [Cleanliness] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Cleanliness]), 0) [Cleanliness],
  COUNT(CASE [Store layout & signage] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Store layout & signage]), 0) [Store layout & signage],
  COUNT(CASE [Staff knowledge] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Staff knowledge]), 0) [Staff knowledge],
  COUNT(CASE [Parking facilities] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Parking facilities]), 0) [Parking facilities],
  COUNT(CASE [Price competitiveness] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Price competitiveness]), 0) [Price competitiveness],
  COUNT(CASE [Range] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Range]), 0) [Range],
  COUNT(CASE [Display of products] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Display of products]), 0) [Display of products],
  COUNT(CASE [Promotions] WHEN ‘yes’ THEN 1 END) * 1.00 / NULLIF(COUNT([Promotions]), 0) [Promotions]
  FROM
  Data
  GROUP BY
  YEAR([Date]),
  MONTH([Date])
  ORDER BY
  YEAR([Date]),
  MONTH([Date]);
  Reply
- Brian Tkatch
  March 17, 2010 7:04 pm
  If the percentage is of all responses, not just the non-NULL responses, divide by COUNT(*) instead:
  WITH
  Data
  (
  [Date],
  [Overall Satisfaction],
  [staff availability],
  [Ease in locating Product],
  [availability],
  [Payment process],
  [Helpful staff],
  [After sales],
  [Cleanliness],
  [Store layout & signage],
  [Staff knowledge],
  [Parking facilities],
  [Price competitiveness],
  [Range],
  [Display of products],
  [Promotions]
  )
  AS
  (
  SELECT CONVERT(SMALLDATETIME, ’29/12/09′, 3), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ’30/12/09′, 3), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ’31/12/09′, 3), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2009’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’ UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘1/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘no’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘2/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘3/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘3/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘4/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘4/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘5/1/2010’, 103), ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘7/1/2010’, 103), ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
  SELECT CONVERT(SMALLDATETIME, ‘7/1/2010’, 103), ‘yes’, ‘yes’, ‘yes’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
  )
  SELECT
  YEAR([Date]),
  MONTH([Date]),
  COUNT(CASE [Overall Satisfaction] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Overall Satisfaction],
  COUNT(CASE [staff availability] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [staff availability],
  COUNT(CASE [Ease in locating Product] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Ease in locating Product],
  COUNT(CASE [availability] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [availability],
  COUNT(CASE [Payment process] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Payment process],
  COUNT(CASE [Helpful staff] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Helpful staff],
  COUNT(CASE [After sales] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [After sales],
  COUNT(CASE [Cleanliness] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Cleanliness],
  COUNT(CASE [Store layout & signage] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Store layout & signage],
  COUNT(CASE [Staff knowledge] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Staff knowledge],
  COUNT(CASE [Parking facilities] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Parking facilities],
  COUNT(CASE [Price competitiveness] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Price competitiveness],
  COUNT(CASE [Range] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Range],
  COUNT(CASE [Display of products] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Display of products],
  COUNT(CASE [Promotions] WHEN ‘yes’ THEN 1 END) * 1.00 / COUNT(*) [Promotions]
  FROM
  Data
  GROUP BY
  YEAR([Date]),
  MONTH([Date])
  ORDER BY
  YEAR([Date]),
  MONTH([Date]);
  Reply
  - Madhu
    March 18, 2010 4:04 pm
    hi Tkatch & everyone
    i need one more favor ….
    i need to get all the months which are coming between two dates , not number of months. for example :
    i have date1 (2/2010) and date2(5/2010).
    i want the output as 2,3,4,5.another eg. .
    date1(10/2010) and date2(2/2011) then o/p shuld be ..
    10,11,12,1,2.
    i have two columns in my table 1st is “Month” and 2nd is “Year”
    please do the favor ….
Madhu
March 18, 2010 10:40 am
Hi Brain Tkatch ,
Thanks a lot for giving your valuable time & brain to me .
Let me execute this query . then i will let you kn the progress.
thanq u soo much .
hv a nice day.
Reply
Mishri
April 8, 2010 4:24 pm
Hey,
I was just wondering if its possible to set value of two variables based on the case ??
For e.g.
WHEN ’00’ THEN @Channel=’ABC’,@Status=’XYZ’
I am a newbee to SQL and I am not getting how to achieve this.
Please let me know the way t do it.
Any help is appreciated.
Thanks,
Mishri
Reply
- madhivanan
  August 19, 2015 3:50 pm
  No you need to use that expression twice
  @Channel=case WHEN ’00’ then ‘ABC’ end,
  @Status=case WHEN ’00’ then ‘XYZ end,
  Reply
Brian
June 11, 2010 10:03 pm
Cool idea. This solved my problem.
I was about to give up and code it in the application.
Reply
Kamal
September 14, 2010 5:59 pm
Cheers, this solved my problem after hours of googling!
Reply
mahesh
October 6, 2010 1:24 am
Hi Pinal,
I want to execute a set of sql statements depending on the category selected
to achieve this i am using case statement
following are the statements in the stored procedure –
case @cat
when ‘sports’ then
select * from news where type = @cat
when ‘tech’ then
select * from news where type = @cat
end
pls note – i dont want to use dynamic query
the above query not works
i have observed that we can’t use select clause in case statement
whereas we can use case in select statement.
Please clarify me on this
Kind Regards,
D.Mahesh
Reply
bhumi Dev
October 15, 2010 2:08 pm
Sir
can u solve this problem i want to find out number of days in 4 quater in year
1 qtr from jan-mar
2 april to june
3 jul-sept
4 oct-dec
if i have table fields start_date and End_date
iam inserting my code
————
SELECT convert(char(14),start_date,103) Start_Date,
convert(char(14),End_date,103) End_Date,
CASE WHEN datepart(mm,start_date) between 1and 3 and
datepart(mm,end_date) between 1 and 3
THEN datediff(d, end_date,start_date) ELSE
datediff(d,’2010-03-31′,start_date) END as Qtr1,
–Start 2nd Quater from
CASE WHEN datepart(mm,start_date) between 4 and 6
and
-datepart(mm,end_date) between 4 and 6
THEN datediff(d, end_date,’2010-04-01′) ELSE
datediff(d,’2010-06-30′,’2010-04-01′) END as Qtr2,
–Start 3rd Quater from
CASE WHEN datepart(mm,start_date) between 7 and 9
and
-datepart(mm,end_date) between 7 and 9
THEN datediff(d, end_date,’2010-07-01′) ELSE
datediff(d,’2010-09-30′,’2010-07-01′) END as Qtr3,
–Start 4th Quater from
CASE WHEN datepart(mm,start_date) between 10 and 12
and
-datepart(mm,end_date) between 10 and 12
THEN datediff(d, end_date,’2010-10-01′) ELSE
datediff(d,’2010-12-31′,’2010-10-01′) END as Qtr4,
datediff(d,end_date,start_date)Actul_Days
FROM Z_FDR
Reply
- Carl
  October 15, 2010 8:45 pm
  DECLARE @dtStart DATETIME
  DECLARE @dtEnd DATETIME
  SET @dtStart = ‘2010-12-14’
  SET @dtEnd = ‘2010-12-22′
  SELECT
  CASE WHEN MONTH(@dtStart) BETWEEN 1 AND 3
  THEN
  CASE WHEN MONTH(@dtEnd) BETWEEN 1 AND 3
  THEN DATEDIFF(DAY,@dtStart,@dtEnd)
  ELSE DATEDIFF(DAY,@dtStart,’2010-03-31′) END
  WHEN MONTH(@dtStart) BETWEEN 4 AND 6
  THEN
  CASE WHEN MONTH(@dtEnd) BETWEEN 4 AND 6
  THEN DATEDIFF(DAY,@dtStart,@dtEnd)
  ELSE DATEDIFF(DAY,@dtStart,’2010-06-30′) END
  WHEN MONTH(@dtStart) BETWEEN 7 AND 9
  THEN
  CASE WHEN MONTH(@dtEnd) BETWEEN 7 AND 9
  THEN DATEDIFF(DAY,@dtStart,@dtEnd)
  ELSE DATEDIFF(DAY,@dtStart,’2010-09-30′) END
  ELSE
  CASE WHEN MONTH(@dtEnd) BETWEEN 10 AND 12
  THEN DATEDIFF(DAY,@dtStart,@dtEnd)
  ELSE DATEDIFF(DAY,@dtStart,’2010-12-31’) END
  END AS QDAYS
  Reply
rhymie
April 7, 2011 3:26 pm
Can i use the field selected in case statement? Need urgent info. Thanks
SELECT DISTINCT PARTNERK1.job_no as jobNo, PARTNERK1.name, SendFlatK1.Date_send,
CASE WHEN RESPONSE.AA_REGNO = ‘CUSERR’
THEN (SELECT AA_REGNO FROM RESP WHERE RES_TYPE=’961′ AND JOB_NO = PARTNERK1.job_no)
END [AA_REGNO],
CASE WHEN RESPSTT.RES_CODE = ‘HD1’
THEN ‘1ST REMINDER FOR HANGING CUSTOMS FORMS’
WHEN RESPSTT.RES_CODE = ‘HD2’
THEN ‘2ND REMINDER FOR HANGING CUSTOMS FORMS’
–ELSE ‘CANCELLATION OF HANGING CUSTOMS FORMS’
END [RES_CODE],
Response.Recv_date,
Response.Recv_time
–FROM PARTNERK1 INNER JOIN (SendFlatK1 INNER JOIN (Response INNER JOIN RESPSTT ON RESPONSE.Recv_SNRF = RESPSTT.Recv_SNRF)
FROM PARTNERK1 INNER JOIN (SendFlatK1 INNER JOIN
(Response INNER JOIN RESPSTT ON RESPONSE.Recv_SNRF = RESPSTT.Recv_SNRF)
ON SendFlatK1.ACCOUNT_NO = Response.ACCOUNT_NO AND SendFlatK1.job_no = Response.job_no)
ON PARTNERK1.account_no = SendFlatK1.account_no AND PARTNERK1.job_no = SendFlatK1.job_no
WHERE Response.Decl_Type = ‘K1’
AND PARTNERK1.type = ‘1’
AND Response.account_no = ‘177974’
AND Response.user_id = ‘rat’
AND Response.agent_code = ‘HF1001’
AND Response.Res_Type = ‘963’
AND Response.Status = ‘HD’
AND RESPSTT.RES_CODE IN (‘HD1′,’HD2’)
Reply
sunil
June 3, 2011 3:26 pm
Sir please give a solution/query for the below table format
hcode tcode datetime count
0001 301 01.01.2011 12:23:34 6
0001 301 01.01.2011 12:25:45 8
0002 301 01.01.2011 12:25:49 8
002 301 01.01.2011 12:25:55 7
so from the above table i want to show like below
hcode tcode datetime count
0001 301 01.01.201 14
0002 301 01.01.2011 15
where i have used count function and convert function to select date between two date to get the result as the 1st table..but i need to show like the second one..
please reply quickly…
Reply