Parse Coloumn into One Single String

There is 2 ways I know :
------------------------------------------------------------------------------------------
DECLARE @List VARCHAR(MAX)

SELECT
           @List = ISNULL(EmployeeID + ', ' , '') + @List
FROM
           Employee

SELECT @List
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

SELECT
LEFT(CA.List, LEN(CA.List)-1)
FROM
(
SELECT
CONVERT(VARCHAR(100),EmployeeID) + ',' AS [text()]
FROM
 Employee 
FOR XML PATH('')
)CA(List)
-------------------------------------------------------------------------------------------

Sometimes way no 2 faster than no 1, but for some condition no 1 is faster.

Apply the APPLY Clause

The real magic happens when you use SQL Server 2005's new APPLY clause. The APPLY clause let's you join a table to a table-valued-function. That let's you write a query like this:

SELECT  C.CustomerID, 
	O.SalesOrderID,
	O.TotalDue
FROM 
	AdventureWorks.Sales.Customer AS C
CROSS APPLY
	AdventureWorks.dbo.fn_GetTopOrders(C.CustomerID, 3) AS O
ORDER BY 
	CustomerID ASC, TotalDue DESC

which results in this...

CustomerID  SalesOrderID TotalDue
----------- ------------ ---------------------
1           45283        37643.1378
1           46042        34722.9906
1           44501        26128.8674
2           46976        10184.0774
2           47997        5469.5941
2           57044        4537.8484
3           53616        92196.9738
3           47439        78578.9054
3           48378        56574.3871
4           47658        132199.8023
. . .

credit : http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Javascript get text from dropdownlist

var ddlReport = document.getElementById("<%=DropDownListReports.ClientID%>");
var Text = ddlReport.options[ddlReport.selectedIndex].text;
var Value = ddlReport.options[ddlReport.selectedIndex].value;

SQL DATEADD Function

Returns a new datetime value based on adding an interval to the specified date.
SQL DATEADD SyntaxDATEADD ( datepart , number, date )


DECLARE @DateNow DATETIME
SET @DateNow='2007-06-04'
SELECT DATEADD(Year, 3, @DateNow) AS NewDate 
Return Value = 2010-06-04 00:00:00.000

SELECT DATEADD(quarter, 3, @DateNow) AS NewDate
Return Value = 2008-03-04 00:00:00.000

SELECT DATEADD(Month, 3, @DateNow) AS NewDate
Return Value = 2007-09-04 00:00:00.000

