пятница, 18 февраля 2011 г.

Reading Numbers as Strings from .csv file

When Excel opens a csv file it processes it automatically, and displays numbers using a general format.

If you want to bring these fields in with leading zeros, you could change the file to a txt file (by changing the suffix), and then open it with Excel. This will bring up the text file wizard, which has options to treat the field as text. This will retain leading zeroes.

воскресенье, 13 февраля 2011 г.

Sample for HTTPWebRequest using PUT Method

try
{
string str = "test";
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
byte[] arr = encoding.GetBytes(str);
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create("url");
request.Method = "PUT";
request.ContentType = "text/plain";
request.ContentLength = arr.Length;
request.KeepAlive = true;
Stream dataStream = request.GetRequestStream();
dataStream.Write(arr, 0, arr.Length);
dataStream.Close();
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
string returnString = response.StatusCode.ToString();
}
catch {}

четверг, 10 февраля 2011 г.

sql: COUNT() with conditions


Here’s the skinny, I basically have to periodical parse raw data and save their totals on another table for easier access. In this scenario I basically need to count all raw and unique IP’s from a table and mark them; so when I parse the data again I don’t have to count the values I already totaled.

Below is a sample table containing some data. My goal is to get a count of the unique IP’s that are counted and the total number of raw ip’s that are not counted. This is a much simpler example of what I am trying to achieve on my script, but I hope you get the gist.
01 +----+---------------+---------+
02 | id | ip | counted |
03 +----+---------------+---------+
04 | 1 | 127.0.0.1 | 0 |
05 | 2 | 127.0.0.1 | 1 |
06 | 3 | 192.168.1.100 | 0 |
07 | 4 | 192.168.1.100 | 1 |
08 | 5 | 10.0.0.1 | 0 |
09 | 6 | 10.0.0.1 | 0 |
10 +----+---------------+---------+


Now here’s the query:1 SELECT ip,
2 COUNT(CASE WHEN counted=0 THEN id ELSE NULL END) AS not_counted,
3 COUNT(DISTINCT CASE WHEN counted=1 THEN ip ELSE NULL END) AS is_counted
4 FROM test
5 GROUP BY ip;
1 +---------------+-------------+------------+
2 | ip | not_counted | is_counted |
3 +---------------+-------------+------------+
4 | 10.0.0.1 | 2 | 0 |
5 | 127.0.0.1 | 1 | 1 |
6 | 192.168.1.100 | 1 | 1 |
7 +---------------+-------------+------------

Работа с данными hierarchyid

Пример, использующий среду CLR

Одной из частых операций, в которых участвуют два узла из иерархии, является нахождение ближайшего общего предка. Эта операция может быть описана в среде Transact-SQL либо в среде CLR, поскольку тип данных hierarchyid доступен в обеих. Рекомендуется использовать среду CLR, поскольку она обеспечивает большую производительность.

Используйте следующий код CLR, чтобы найти список предков и ближайшего общего предка:
using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;

