Has anyone had to do sorting on a column of string datatype that can hold multiple decimals. Just like an IP Address but not necessarily 3 decimal place ?
I could not find an out of box solution with SQL Server and wasn't sure if there was a nice way to use a datatable or some specific function.
Thanks,
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Any suggestions would help.
Thanks
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. Â When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. Â The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. Â The sorting is implemented using the Category Group sorting option.
View 6 Replies View RelatedI have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies View RelatedHello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
Hello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies View RelatedHi all,
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Any help would be much appreciated
Thanks!
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
View 3 Replies View RelatedOk this is what I have
$query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";
But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????
hi,
i need a query based on sorting.
i have date column and time as varchar in another column and other order details
datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1
so i need to get query o/p as
12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm
based on date and time. pls help!
here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim
View 3 Replies View RelatedI have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1
When I sort, it works fine unless I get out to 10 digits.
Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10
Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9
The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
Hi everyone.
Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:
iif(somevalue is nothing, 'Other', somevalue)
Thank you in advance.
I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got
ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END
and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure. If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something? ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table CAST(getdate() as int)INSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
View 1 Replies View RelatedThis is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
Hi,
I hope I can explain what I am trying to achieve (in MS SQL Server):
The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name.
I have tried:
ORDER BY date, surname, forename (gives dates in right order but not names)
and
ORDER BY surname, forename, date (gives names in right order but not dates)
Thanks for any advice!
I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.
Is it more effecient to sort a dataset within my asp.net program?
Hi,
i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.
EXAMPLE
Initial situation:
BAKKER 0
AKKER 0
CARNIE 0
Now my question is how can i update the table with a sp in wich te outcome is:
AKKER 1
BAKKER 2
CARNIE 3
Can someone please help me?
Greetings,
Mark
i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?
View 1 Replies View RelatedAnybody know how to accomplish lexigraphical sorting? Any how-tos, docs, tuts or if you just want to paste code
THanks!
I'm trying to sort by domain user id, I can pull the user id fine but now I want to sort my SELECT by that name. How do I put the value of getUserIdentity into my SELECT statment.
Thanks
<script language="C#" runat="server"> protected string getUserIdentity() { return HttpContext.Current.User.Identity.Name.ToString().Replace("DOMAIN\", ""); }
protected void Page_Load(Object sender, EventArgs e) { username = getUserIdentity(); SqlConnection myConnection = new SqlConnection("Data Source=CLIENTELE;Initial Catalog=forms;Integrated Security=True"); SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM formTable WHERE userID = @username ORDER BY status DESC", myConnection);
DataSet ds = new DataSet(); myCommand.Fill(ds, "names");
MyDataGrid.DataSource=ds.Tables["names"].DefaultView; MyDataGrid.DataBind(); }
</script>
hi,
i am trying to figure out how to sort the data in a column in an ascending way when the column contains numeric and char data.
In the example I want to sort by category,lookup value. However, as you can see the top two rows are sorted right but the others are all messed up.
I would really appreciate any hints you can give me.
thanks
Rozina
Category ID lookupvalue
CPU Type4CPU Type 1
CPU Type4CPU Type 2
CPU Number51
CPU Number516
CPU Number52
CPU Number532
CPU Number54
CPU Number564
CPU Number58
I have a table with a composite primary key (ie a,b, autoInteger)
that includes an auto-incrementing system generated integer.
How do I sort out the records such that if a and b above repeat, I only
get the records with the highest autoInteger?
Thanks for any suggestions
Shelly
I need help sorting a date field. My field looks like this: dd/mm/yy. I want to sort by month. How would I do that?
Any help would be appreciated...
Hiya,
I'm back with another question.
I have a view of a table which contains users. the SQL is:
CREATE VIEW dbo.vwTravellers
AS
SELECT TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value
FROM dbo.tblTravellers
I would like to be able to sort the view by LastName, Firstname, but it doesnt allow a SORT BY in the view. Does anyone know of a cheeky little trick I could use to get around this?
Cheers
Guy
I wish I'd listened in that SQl Server training course I attended :(