SELECT DATEADD(dayofyear,3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

SELECT DATEADD(Day, 3, @DateNow) AS NewDate
Return Value = 2007-06-07 00:00:00.000

SELECT DATEADD(Week, 3, @DateNow) AS NewDateReturn Value = 2007-06-25 00:00:00.000


SELECT DATEADD(Hour, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 03:00:00.000

SELECT DATEADD(minute, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:03:00.000

SELECT DATEADD(second, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:03.000

SELECT DATEADD(millisecond, 3, @DateNow) AS NewDate
Return Value = 2007-06-04 00:00:00.003




repost from : http://sqltutorials.blogspot.com/2007/06/sql-dateadd-function.html

Rumus Ms Excel untuk mempermudah penghitungan PPh

RUMUS MS EXCEL TARIF PASAL 17 WP OP/PPH 21
kopikan rumus ini pada cell tempat dimana anda akan menghitung PPh 21 (sel x19 adalah nilai PKP)

=IF(X19<=25000000,X19*5%,IF(X19<=50000000,25000000*5%+(X19-25000000)*10%,IF(X19<=100000000,25000000*5%+(50000000-25000000)*10%+(X19-50000000)*15%,IF(X19<=200000000,25000000*5%+(50000000-25000000)*10%+(100000000-50000000)*15%+(X19-100000000)*25%,25000000*5%+(50000000-25000000)*10%+(100000000-50000000)*15%+(200000000-100000000)*25%+(X19-200000000)*35%))))

Rumus tarif progresif di Module
apabila anda lebih ingin hasil yang lebih pro, buka vb editor excel (tekan Alt+F11) dan insert new module, dan kopikan rumus ini di script editornya.lalu simpan dan isikan rumus “=progresif(PKP) “(cell dimana nilai PKP ada) lalu enter.

Function progresif(pkp)
Select Case pkp
Case Is <= 25000000
progresif = pkp * (5 / 100)
Case Is <= 50000000
progresif = 1250000 + ((pkp – 25000000) * 10 / 100)
Case Is <= 100000000
progresif = 3750000 + ((pkp – 50000000) * 15 / 100)
Case Is <= 200000000
progresif = 11250000 + ((pkp – 100000000) * 25 / 100)
Case Is > 200000000
progresif = 36250000 + ((pkp – 200000000) * 35 / 100)
End Select
End Function

Rumus terbilang

Ini adalah rumus terbilang, cara nya sama dengan rumus progresif diatas. “=terbilang(nilai)”

Function Terbilang(Nilai)
If Nilai = “0″ Then
Terbilang = “- N I H I L -”
Else
Snil = Format(Str(Nilai), “000000000″)
JUTA = Mid(Snil, 1, 3)
RIBU = Mid(Snil, 4, 3)
SATU = Mid(Snil, 7, 3)

If JUTA = “000″ Then
JUT = “”
Else
UCAP = Ucapan(JUTA)
JUT = UCAP + “Juta “
End If

If RIBU = “000″ Then
RIB = “”
Else
UCAP = Ucapan(RIBU)
RIB = UCAP + “Ribu “
End If

If SATU = “000″ Then
SAT = “”
Else
UCAP = Ucapan(SATU)
SAT = UCAP
End If
Terbilang = “( ” + JUT + RIB + SAT + “Rupiah )”
End If
End Function

Function Ucapan(Bilang)
RATUSAN = Left(Bilang, 1)
PULUHAN = Mid(Bilang, 2, 1)
SATUAN = Right(Bilang, 1)

Select Case RATUSAN
Case “0″
SRATUS = “”
Case “”
SRATUS = “”
Case “1″
SRATUS = “Seratus “
Case “2″
SRATUS = “Dua Ratus “
Case “3″
SRATUS = “Tiga Ratus “
Case “4″
SRATUS = “Empat Ratus “
Case “5″
SRATUS = “Lima Ratus “
Case “6″
SRATUS = “Enam Ratus “
Case “7″
SRATUS = “Tujuh Ratus “
Case “8″
SRATUS = “Delapan Ratus “
Case “9″
SRATUS = “Sembilan Ratus “
End Select

Select Case PULUHAN
Case “0″
SPULUH = “”
Case “”
SPULUH = “”
Case “1″
SPULUH = “”
Case “2″
SPULUH = “Dua Puluh “
Case “3″
SPULUH = “Tiga Puluh “
Case “4″
SPULUH = “Empat Puluh “
Case “5″
SPULUH = “Lima Puluh “
Case “6″
SPULUH = “Enam Puluh “
Case “7″
SPULUH = “Tujuh Puluh “
Case “8″
SPULUH = “Delapan Puluh “
Case “9″
SPULUH = “Sembilan Puluh “
End Select

If PULUHAN = “1″ Then
Select Case SATUAN
Case “0″
SSATU = “Sepuluh “
Case “1″
SSATU = “Sebelas “
Case “2″
SSATU = “Dua Belas “
Case “3″
SSATU = “Tiga Belas “
Case “4″
SSATU = “Empat Belas “
Case “5″
SSATU = “Lima Belas “
Case “6″
SSATU = “Enam Belas “
Case “7″
SSATU = “Tujuh Belas “
Case “8″
SSATU = “Delapan Belas “
Case “9″
SSATU = “Sembilan Belas “
End Select

Else
Select Case SATUAN
Case “0″
SSATU = “”
Case “”
SSATU = “”
Case “1″
SSATU = “Satu “
Case “2″
SSATU = “Dua “
Case “3″
SSATU = “Tiga “
Case “4″
SSATU = “Empat “
Case “5″
SSATU = “Lima “
Case “6″
SSATU = “Enam “
Case “7″
SSATU = “Tujuh “
Case “8″
SSATU = “Delapan “
Case “9″
SSATU = “Sembilan “
End Select
End If
Ucapan = SRATUS + SPULUH + SSATU
End Function

credit : primarycons.wordpress.com

Sort Number in Varchar Coloum

Here are some methods:
declare @t table(data varchar(15))
insert into @t
select '6134' union all
select '144' union all
select '7345' union all
select '109812' union all
select '100074'union all
select '1290' union all
select '45764'

--Method 1
select data from @t
order by cast(data as int)

--Method 2
select data from @t
order by data+0

--Method 3
select data from @t
order by len(data),data

--Method 4
select data from @t
order by replace(str(data),' ','0')

--Method 5
select data from @t
group by data
order by replicate('0',len(data)),data

--Method 6
select data from @t
order by replicate('0',(select max(len(data+0)) from @t)-len(data))+data

--Method 7
select data from @t
cross join
(
        select len(max(data+0)) as ln from @t
) as t
order by replicate('0',ln-len(data))+data

(credit: sqlblogcasts.com/blogs/madhivanan)


Those method only can be used if the coloum containing number only.
For varchar also number, you can use this:

DECLARE @TBL TABLE
(
 Name VARCHAR(100)
)

INSERT INTO @TBL VALUES('TKK1'),('TKK2'),('TKK11')

SELECT * FROM @TBL
ORDER BY LEN(Name),Name

This method only limited for fixed pattern (varchar value always same).

If you have data like this:

1
2
11
abc
you can use this method:
SELECT ... FROM table order by
CASE WHEN column < 'A' THEN LPAD(column, size, '0') ELSE column END;

USB Disk Security 6.0.0.126

USB Disk Security 6.0.0.126 USB Disk Security provides 100% protection against any threats via USB drive, however, the majority of other products are unable even to guarantee 90% protection. USB Disk Security is the best antivirus software to permanently protect offline computer without the need for signature updates, but other antivirus software should update signature database regularly, and they cannot effectively protect offline computer.

USB Disk Security provides 100% protection against any threats via USB drive, however, the majority of other products are unable even to guarantee 90% protection. USB Disk Security is the best antivirus software to permanently protect offline computer without the need for signature updates, but other antivirus software should update signature database regularly, and they cannot effectively protect offline computer. This light and easy to use solution is 100% compatible with all software and doesn’t slow down your computer at all. You pay USB Disk Security once and get it all, however, other antivirus products should be paid for updates every year. (forums.hostsearch.com)

USB Disk Security 6.0.0.126

USB Disk Security v5.4.0.6