function IsNumeric(sText)
{
var ValidChars = "0123456789.";
for (i = 0; i < sText.length; i++) {
if (ValidChars.indexOf(sText.charAt(i)) == -1) {
return false;
}
}
return true;
}
| permalink | related link |
( 3.2 / 192 )So you've got a table with 30,000 rows and you want to find the median value. Microsoft provides a built in function called "AVG" which calculates the average. But what you really want is the median (or any other percentile calculation).
I've found a few different solutions online, but here's the one that seems to work the best.
WITH numbered AS (
SELECT myvalue, rn_up = row_number() OVER (ORDER BY myvalue, myrowidentity),
rn_down = = row_number() OVER (ORDER BY myvalue DESC, myrowidentity DESC)
FROM mytable
)
SELECT AVG(myvalue)
FROM numbered
WHERE ABS(rn_up - rn_down) <= 1
Why does this work? This function produces a temporary table called "Numbered" and it gives every record a number, both in terms of ascending rank and descending rank. As you can imagine, the record where ascending rank equals descending rank is the one in the middle. This happens when you have an odd number of records.
When you have an even number of records, you get one row where the ascending rank - descending rank = 1, and one row where the descending rank - ascending rank = 1. In that case, you average between the two values, and that's your median.
Now, what happens if you want to produce a range of medians? Let's say you have three types of products: books, DVDs, and CDs, and you want to find the median price for each. In this case, you use a partitioned with clause.
WITH numbered AS (
SELECT product_type, product_id, price, rn_up = row_number() OVER (PARTITION BY product_type ORDER BY price, product_id),
rn_down = row_number() OVER (PARTITION BY product_type ORDER BY price DESC, product_id DESC)
FROM products
)
SELECT product_type, AVG(price)
FROM numbered
WHERE ABS(rn_up - rn_down) <= 1
GROUP BY product_type
ORDER BY product_type
| permalink | related link |
( 3 / 164 )So I have an object with a bunch of properties. I'm using the C# / .Net Property Grid to work with the object. But what I'd really like to do is create an object with all the necessary default properties so that it can start from a normal, consistent state.
For example, here's how I list a property:
[DescriptionAttribute("My description text goes here"),
DefaultValue(true), XmlElement("UseDateRanges", typeof(bool)),
CategoryAttribute("Factor Settings")]
public bool UseDateRanges
{
get { return _UseDateRanges; }
set { _UseDateRanges = value; }
}
With all these attributes, the overall property shows up nicely in the PropertyGrid and serializes well to XML via the XMLSerializer. Pretty fun!
Now, when it comes time to make an object, I'd like to make a new object with all the default settings that I specified using DefaultValue(). Here's how I do it:
public static RevenueWindowSettings GetDefault()
{
RevenueWindowSettings rws = new RevenueWindowSettings();
Type t = typeof(RevenueWindowSettings);
PropertyInfo[] list = t.GetProperties();
for (int i = 0; i < list.Length; i++) {
AttributeCollection ac = TypeDescriptor.GetProperties(rws)[list[ i ].Name].Attributes;
DefaultValueAttribute def = (DefaultValueAttribute)ac[typeof(DefaultValueAttribute)];
if (def != null) {
list[ i ].SetValue(rws, def.Value, null);
}
}
return rws;
}
This function produces a new object, runs through all properties defined on the object, and for every one that had a default value set, it puts that value in place. Pretty nice!
| permalink | related link |
( 2.9 / 178 )This is a rather useful solution to a rather complex and annoying problem. I'm writing a .NET app that opens up an excel file and loads in some data from it. However, I want to use the Excel 2007 Ole DB provider rather than excel itself (I don't want to launch the whole application). Since some excel files have multiple worksheets, or since some of them have renamed worksheets, this is how I figure out which worksheet to use.
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=YES\";");
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string sheetname = dt.Rows[0].ItemArray[2].ToString();
The Row 0 and ItemArray 2 indices are magic numbers. If you want your code to be more robust, you should find the correct column in the datatable that has the columnname "TABLE_NAME" and use that. In this case, I use row 0 because I only want to pay attention to the first worksheet in the file.
| permalink | related link |
( 3 / 216 )Hi there,
I recently had to deal with a Subversion repository that produced some strange errors. Here's what happened.
We run Subversion using mod_dav_svn on Apache on OpenSSL. The end result is that we connect through an encrypted environment and our data is safe. However, although our environment worked fine when I set it up, users started to tell me that they weren't able to commit any files. The error they received was this:
Error: MKACTIVITY of '/sourcecode/!svn/act/23ae8076-d94a-1d43-9824-5cfe874b60a5': Could not read status line: An existing connection was forcibly closed by the remote host.
What did this mean? I searched for MKACTIVITY and I got a bunch of different hints. Some hints said that the problem was related to case sensitivity (I always use lowercase - case sensitivity is a bug carefully preserved by Unix for historical reasons). Another hint said that there might be a proxy in between Subversion and the HTTPS server. Another hint said that there might be an authentication problem.
The real answer? I had left the Subversion 1.4.3 binaries in place and installed 1.4.5 elsewhere. When I removed the 1.4.3 binaries Apache refused to load. For some reason, Apache was linking to the wrong version!
Now that I understood the problem, I just moved the 1.4.5 binaries into the location where the old ones used to live, and everything works fine.
Let that be a lesson to you - don't upgrade carelessly!
| permalink | related link |
( 4.1 / 498 )Back Next
Calendar
