Datetime data from Excel to SQL Server 2005 by SSIS

Posted: January 23, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment
A derived column to convert datetime data from Excel to DT_DBTimeStamp is used. Also all NULL values should be taken care too.
Expression should like this in the derived column:
 
(ISNULL(Sampling_Date) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)Sampling_Date)

Sort Max with Binary convertion way

Posted: January 21, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment

Question: Get minimum DateC and Maximum DateR along with the value in column Amount.

DECLARE @TableA TAble (DateR Datetime, DateC datetime, Amount smallmoney)

INSERT

INTO @TableA

SELECT

’01/01/08′, ’01/05/088′, 500.00

UNION ALL

SELECT ’01/06/088′, ’01/08/088′, 400.00

UNION ALL

SELECT ’01/13/088′, ’01/18/088′, 350.00

UNION ALL

SELECT null, ’01/21/088′, 650.00

UNION ALL

SELECT ’01/21/08′, null, 350.00

 

SELECT

CAST(Substring(binCol,1,8) as Datetime) as DateRmax

,

minDateC

,

CAST(Substring(binCol,9,4) as money) as MoneyWithMaxDateR

FROM

(

SELECT MAX(CAST(DateR as binary(8))

+

CAST(Amount as binary(4)) )

as

binCol, MIN(DateC) as minDateC

FROM

@TableA

)

as t

–OR

 

SELECT

CONVERT(DateTime, Substring(binCol,1,8)) as DateRmax

,

minDateC

,

CONVERT(SmallMoney, Substring(binCol,9,4) ) as MoneyWithMaxDateR

FROM

(

SELECT MAX(CAST(DateR as binary(8))

+

CAST(Amount as binary(4)) )

as

binCol, MIN(DateC) as minDateC

FROM

@TableA

)

as t

Tip for Fisical year and month in fisical year

Posted: January 15, 2009 | Author: | Filed under: SQL Server | Leave a comment

SELECT dtcol3, YEAR(DATEADD(m, 6, dtcol3)) AS [FiscalYear]

,

MONTH(DATEADD(m, 6, dtcol3)) AS [FiscalMonth] FROM theTable

Using UpdatePanels with Google Maps

Posted: January 14, 2009 | Author: | Filed under: ASP.NET 2 | Leave a comment
The trick is to not wrap the whole page in one big UpdatePanel provided by Jacob Reimers. Leave ASP.NET GoogleMap control outside of any Updatepanels.
http://www.reimers.dk/blogs/jacob_reimers_weblog/archive/2008/11/23/an-example-using-updatepanels-and-google-maps.aspx
 

DateTime column default string format (US)

Posted: January 12, 2009 | Author: | Filed under: SQL Server | Leave a comment 
A datetime column's default conversion format to a string is mon dd yyyy hh:miAM (or PM).  
If you want to work with the default, the WHERE clause will look like this:
....WHERE dtColumn Like '%Sep  9 2008%' to retrieve all September 9, 2008 records
Two spaces are needed for the day part. If the day part is one digit number,
 you need to pad a space infront of it.

A Group BY GROUPING SETS sample in SQL Server 2008

Posted: January 11, 2009 | Author: | Filed under: SQL Server 2008 | Leave a comment
Today, I came across a solution with GROUP BY SETS sample (http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/897ab93d-046a-4aa6-87de-0ad1b438b04c).
 
Table from user: tanoshimi
 Bedroom int,  

 Type int 

)  

 

DECLARE @i int = 0  

WHILE @i < 100  

BEGIN 

INSERT INTO saeed_sp VALUES 

(RAND() * 10, RAND() * 3)  

SET @i = @i +1  

END 

Solution from Artemakis:

SELECT    

isnull((CAST(Bedroom AS varchar(8)) + ‘ Bedroom’),‘Type ‘ + cast([type] as varchar(8))),  

CAST(COUNT(Bedroom) AS varchar(8)) + ‘ Items’    

FROM saeed_sp     

GROUP BY grouping sets(  

(bedroom),([type])  

)

 

TOP (100) PERCENT … ORDER BY in View or CTE

Posted: January 5, 2009 | Author: | Filed under: SQL Server 2005 | Leave a comment
 
I use a CTE to test this TOP (100) PERCENT… ORDER BY issue.
TOP 100 PERCENT  does a table scan and finished up without doing the ORDER BY action;
By using a variable for the TOP PERCENT as TOP (@i) PERCENT or a number other than 100, it will execute the ORDER BY clause;
As Adam answered above, by using TOP aNumber …. ORDER BY, it works too.
As George pointed out, it is important to use ORDER BY on the outer-most select statement to get correct ordered result.
 
The question is from the discussion at MSDN: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/266bb93d-41dd-4d5e-ab3b-0ef4cac3b45c#page:2

Show ObjectDataSource ‘s SQL statement in ASP.NET

Posted: January 1, 2009 | Author: | Filed under: ASP.NET 2 | Leave a comment
In my previous post, I posted a tip for how to get the parsed SQL statement of an SQLDataSource in ASP.NET(http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!288.entry). It seems it needs some changes to make it work with ObjectDataSource. Here is the code:
C#:
//using System.Collections.Specialized;
 

protected void ObjectDataSource1_Updating(object sender, ObjectDataSourceMethodEventArgs e)

{

IOrderedDictionary myCollection = (IOrderedDictionary)e.InputParameters;

IEnumerator ie = myCollection.GetEnumerator();

string strSQL = string.Empty;

while ((ie.MoveNext()))

{

DictionaryEntry param = (DictionaryEntry)ie.Current;

string strValue = string.Empty;

if (param.Value == null)

strValue =

"NULL";

else

strValue = param.Value.ToString();

strSQL += param.Key.ToString() +

"=" + strValue + "<BR />";

}

Response.Write(strSQL.ToString());

}

VB.NET:

Protected Sub ObjectDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs) Handles ObjectDataSource1.Updating

Dim myCollection As IOrderedDictionary = e.InputParameters()

Dim ie As IEnumerator = myCollection.GetEnumerator

Dim strSQL As String = ""

While (ie.MoveNext())

Dim param As System.Collections.DictionaryEntry = ie.Current()

strSQL += param.Key &

"=" & param.Value() & "<BR />"

End While

Response.Write(strSQL.ToString())

End Sub