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)
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.
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)
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.
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
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]
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….
Thanks
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.
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
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.
This fixed my problem. Thanks!
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.
[...] You can use any of above mentioned correct method and use CASE statement to ORDER BY dynamically. If you are interested to learn how to ORDER BY dynamically more than one column you can read my previous article SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable. [...]
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
Good..This solved my problem.
@Carl : How does this affect my performance than an ordinary ORDER BY clause?
Is there any other way to achive this?
rave: Keep it simple; if your users do not request a multi-column order by then I would not use the above solution. Another way would be to use Data binding throught Linq to SQL.
Hi,
Any answers for Vasilis’ question? I’m actually looking for exactly the same answer.
Thanks!
–E
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,
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.
Sorry didn’t realize it wouldn’t tab properly. /t I promise it’s easier to read when properly tabbed.
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
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
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.
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,
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
Is it possible to use the results of a case statement to create another case statement
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.
Pinal,
This post saved my skin. You continue to amaze and satisfy with your knowledge of SQL Server.
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.
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!
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.
Aha. I should have gone through the comments first!
I see Rökkvi has answered my question. I am eager to try!
Cheers folks.
matt.
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
This articles really helped me a lot….This solution fixed my problem….Thanks
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
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
Nvm, I just remembered that SQL treats it as a value when coming out of a CASE WHEN.
Thank you Vassalis for answering the question and Rökkvi for providing the answer!
[...] SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable [...]
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)
I have to List all the coutries from counties table order by CountryName, but I want United states and Germany to be on the top of list.
@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.
Hi Pinal, works great, thx!
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
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
@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.
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)
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
Great concept!
hi sir,
i want to make sp
and dynamically set the table name with select statement in sqlserver 2005.
plz help me……….
@Manish
Dyanimc TABLE names usually require dynamic SQL.
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
Hello Tweety,
You can get the desired result by using ROW_NUMBER() OVER (ORDER BY … PARTITIONED BY) clause.
Regards,
Pinal Dave
Hello Pinal,
Thank you for the reply. I got the required output.
Tweety
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
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 ….. :(
@madhuri
Please give an example of the TABLE.
if anyone have solution for my problem …
please do the favor for me …
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’
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
See Pinal’s article on seaparate comma-separated values:
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
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
columns are … Date , overall satisfaction , Staff availability etc .
@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]);
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
You are asking two questions:
1) How do i get months between two dates?
2) How do i return values separated by commas?
Pinal has alrwady answered #2 here: http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/
So, whatever answer you get for #1, you can them use that.
#1 itself has two answers!
a) Use a calendar TABLE
b) User a special FUNCTION
Both are easily found on google. I will supply a link for each where i did a quick search:
a) Calendar TABLE: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
b) FUNCTION: http://facility9.com/2008/12/08/getatableofmonthsbetweentwodates
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.
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
Cool idea. This solved my problem.
I was about to give up and code it in the application.
Cheers, this solved my problem after hours of googling!
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
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
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′)
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…
Thanks so much for the advice, I’m big fan of your page it had saved my day many times, regards
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 =)
First you should never use “Select * “. What is the datatype of the column used in the order by clause?
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 =)
Why are you using a varchar for ID field?
Hi Carl thank you so much 4 your help, actually i’m a jr programer so i’m working in a database who other guys did and they decided to set varchar the type of the product id.
i guess this is why it orders 1,10,100 not sequentially, but exist one way to order the ids in sequentially way? or because they’re numbers and are set like char it cannot do??
thnks alot!
If all the id’s are integers, then you could possible use the Convert or Cast.
http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/
But yes, that is why order id column is not sequentially ordered. So if the id has a space before or after number, the order is treated differently as a true integer datatype.
Thank you very much Carl for your help! i’ll try the tip
thanks a lot!
I always come across your posts. Thanks for the great information. Very helpful.