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

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 (http://blog.SQLAuthority.com)

About these ads

117 thoughts on “SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

  1. So what I don’t understand is why the application isn’t sorting the information in what ever way the user of the application wants it to be sorted. The biggest cost of the above execution plan is the sort operation. .Net applications and reporting applications are capable of sorting data – and if the application is running into performance issues with large sets of data – perhaps the data sets should be reduced or aggregated in order to accommodate the performance issues.

    Also, if you must do the sorting on the DB Server side, and it must be dynamic, why do 6 case statements in a row? Why not do 2 case statements such as
    CASE @OrderBy WHEN ‘Postal’ THEN PostalCode
    WHEN ‘City’ THEN City
    WHEN ‘State’ THEN StateProvinceID ELSE AddressID END

    Then you don’t have 6 case statements being evaluated everytime the Stored Proc runs. You’ll lower the overhead of the Order By Operation.
    In order to get the direction of the sort just do 2 case statements. 1 for the ‘D’ and 1 for the ‘A’, and nest the other case statement within.

    • Sorting should generally be done on the db server, partly because of the massive computing power and speed of the server but mainly because there may be indexes that can be used.

      However, there are valid cases where a data set (sometimes large) can be brought down to a workstation client or to a server side of a web app (or even to the web client — aka browser) where the programmer is using certain tools such as the amazing grid widgets I’ve seen from DevXPress or Infragistics, which can implement sorting, grouping, crosstab and even drill down either on the client or using built-in Ajax functionality to bring down subsets of a data large data set. This allows the user to repeatedly resort, regroup, crosstab or drill down without having to requery the database.

  2. Simon,

    Nested Case Statement will not work in Order By.

    This is a sample example.

    The point of this post was to do it in SQL not in application.

    We do not want to do it in application because we have gained 60% performance improvement doing this way than the way you are explaining.

    Kind Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  3. why can’t you do
    case when @OrderByDirection = ‘D’ THEN (CASE @OrderBy WHEN ‘Postal’ THEN PostalCode
    WHEN ‘City’ THEN City
    WHEN ‘State’ THEN StateProvinceID ELSE AddressID END) END DESC,
    case when @OrderByDirection ‘D’ THEN (CASE @OrderBy WHEN ‘Postal’ THEN PostalCode
    WHEN ‘City’ THEN City
    WHEN ‘State’ THEN StateProvinceID ELSE AddressID END) END ASC

    The only issue you’re gonna run into is a datatype conversion for the postal code and city, but you could CAST the StateProvinceID into a varchar and pad it if you really wanted to do the logic on the server and still keep proper sorting.

    But I must admit, I’m not a fan of the dynamic sorting on the server side – I’d be interested in knowing how much data is actually being pulled across to the application layer where you’re seeing 60% gains. Sounds like the data could be aggrigrated higher or more criteria added to restrict the amount of data crossing the pipe in the first place. With 60% gains (not knowing what those gains really are – could be a few seconds for all I know) it sounds like the bottleneck is the application. An evaluation of the application code would probably fetch you 60% gains as well.

  4. sorry, there’s supposed to be a in there for the second case statement, I didn’t test it or anything, I just wrote it in the comments text box

  5. Simon,

    Your method still has four cases, as you said it will have data conversion problem as well slower or faster depending on data.

    We absolutely do not like dynamic SQL. Our Application Team leads has made rule for that.

    Thanks, however we are SQL fans.
    I love SQL and rather work on SQL to improve performance instead of application. Application just builds the string and we like complied stored procedure which is way faster than dynamic SQL.

    Regards,
    Pinal

    • Sir,
      I have one table in sql server, which has 2 column (i.e t1 [date time], t2 [date time])

      Example of table
      T1 T2
      1-dec-11 9:00 AM 3-dec-11 10:00 AM

      I need output
      1-dec-11 = 15 hrs [24-9]
      2-dec-11 =24hrs
      3-dec-11 = 10Hrs

      Please sir try to solve my problem

      [email removed]

  6. IT’s ok……Can u tell me on thing?

    I have one stored Procedure, in which I have used multiple case statements in Where Clause…Will they be executed for each row in table…? COz….this is not expected….

  7. Are there any advantages in using CASE over IF in a stored procedure?

    The SQL compiler only stored the execution plan of the last executed IF branch and therefore if the next execution happens on another IF branch, the Stored Procedure is recompiled for that branch – thereby inducing ‘dynamism’ to a pre-compiled SP.

    Are there any advantages in using a CASE statement? Won’t the SQL compiler compile only the last executed CASE WHEN branch and store it in it’s execution plan? Like it does for the IF statement?

    Pardon me if my knowledge of the concepts is off.

  8. Has anyone found a solution to the conversion problem?

    When the case statement references columns of various datatypes, SQL often complains of a conversion error.

    This is annoying and frustrating.

    Dynamic SQL sucks on security and performance.

    • ORDER BY
      CASE –- VARCHAR types ascending
      WHEN @OrderBy = ‘ProductNameAsc’ THEN ProductName
      END ASC,
      CASE –- VARCHAR types descending
      WHEN @OrderBy = ‘ProductNameDesc’ THEN ProductName
      END DESC,
      CASE — INT types ascending
      WHEN @OrderBy = ‘QuantityAsc’ THEN Quantity
      END ASC,
      CASE — INT types descending
      WHEN @OrderBy = ‘QuantityDesc’ THEN Quantity
      END DESC,
      CASE — MONEY types ascending
      WHEN @OrderBy = ‘PriceAsc’ THEN Price
      END ASC,
      CASE — MONEY types descending
      WHEN @OrderBy = ‘PriceDesc’ THEN Price
      END DESC,
      CASE — Default order by
      WHEN 1 = 1 THEN ProductName
      END ASC

      • I’ve seen the same thing in ORDER OVER clauses, instead of in the ORDER BY at the end. Works with columns of different data types.

  9. Has anyone used more than one field in the WHEN Clause:

    i.e.
    CASE WHEN @OrderBy = ‘Last’
    THEN Lastname, FirstName END

    When I use Lastname, FirstName’ in a SQL Where clause I do not get an error but if I put the same statement in a CASE statement, then I get an error that it does not like the comma between Lastname and FirstName. What am I misunderstanding?

    Any help will be appreciated.

  10. I have a query with 8 to 10 columns on which sorting need to be done dynamically, it is efficient to do with order by and case statements or individual if else blocks depending on the column selected for sorting.

  11. Pingback: SQL SERVER - Dynamic Case Statement - FIX : ERROR 156 : Incorrect syntax near the keyword Journey to SQL Authority with Pinal Dave

  12. Hello,

    I would like to ask if there is a way to have multiple (consecutive) order fileds in one query. Example:

    ORDER BY
    CASE WHEN @OrderBy = ‘Option1′
    THEN ORDER BY Field1 ASC, Field 2 DESC END,
    CASE WHEN @OrderBy = ‘Option2′
    THEN ORDER BY Field1 DESC, Field 2 DESC END,

    This is obviously wrong but I need something like this.
    KShah above has asked the same thing but I don’t seem to be able to find an answer.

    Thanks in advance,
    Vasilis

    • 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

    • Try this…

      ORDER BY
      CASE WHEN @OrderBy = ‘Option1′
      THEN ORDER BY Field1 ASC END, Field 3 ASC
      CASE WHEN @OrderBy = ‘Option2′
      THEN ORDER BY Field1 DESC, Field 2 DESC END,

  13. Why is this such a bulky SQL statement? You can accomplish the same thing when you change your order by like:

    ORDER BY
    CASE
    WHEN @OrderBy = ‘Postal’ AND @OrderByDirection = ‘D’
    THEN PostalCode DESC
    WHEN @OrderBy = ‘Postal’ AND @OrderByDirection != ‘D’
    THEN PostalCode
    WHEN @OrderBy = ‘State’ AND @OrderByDirection = ‘D’
    THEN StateProvinceID DESC
    WHEN @OrderBy = ‘State’ AND @OrderByDirection != ‘D’
    THEN StateProvinceID
    WHEN @OrderBy = ‘City’ AND @OrderByDirection = ‘D’
    THEN City DESC
    WHEN @OrderBy = ‘City’ AND @OrderByDirection != ‘D’
    THEN City
    END

    and if you want to increase performance and readability (in my opinion) then do it this way:

    ORDER BY
    CASE @OrderByDirection
    WHEN ‘D’
    CASE @OrderBy
    WHEN ‘Postal’
    THEN PostalCode DESC
    WHEN ‘State’
    THEN StateProvinceID DESC
    WHEN ‘City’
    THEN City DESC
    END
    ELSE
    CASE @OrderBy
    WHEN ‘Postal’
    THEN PostalCode
    WHEN ‘State’
    THEN StateProvinceID
    WHEN ‘City’
    THEN City
    END
    END

    Just a suggestion.

  14. This logic does not work when you use distinct in your select statement.
    Could you describer, How can we use this logic when we are using distinct in the select statement?

    Thanks

  15. Vasilis Please Trye this

    order by
    CASE
    WHEN @Test1 = 1 THEN
    CASE WHEN @NestedTest2 = 1 THEN Customer.CustomersOwnNumber ELSE Customer.CustomerNumber
    END
    END,
    CASE WHEN @SortOrder = 2 THEN Customer.Balance END DESC, Customer.CustomerName,
    CASE WHEN @SortOrder 1 and @SortOrder 2 THEN Customer.CustomerName End

  16. Can’t use case in ORDER BY clause. I have 2 ways to solve this problem:

    1. Use the dataview sort property to sort the data in your code.

    2. Use IF/ELSE statements in SQL server to choose the appropriate query to use. Example:

    IF @sort = ‘Balance’
    SELECT * from Table ORDER BY Customer.Balance
    ELSE IF @Sort = ‘CustomerName’
    SELECT * FROM Table ORDER BY CustomerName

    I use SQL Server 2000. I do not know if this was changed in 2005.

  17. Hello Vasilis

    I tried something like this ..let the case statement fall through

    ORDER BY
    CASE WHEN @OrderBy = ‘Option1′
    THEN ORDER BY Field1 END ASC,
    CASE WHEN @OrderBy = ‘Option1′
    THEN ORDER BY Field2 END DESC,
    CASE WHEN @OrderBy = ‘Option2′
    THEN ORDER BY Field1 END DESC,
    CASE WHEN @OrderBy = ‘Option2′
    THEN ORDER BY Field 2 END DESC ,

    This is obviously wrong but I need something like this.
    KShah above has asked the same thing but I don’t seem to be able to find an answer.

    Thanks in advance,

  18. The answer to Vasilis´question:

    Use the same case multiple times in a row:

    ORDER BY
    CASE WHEN @SortOrder=1 THEN Cards.Cardholder
    END,
    CASE WHEN @SortOrder=1 THEN Transactions.Date
    END,
    CASE WHEN @SortOrder=2 THEN Transactions.Date
    END,
    Transactions.Amount

    This would in case 1 order by cardholders, date, amount
    and in case 2 oder by date, amount

  19. Here’s what I use:

    ORDER BY
    CASE @OrderBy
    WHEN ‘itemCategory’ THEN itemCategory
    WHEN ‘item’ THEN item
    END ASC,
    CASE @OrderBy
    WHEN ‘itemCategory DESC’ THEN itemCategory
    WHEN ‘item DESC’ THEN item
    END DESC

    If you have different datatypes, don’t cast as varchar since then you would have to pad strings, just a pain. When you have multiple data types, just divide the case groupings by datatype. So all the string fields would be together, all the numeric, then all the dates

    Example:
    ORDER BY
    CASE @OrderBy
    WHEN ‘itemCategory’ THEN itemCategory
    WHEN ‘item’ THEN item
    END ASC,
    CASE @OrderBy
    WHEN ‘someInt1′ THEN someInt1
    WHEN ‘someInt2′ THEN someInt2
    END ASC,
    CASE @OrderBy
    WHEN ‘itemCategory DESC’ THEN itemCategory
    WHEN ‘item DESC’ THEN item
    END DESC
    CASE @OrderBy
    WHEN ‘someInt1 DESC’ THEN someInt1
    WHEN ‘someInt2 DESC’ THEN someInt2
    END DESC

    We have a mixed environment where we needed to support SQL 2000 and SQL 2005. The other reason that helps for sorting on the server is we use paging on the server… If you get paged data and then sort in the code retrieving it, it only sorts the paged data, which is no good to anybody.

    I program in .net, and the object data source / gridviews automatically pass the sort with ” DESC” as part of the string, so we checked the asc and desc @OrderBy separately as you can see.

  20. Pinal,
    Is it possible to give an OrderBy like this below..

    Order By
    case Column1
    when ‘IT1′ then ‘XXX’
    else Column1
    end

    I suppose then part should carry a column name for an order by clause.

  21. has anybody figured out to get around the type problem.

    i know you can CAST things over to the same type, but when you cast an int over to a varchar is does whole string order. 1,11,111,2,22,222,3… I saw something mentioned about padding? i need padding on my head, but i am sure that is not what they meant.

    my types are

    3 varchars
    2 dates
    2 int

    i stinks to cast all to varchars.

    thank you in advance!

  22. Pinal.

    I’m wondering if I can order by more than one column using the technique you have discussed here.

    Here is an example of what I want to accomplish.

    ..
    ..
    (ORDER BY
    CASE @SortOrder
    WHEN ‘asc’ THEN
    CASE @SortBy
    WHEN ‘This’ THEN
    CASE [That]
    WHEN ‘A’ THEN 3
    WHEN ‘B’ THEN 2
    WHEN ‘C’ THEN 1
    ELSE 0
    END, [This] –the comma doesn’t compile
    END
    END asc

    Basically what I want to do is, when the user wants to sort by [This], I want to sort by [That] first using my custom specifications then sort by [This] normally.

    If I just wanted to sort by [This], I would code

    WHEN ‘This’ THEN [This]

    But in my case I want to do some custom logic on [That] first then sort by [This]. The comma syntax I present there doesn’t work.
    I’d like to avoid having to put another case statement for each WHEN clause in the CASE [That] clause if possibke.

    Just wondering if you had any suggestions, thanks!

    Matt.

  23. I’m wondering if anyone can help me with a cleaner implementation of this.

    –note if sorting by [this] we need to sort by [that] first then by [this]
    case @SortOrder
    when ‘asc’ then
    case @SortBy
    when ‘This’ then
    case [That]
    when 4 then 1
    else 0
    end
    end
    end desc,
    case @SortOrder
    when ‘asc’ then
    case @SortBy
    when ‘This’ then [This]
    end
    end desc,

    I think that that should evaluate to
    Order by [That], [This]

    Cheers folks.

    Matt

  24. I used above solution as below…

    ORDER BY
    CASE WHEN @sort = 0
    THEN X.SurName + ‘ ‘ + X.FirstName END,
    CASE WHEN @sort = 1
    AND @sortdirection = ‘D’
    THEN X.ClientName END DESC,
    CASE WHEN @sort = 1
    AND @sortdirection = ”
    THEN X.ClientName END,
    CASE WHEN @sort = 2
    AND @sortdirection = ‘D’
    THEN X.StatusTypeDesc END DESC,
    CASE WHEN @sort = 2
    AND @sortdirection = ”
    THEN X.StatusTypeDesc END,
    CASE WHEN @sort = 3
    AND @sortdirection = ‘D’
    THEN Convert(varchar(20),X.CreationDate) END DESC,
    CASE WHEN @sort = 3
    AND @sortdirection = ”
    THEN Convert(varchar(20),X.CreationDate) END,
    CASE WHEN @sort = 4
    AND @sortdirection = ‘D’
    THEN X.Name END DESC,
    CASE WHEN @sort = 4
    AND @sortdirection = ”
    THEN X.Name END,
    CASE WHEN @sort = 5
    AND @sortdirection = ‘D’
    THEN Convert(varchar(20),X.StartDate) END DESC,
    CASE WHEN @sort = 5
    AND @sortdirection = ”
    THEN Convert(varchar(20),X.StartDate) END,
    CASE WHEN @sort = 6
    AND @sortdirection = ‘D’
    THEN X.categorycode END DESC,
    CASE WHEN @sort = 6
    AND @sortdirection = ”
    THEN X.categorycode END

  25. I’m a bit rust on SQL but this statement in the order by confuses me. Doesn’t 3 mean the 3rd column, 2 mean the 2nd, etc…? If so why doesn’t 0 throw an error? I know it doesn’t but I don’t know why.

    CASE [That]
    WHEN ‘A’ THEN 3
    WHEN ‘B’ THEN 2
    WHEN ‘C’ THEN 1
    ELSE 0

  26. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  27. SET NOCOUNT ON

    IF LEN(@SortExpression) = 0
    SET @SortExpression = ‘ID DESC’

    SELECT * FROM
    (
    SELECT
    [ID],
    [AddedBy],
    [AddedDate],
    [AmountOfSales],
    [ModifiedBy],
    [ModifiedDate],
    […],
    ROW_NUMBER() OVER (ORDER BY CAST(@SortExpression as sql_variant)) AS RowNum
    FROM
    [dbo].[sell_Sales]
    WHERE
    ([UserID] = @UserID)
    ) sales
    WHERE sales.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
    ORDER BY CAST(@SortExpression as sql_variant)

  28. @Puneet,

    This is usually done through Front End. They can put a default value in the combo box (drop down box). Ask your Front End Developer to implement this logic through front end.

    I have seen many cases, we usually implement these things through front end.

    ~ IM.

  29. Puneet, you can use this trick:

    ORDER BY
    CASE WHEN Country=’US’ OR Country=’UK’ THEN 0
    ELSE 1 END
    , Country

    This will order the list at first by 1 for us and uk and 0 for others then reorder it by country for those with same 1 or 0

  30. I have on urgent query….

    suppose i am using like query to search…
    query may be like
    select firstname from tbllogin where firstname like ‘%su%’

    what i want how can i sort data on the basis of result found and then sorted on searched value i.e “su”

    return data will be like
    firstname
    sumit
    sumitkumar
    kumarSumit
    vermakumarsumit

    i hope you get me….

    please help me

  31. @Sumit,

    Use CharIndex function to location the position of ‘SU’ Character. Below is a sample code.

    create table #tbllogin (firstname varchar(200))
    go
    insert into #tbllogin values (‘Kumarsumit’)
    insert into #tbllogin values (‘sumit’)
    insert into #tbllogin values (‘Kumsumit’)
    insert into #tbllogin values (‘Ssumit’)
    go
    Select FirstName from #tbllogin
    go
    Select FirstName
    from #tbllogin
    Where FirstName like ‘%SU%’
    order by charindex ( ‘su’, firstname )
    go
    drop table #tbllogin

    ~ IM.

  32. I agree with Imran Mohammed also you can add the length of the firstname as I noticed you wand the shorter firstnames first:

    Select FirstName
    from #tbllogin
    Where FirstName like ‘%SU%’
    order by charindex ( ’su’, firstname ),Len(firstname)

  33. Thanks a lot, i was stuck with this problem
    your code help me :D
    i going to buy some beers for you :P
    your blog save me a lot of times

  34. 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.

    • @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

  35. 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

  36. 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 ….. :(

  37. 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

    • @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’

  38. 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

  39. 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

    • @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]);

    • 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]);

    • 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]);

  40. 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.

  41. 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

  42. 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

  43. 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

    • 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

  44. 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′)

  45. 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…

  46. Hello to all i’m usin this code

    select * from ARTICULOS
    order by CD_ARTICULO asc

    but the result that i have is 1,10,100,125 when the select result must be 1,2,3…10….100,etc why the clause is listing the ids not secuencialy 1,23??
    it would be so much apreciated your help.
    Thank to you very much =)

      • thank you Carl for your time, the data type of the ID is varchar but actually we are using correlatives to introduce de ids.
        and i use “select * ” because i need all the info not only the ID

        thanks again =)

  47. Thanks carl; You wrote this 2 years ago and it helped me today. It works greate 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

    • I have a bit of a different spin on this. I have a stored procedure that creates a temp table and does a bunch of code to populate it.

      declare @data table (fields…)

      much code…

      select * from @data
      order by sort1, sort2, sort3, sort4, sort5, sort6

      I always want to sort by sort1-sort6

      BUT each of these can be in ASC or DESC order. There are also cooresponding fields sort1direction – sort6direction what hold ‘ASC’ or ‘DESC’

      So what I would need is something like this – but I know this doesn’t work.

      select * from @data
      order by sort1 sort1direction, sort2 sort2direction, sort3 sort3direction….

      NOTE: I can’t use dynamic sql there is too much code in the body of this procedure and there are already several parts using dynamic sql statements.

      Many Thanks!
      LT

  48. hi,

    i have a problem as below
    a table has 4 column and 1 row..I want only 1 record as a result by applying formula.
    table is like
    CCY POLID amt1 amt2 amt3 amt4
    GBP 01XYZ 0.00 1.00 0.00 2.00

    formula is if amt2 >0 then result =amt2
    else if amt1 >0 then result =amt1
    else if amt3>0 then result=amt3
    else result =amt4

    i need to insert the result value into a temp table also.

    can you please help me to find a solution of this?

  49. One answer to Simon’s good question as to why sort on the database side vs sorting in the application is that the database engine can do sorting using indexes (if the indexes exist). If the db engine can query straight from an index (which sometimes it can), then there is sometimes immense performance gain.
    Someone in another website mentioned that the optimizer might get confused as to which saved execution plan to use and suggested building dynamic SQL in the SP, and sp_executesql (using parameterized query of course) so that the different execution plans get saved. Of course, in that case, one might also consider writing 6 different SPs each using a different order by. Not saying either one of those is best.

  50. Hi Pinal , fantastic blog.
    What if you have to order by more than 1 field? With the first field having ASC or DESC

    This: (But the correct method, cannot seems to get it right)

    ORDER BY
    CASE WHEN @OrderBy = ‘Postal3′
    THEN PostalCode END DESC, Street

    CASE WHEN @OrderBy = ‘Postal2′
    THEN PostalCode END ASC, Street
    GO

  51. if give this statement is not working helpme

    order by table . column, case when @sortby =1 then vendor. vend_num
    when @sortby=2 then vender_all.vend_category Else vendor_st.name
    this statement if give 1 mean vend-num it come orderby depending upon the value order by is work but it was not work tell me the ans

  52. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  53. Hi,
    I got an error when I am opening management studio 2012. It is saying ‘can not find one or more components.Please reinstall the application’.What could happen.I already uninstall and reinstall sql server few times.But it is giving same error every time .Any solution would be appriciated.
    Thanks

  54. Hy Dave. Thanks for all the great work you are doing. I’ facing a problem with order by case.

    I am trying to select from one table a list of products ordered by price, year, name, and others….
    The problem is that I must make zero values come last when sorting ascending.


    SELECT * FROM Product P
    ORDER BY CASE WHEN @OrderBy='Date ASC' THEN Date END ASC,
    CASE WHEN @OrderBy='Price ASC' THEN Price END ASC,
    CASE WHEN @OrderBy='Title ASC' THEN Title END ASC,
    CASE WHEN @OrderBy='' THEN Match END

    This works but don’t put the zero at the bottom of the list.
    So, I tried to transform it (see next code), but it gave me the error ‘Incorrect syntax near ‘,’.’

    select Price from Advert A

    ORDER BY CASE WHEN @OrderBy='Price ASC' THEN

    (case A.Price WHEN
    0 THEN 1 ELSE 0
    END,A.Price )

    END ASC

  55. I’ve seen this which was fast and simple:
    SELECT *,
    ROW_NUMBER( ) OVER (ORDER BY CASE @OrderBy
    WHEN ‘param1′ THEN param1
    WHEN ‘param2′ THEN param2
    WHEN ‘param3′ THEN param3
    ELSE ID
    ) as RowNo
    FROM ….
    …………..
    If you wanted to, you could add an ORDER BY RowNo, but I believe that is unnecessary.

  56. Following code is not giving any error but also not giving proper output.

    DECLARE @SortField varchar(100)
    set @SortField = ‘Make,Model’

    SELECT Make,Model FROM CarMaster
    WHERE ISNULL(DeletedFlag,0)=0
    ORDER BY
    Case @SortField When ” Then MAKE Else @SortField End

  57. what about I wanted the last one like this

    CASE WHEN @OrderBy = ”
    AND @OrderByDirection != ‘D’
    THEN City, LastName, FirstName END

    i cant seem to do it. any tips?

  58. Hi Pinal,

    I used a very simple method for my scenario. I have the sorting criteria set in a separate table.
    My solution is like this:
    DECLARE @LIST VARCHAR(4000)
    SET @LIST = SUBSTRING(
    ( SELECT ‘, ‘ + CASE pfr.PRIORITY_CD WHEN ‘ACCT’ THEN ‘Acct’ ELSE ‘PlayerID’ END + CASE pfr.IS_ASC WHEN 1 THEN ‘ ASC’ ELSE ‘ DESC’ END
    FROM dbo.PRIORITY_COMP_SETTING pcs WITH(NOLOCK)
    JOIN dbo.PRIORITY_FACTOR_REF pfr WITH(NOLOCK) ON pcs.PRIORITY_ID = pfr.PRIORITY_ID
    AND pfr.IS_ACTIVE = 1
    WHERE pcs.COMP_SET_ID = 1
    ORDER BY pcs.PRIORITY_IND
    FOR XML PATH (”)
    ),2,20000)
    –PRINT @LIST
    SELECT *
    FROM dbo.players
    ORDER BY ( SELECT @LIST)

    The only thing to keep in mind is to specify column names in single quotes in case you are directly writing the logic in order by clause instead of using a varchar type variable. I compared two methods and found that cost of using a varchar type variable is lower.

    I suppose this method should apply to your example in blog and other examples in comments section.

    Guys, please let me know whether this approach works for you. Please let me know your suggestions regarding performance as well.

    Thanks,
    Deepak Khandelwal

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s