public partial class HierarchyId_Operations
{
[SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
public static IEnumerable ListAncestors(SqlHierarchyId h)
{
while (!h.IsNull)
{
yield return (h);
h = h.GetAncestor(1);
}
}
public static void FillRow_ListAncestors(Object obj, out SqlHierarchyId ancestor)
{
ancestor = (SqlHierarchyId)obj;
}

public static HierarchyId CommonAncestor(SqlHierarchyId h1, HierarchyId h2)
{
while (!h1.IsDescendant(h2))
h1 = h1.GetAncestor(1);

return h1;
}
}

using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;

public partial class HierarchyId_Operations
{
[SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
public static IEnumerable ListAncestors(SqlHierarchyId h)
{
while (!h.IsNull)
{
yield return (h);
h = h.GetAncestor(1);
}
}
public static void FillRow_ListAncestors(Object obj, out SqlHierarchyId ancestor)
{
ancestor = (SqlHierarchyId)obj;
}

public static HierarchyId CommonAncestor(SqlHierarchyId h1, HierarchyId h2)
{
while (!h1.IsDescendant(h2))
h1 = h1.GetAncestor(1);

return h1;
}
}

Чтобы получить возможность использовать методы ListAncestor и CommonAncestor в следующих примерах Transact-SQL, постройте библиотеки DLL и создайте сборку HierarchyId_Operations в SQL Server, выполнив код, аналогичный следующему:

CREATE ASSEMBLY HierarchyId_Operations
FROM '\ListAncestors.dll'
GO
 

CREATE ASSEMBLY HierarchyId_Operations
FROM '\ListAncestors.dll'
GO

В начало
Перечисление предков

Создание списка предков узла — это распространенная операция, использующаяся, например, для демонстрации позиции в организации. Одним из способов ее реализации является применение возвращающей табличное значение функции, использующей класс HierarchyId_Operations, определенный выше:

Использование среды Transact-SQL:
 

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

Пример использования:
DECLARE @h hierarchyid
SELECT @h = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID, OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
JOIN ListAncestors(@h) AS A
ON ED.OrgNode = A.Node
GO

DECLARE @h hierarchyid
SELECT @h = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID, OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
JOIN ListAncestors(@h) AS A
ON ED.OrgNode = A.Node
GO
Нахождение ближайшего общего предка

С помощью класса HierarchyId_Operations, определенного выше, создайте следующую функцию Transact-SQL для нахождения ближайшего общего предка, затрагивающую два узла в иерархии:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Копировать
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO

Пример использования:
DECLARE @h1 hierarchyid, @h2 hierarchyid

SELECT @h1 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/

SELECT @h2 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/

SELECT OrgNode.ToString() AS LogicalNode, LoginID
FROM HumanResources.EmployeeDemo
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;
Копировать
DECLARE @h1 hierarchyid, @h2 hierarchyid

SELECT @h1 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/

SELECT @h2 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/

SELECT OrgNode.ToString() AS LogicalNode, LoginID
FROM HumanResources.EmployeeDemo
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;

Результирующий узел — /1/1/

SQL 2008: Where is SqlHierarchyId?

I spent 5 mins looking for the .NET type SqlHierarchyId today so I thought I'd share (it's probably very obvious but not so obvious that I actually knew). It seems to live in;

Microsoft.SqlServer.Types.dll

which, for me, lives in c:\program files\Microsoft SQL Server\MSSQL.1\Binn

CLR Enabled

sp_configure 'clr enabled', 1
go
reconfigure
go

вторник, 1 февраля 2011 г.

The DatePart() Function in SSRS


Using the DatePart() Function in SSRS can be tricky, even at the best of times. I have pulled my hair out too often trying to remember what syntax should be used, so have resorted to writing down all my findings. This is so that you don’t have to go through the same agony & frustration as I did, as well as for my own benefit (my memory is shot…)
What it does: Returns an Integer containing the specified part of a given date
Syntax: DatePart(interval, date[,firstdayofweek[,firstweekofyear]])
There are two ways to use this function:
  • Using the DateInterval collection
  • Using a string expression
 
Using the DateInterval Collection
Let’s start with an example:
To return the current month: =DatePart(DateInterval.Month, Today())
Now, in the expression builder (in BIDS), it will look like there’s something the matter with what we have just written:
You can ignore this, the expression will work. An important point to note when using the DateInterval collection, is that you must use a date that is of type Datetime. If you use a string representation of a date (“2009/11/13”), then you’ll need to convert this to Datetime using CDate(“2009/11/13”) before the expression will work. Thus:
This will work:  =DatePart(DateInterval.Month, CDate(“2009/11/13”))
This won’t:  = DatePart(DateInterval.Month, “2009/11/13”)
When you use the DatePart function to identify the day of the week you can specify what the First Day Of The Week is (defaults to Sunday if this is not specified). You can do this by accessing the FirstDayOfWeek collection. Example – To Return the day of the week, specifying Monday as the first day of the week:
DatePart(DateInterval.Weekday, CDate(“2009/11/13”), FirstDayOfWeek.Monday) returns 5 (Friday)
All the possibilities for using the DatePart() function with the DateInterval Collection:
The following table shows all the possibilities for the DatePart Function using the DateInterval collection. All expressions use a Parameter called MyDate (type = Datetime), which has a value of “2009/11/13 12:34:23”
ExpressionResult
DatePart(DateInterval.Second, Parameters!MyDate.Value)23
DatePart(DateInterval.Minute, Parameters!MyDate.Value)34
DatePart(DateInterval.Hour, Parameters!MyDate.Value)12
DatePart(DateInterval.Weekday, Parameters!MyDate.Value, FirstDayOfWeek.Monday)5
DatePart(DateInterval.Day, Parameters!MyDate.Value)13
DatePart(DateInterval.DayOfYear, Parameters!MyDate.Value)317
DatePart(DateInterval.WeekOfYear, Parameters!MyDate.Value)46
DatePart(DateInterval.Month, Parameters!MyDate.Value)11
DatePart(DateInterval.Quarter, Parameters!MyDate.Value)4
DatePart(DateInterval.Year, Parameters!MyDate.Value)2009

Using a String Expression
Again, let’s start with an example:
To return the current month:  =DatePart(“m”, Today())
Using a String expression for your interval enables you to use a date that is of datatype String:
=DatePart(“m”, “2009/11/13”)
All the possibilities for using the DatePart() function with a String Expression:
As noted previously, using a string expression for the interval allows you to use a date value that is of the String datatype. You can of course use a Datetime value if you wish to.
This table shows all the possibilities for the DatePart function using a String Expression for the interval. All expressions use a Parameter called MyDate (type = String), which has a value of “2009/11/13 12:34:23”
Date PartExpressionResult
SecondDatePart(“s”, Parameters!MyDate.Value)23
MinuteDatePart(“n”, Parameters!MyDate.Value)34
HoursDatePart(“h”, Parameters!MyDate.Value)12
Day of WeekDatePart(“w”, Parameters!MyDate.Value, FirstDayOfWeek.Monday)5
Day of MonthDatePart(“d”, Parameters!MyDate.Value)13
Day of YearDatePart(“y”, Parameters!MyDate.Value)317
Week of YearDatePart(“ww”, Parameters!MyDate.Value)46
MonthDatePart(“m”, Parameters!MyDate.Value)11
QuarterDatePart(“q”, Parameters!MyDate.Value)4
YearDatePart(“yyyy”, Parameters!MyDate.Value)2009

Is there a preferred method?
Ultimately, no. At the end of the day, all the results are the same. What is my preference? I use the DateInterval collection, mainly becuase I like that fact that you can’t get confused about which date part you are using (w/ww, y/yyyy, n….). It’s very clear to anyone who is reading the code that (for example) DateInterval.WeekOfYear refers to the week of the year and nothing else. It also forces you to use a Datetime Value, which safeguards against invalid dates (sort of).
I hope this clears things up a little!


Read more: http://www.lukehayler.com/2009/11/the-datepart-function-in-ssrs/#ixzz1CkTmT02H