Friday, October 17, 2008

Return XML in Sql Server 2005

one of the feature in sql server 2005 is returning the query in XML format.

Syntax :
[ FOR { BROWSE | } ]
::=

XML
{ {
RAW [ ( 'ElementName' ) ] | AUTO }
[
[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] }
] [ , ELEMENTS [ XSINIL | ABSENT ] ]
| EXPLICIT
[ [ , XMLDATA ]
]
| PATH
[ ( 'ElementName' ) ]
[

[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
Source: SQL Help Documentation

simple example :

select top 100 contactid, firstname, middlename, lastname
from Person.Contact
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL

At .net application we only have to consume the xml result. How to do it, i will post it later because i still exploring it :D

Monday, October 13, 2008

Reset Visual SourceSafe 6.0 Admin

Forgot your visual sourcesafe 6.0 admin ? i got the way after along time googling, now i will share it to you in case some day i forgot it again :D.

It's simple, just create a new sourcesafe database but don't forget the admin password. Than go to Data Folder ( your new database sourcesafe folder ) and find Um.dat. Copy the file to Data Folder ( the database sourcesafe folder which you want to open). Don't forget to backup the original first. After that try to log on using the new one.

Have a nice day.

Friday, October 10, 2008

Taxed Income Function: Case Indonesia

Base on UU PPh pasal 21 in Indonesia, every working citizen have to pay income tax with progressive calculation tariff. The detail of this UU PPh will not discuss in here. What i will try to discuss is how to implemented it into sql server function dan store the tariff into tabel.

Even the progressive calculation tariff may stay for along time, it may possible that the rate may change or the placement. Real case is, the change of PPh 21 before 2008 change from 5 placement into 4 placement on 2009. You can imagine how you have to change all your calculation in your salary system if you use "fix code" method in your calculation system.

This is my idea to make the function and store the tariff into table. I Hope it will help you to efficient your job.

Here is some step you need to consider:
1. The function is for progressive tariff policy
2. There is a table to store the rate. it will be explain later.
3. The calculation is for Taxed Income, i will used PKP as the variable.

Firstly, we create the table:

CREATE TABLE [dbo].[Master_Tarif_Pajak](
[Tahun] [char](4) NOT NULL,
[Tingkat] [int] NOT NULL,
[Lower] [decimal](18, 0) NULL,
[Upper] [decimal](18, 0) NULL,
[Pajak] [decimal](5, 2) NULL,
CONSTRAINT [PK_Master_Tarif_Pajak] PRIMARY KEY CLUSTERED
(
[Tahun] ASC,
[Tingkat] ASC
)ON [PRIMARY]
) ON [PRIMARY]

The Requirement is :
Entry the record from the first placement to the fifth and the last one the upper we leave it Zero
Your data my looks like this :

Tahun Tingkat Lower Upper Pajak
2008 1 0 25000000 0.05
2008 2 25000000 50000000 0.10
2008 3 50000000 100000000 0.15
2008 4 100000000 200000000 0.25
2008 5 200000000 0 0.35
2009 1 0 50000000 0.05
2009 2 50000000 250000000 0.15
2009 3 250000000 500000000 0.25
2009 4 500000000 0 0.30

(9 row(s) affected)

Next step is the function :

/*
created : 29-08-2008 by wihendro

*/
create function dbo.UDF_Calculate_Tax
(
@pkp money , @tahun char(4)
)
returns money
as
begin
declare @tingkat int , @pajak money, @lower money, @upper money, @pajak1thn money
, @terhitung money, @denda money

set @tingkat = 1
set @pajak1thn = 0
set @terhitung = 0

while exists( select tingkat from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat
)
begin
select @lower = [lower], @upper = [upper] , @pajak = pajak
from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat


if @pkp > @upper
begin
if @upper = 0
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung)*@pajak)
break
end
else if @lower is not null
begin
if (@terhitung + ( @upper - @terhitung )) <= @pkp begin set @pajak1thn = @pajak1thn + ((@upper - @terhitung)*@pajak) set @terhitung = @terhitung + (@upper - @terhitung ) end else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
end
else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung) * @pajak )
break
end
set @tingkat = @tingkat + 1
set @lower = null
set @upper = null
end

return @pajak1thn
end

Example:

select Pajak = dbo.UDF_Calculate_Tax(200000000,'2008' )

Result :
Pajak
---------------------
36250000.00

(1 row(s) affected)

So, if next year, the role change into 4 placement ( see the sample data that i have created for you ), you only have to fill the tariff to the table and in your only have to send the value 2008 as the year. More effectively if you make an entry modul for the tariff table. So prepare your income tax calculation program to overcome the new UU Pph next year.

Note: You need to entry the new tariff every new begging year. Just don't forget to maintain the table ok.

Go to try it.
Good Luck.

Tuesday, October 07, 2008

User Defined Function

In every sql statement you made, sometime there's sql statement that you use over and over. In a big project scale, there's a good idea to put it into Store Procedure or Function, depends on your needs. This time, i will discuss about Function,use to be konwn as a User-Defined Functions ( UDFs ).

User-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. UDFs divide into 3 types, which are Scalar Functions, Inline table-valued and multiline table-valued

1. Scalar Functions
Syntax:

CREATE FUNCTION [ schema_name. ] function_name
(
[ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ]
BEGIN
function_body

RETURN scalar_expression
END [ ; ]
(Source : SQL Help Documentation)

sample :

create function dbo.udf_hitung_score
(
@nilai numeric
)returns char(11)
as
begin
declare @grade char(1)

set @grade = case when @nilai between 85 and 100 then 'A'
when @nilai between 70 and 84 then 'B'
when @nilai between 60 and 69 then 'C'
when @nilai between 50 and 59 then 'D'
else 'F' end
return(@grade)
end

Example :

select dbo.udf_hitung_score(82)

You can use it as follow :
example, you have Table A with attribut Mark contain Student Mark with Numeric data type, than your query may become like this :

select nama_mahasiswa, nama_matakuliah, kelas , grade = dbo.udf_hitung_score(nilai)
from A
order by kelas

2. Inline Table-Valued Functions
syntax:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS TABLE [ WITH [ ,...n ] ] [ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_Master_customers
(
@customerid nchar(10)
) RETURNS TABLE
as
RETURN (select * from customers
where customerid = @customerid )

example :

select * from dbo.udf_Master_customers('Alfki')

you can join it to with others table.

3. Multistatement Table-valued Functions
Syntax :
CREATE FUNCTION [ schema_name. ] function_name
(
[ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE <> [ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_order
(
@customerid nchar(10)
) RETURNS @order TABLE (contactname varchar(60) ,orderid int, orderdate datetime
, requireddate datetime )
as
begin
declare @contactname varchar(60)

select @contactname = contactname
from customers where customerid = @customerid

insert @order
select @contactname , orderid, orderdate, requireddate
from orders
where customerid = @customerid

RETURN
end

example

select * from dbo.udf_order('alfki')

multistatement table-valued used if you need more logical programming to manipulate the data before displaying it.

Each type of UDFs has their own unique function depends on your needs in your project. But surely, it's very helpfull in your project later.

After this, i will try to collect some logic and make it as a function that may usefull for you.

Have a try and good luck.