Kamen Rider Decade eps - 01

For kamen rider fans, this is the link to download the subs for eps 1

Order of Zeronos - Kamen Rider Decade - 01
http://www.nyaatorrents.org/?page=download&tid=48378

#TV-Nihon - Kamen Rider Decade - 01
http://www.tvnihon.com/tracker/download.php?id=730&name=%5BT-N%5DKamen_Rider_Decade_01%5BB2A3D889%5D.avi.torrent

row_number function

a new function in sql server 2005. It's function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

syntax :

ROW_NUMBER ( ) OVER ( [<partition_by_clause>] <order_by_clause> )

example :

SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0

(source : Sql server documentation )

great, doesn't it ?

have a try.

Have a nice day.

Pivot Table in Sql Server 2005 ( Part 2 )

Here my logic to make the field more fleksible. So you don't have to fix the field. Of course this method used when you need to make your report fleksible and don't need to add the new field if there's a new data inserted into master table.

declare @field varchar(max)
declare @select varchar(max)
declare @employeeid int

set @field = ''

declare cr cursor for
select employeeid
from HumanResources.Employee

open cr

fetch next from cr into @employeeid

while @@fetch_status = 0
begin
if @field <> ''
begin
set @field = @field + ','
end

set @field = @field + '[' + convert(varchar(max),@employeeid ) + ']'

fetch next from cr into @employeeid
end

close cr
deallocate cr

set @select =
'SELECT VendorID, ' + @field + '
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @field + ' )
) AS pvt ORDER BY VendorID;'

exec (@select)

Have a try. Ask if you still confuse.

Have a nice day.

Pivot Table in Sql Server 2005

New but not new since sql server 2008 have been launch. I will share a little bit about it.

Here's the sample query from Sql Server 2005 Documentation:

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

here is the result :

VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5

if your database was upgraded from sql 2000 to sql 2005 , then you may set the compatiblel level to 90 to make the feature availeble.

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]

example : sp_dbcmptlevel 'databasename', '90

remember : if you use pivot features you have to :
1. one column as the value to aggregate
2. one column as the column name . From the example , the column name would be employeeid.
Unfortunately, You have to define it.
I thought i can use

FOR EmployeeID IN
( select employeeid from HumanResources.Employee )

but it doesn't work, that will make my query more fleksible if it's work. I will find other way to make it. May be a little trik for it. I will share it on other post.
3. you are in sql 2005 - > there's must be obsolute.
4. the feature is on. use sp_dbcmplevel to change the level to 90.

How cool isn't it ? Have a try and have a nice day.

Kamen Rider Decade

This year, the Kamen rider name Kamen Rider Decade. Decade, as its title suggests, is the tenth anniversary of the Heisei Rider Series, having begun with Kamen Rider Kuuga in 2000. It will be on air on January 25, 2009 in Japan.

You can find the group to discuss it at http://www.new.facebook.com/topic.php?topic=6418&post=22788&uid=30467778890#/group.php?gid=30467778890

Cann't wait to have it :D.

Mass Transfer Data

Here my tips for newbie in sql server to transfer massive data from table A to table B.

example using adventuresworks from sql server 2005 database sample.

select * into table1
from HumanResources.Employee
where employeeid between 1 and 20

i create table1 with the same field as HumanResources.Employee. Above query just an example.

--- use it if you need to update some field of records in table1 that may need to be updated if
-- there's exists on destination table.
update HumanResources.Employee set vacationhours = 24
from HumanResources.Employee a
inner join tabel1 b on a.employeeid=b.employeeid

-- here the main massive insert. the order of the field may the same as the destination table.
-- in case there's different, but some of the them have the same field than you need to add
-- (nmfield1, nmfield2 , .. before select statement ) before the select statement.
-- Its better if you defined the field that you want to insert

insert into tabel1
select a.*
from HumanResources.Employee a
left join tabel1 b on a.employeeid=b.employeeid
where b.employeeid is null

Have a try and good luck .

Happy New Years 2009

Using Default Parameter in Store Procedure

Sometime you need to create parameter with default value in your store procedure. Here is a simple example of how to make it:

create proc dbo.sp_getemployee
@employeeid char(10)= ''
as
select * from master_employee
where employeeid = case when @employeeid = '' then employeeid else @employeeid end

In here, i make a default value which is an empty string. I'm using it to manipulate the data that if the value is an empty string than return all employee data from employee table.

Now i can use it as below :

exec sp_getemployee

which is return all the record from master_employee

or

exec sp_getemployee @employeeid = '000000001'

which is return record with employeeid = '000000001'

How owsome isn't it ? but be carefule, it doesn't work if you create a report using Crystal report. Because, the crystal report will still ask to fill the value into the paramater.

Ok, now you know how to make default value into parameter in store procedure. Try it ok. Have a nice day