*my little notes*: 2011

Monday 19 December 2011

Sort Varchar

combine string and number like 1.1

declare @t table(id int ,data varchar(50))
declare @t3 table(id int, data3 varchar(50))
insert into @t
select '1','Rak 10' union all
select '2','Rak 1.2' union all
select '3','Rak 122' union all
select '4','Rak 2' union all
select '5','Area 2'union all
select '6','Area Lantai 1' union all
select '7','Rak 1'

declare @xml xml,
@max_len int

insert @t3 (id, data3)
select id, SubString(data,(LEN (data) - CharIndex (' ', REVERSE(data))+2),LEN (data)) as data2
from @t

set @xml =
(
select data3,
cast('' + replace(data3,'.','') + '' as xml)
from @t3
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(50)')))
from @xml.nodes('/id_root/i') x(i)

select T.data, T3.data3, srt.srtvalue
from @t3 T3
join @t T
ON T3.id = T.id
cross apply(
select
case
when ISNUMERIC(x.i.value('.','varchar(50)')) = 1
then right(replicate('0',@max_len) + x.i.value('.','varchar(50)'),@max_len)
else x.i.value('.','varchar(50)')
end + '.'
from @xml.nodes('/id_root/i') x(i)
where x.i.value('../data3[1]','varchar(50)') = [T3].data3
for xml path('')
) as srt(srtvalue)
order by SubString (data,1 ,CharIndex (' ', data)) ,srt.srtvalue

Result:
data
--------------------------------------------------
Area Lantai 1
Area 2
Rak 1
Rak 1.2
Rak 2
Rak 10
Rak 122

(7 row(s) affected)

Sort Varchar (eq 1.1)

declare @temp table (id varchar(255))

insert into @temp (id) values
('1.1.a.1'),('1.1.aa.2'),
('1.1.b.3'),('1.1.a.4'),
('1.1.a.5'),('1.1.a.6'),
('1.1.a.7'),('1.1.a.8'),
('1.1.a.9'),('21.1.a.10'),
('1.1.a.11'),('1.1.b.1'),
('2.1.b.2'),('1.2.a.1'),
('1.10.a.1'),('1.11.a.1'),
('1.20.a.1'),('101.20.a.2'),
('1.20.a.150'),('1.1'),
('1.2'),('1')

declare @xml xml,
@max_len int

select id as id, cast('' + replace(id,'.','') + '' as xml)
from @temp
for xml path('id_root'),type
set @xml =
(
select id as id, cast('' + replace(id,'.','') + '' as xml)
from @temp
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(10)')))
from @xml.nodes('/id_root/i') x(i)

select @max_len

select [id], srt.srtvalue
from @temp
cross apply(
select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
from @xml.nodes('/id_root/i') x(i)
where x.i.value('../id[1]','varchar(50)') = [@temp].id
for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Result:
id
-------------------------
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
2.1.b.2
21.1.a.10
101.20.a.2

(22 row(s) affected)

Sort VARCHAR on VARCHAR coloum (varchar + int)

SET NOCOUNT ON

Declare @Table table
(
Id INT Identity (1, 1),
StringValue VarChar (30)
)

INSERT INTO @Table (StringValue) VALUES ('CAR 10')
INSERT INTO @Table (StringValue) VALUES ('CAR 20')
INSERT INTO @Table (StringValue) VALUES ('CAR 2')
INSERT INTO @Table (StringValue) VALUES ('CAR 3')
INSERT INTO @Table (StringValue) VALUES ('CAR 4')

INSERT INTO @Table (StringValue) VALUES ('SHIP 32')
INSERT INTO @Table (StringValue) VALUES ('SHIP 310')
INSERT INTO @Table (StringValue) VALUES ('SHIP 320')
INSERT INTO @Table (StringValue) VALUES ('SHIP 33')
INSERT INTO @Table (StringValue) VALUES ('SHIP 34')

SELECT Id,
SubString (StringValue, 1, CharIndex (' ', StringValue)) ObjectName,
CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue))) ObjectId
FROM @Table
ORDER BY 2, 3

SELECT Id, StringValue
FROM @Table
ORDER BY
SubString (StringValue, 1, CharIndex (' ', StringValue)),
CONVERT (INT, SubString (StringValue, CharIndex (' ', StringValue), LEN (StringValue)))

Thursday 22 September 2011

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

Friday 16 September 2011

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;

Thursday 15 September 2011

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

Thursday 25 August 2011

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

Tuesday 23 August 2011

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;

Friday 19 August 2011

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

Windows 7 OEM Theme Pack

Download Link [mediafire]

Advanced System Care Pro 3.7.2

This easy-to-use and informative application cleans, configures, and optimizes your PC. Advanced WindowsCare Pro's interface dispenses with the bells and whistles to display commands and data without embellishment.
The install Wizard zips through the set up, querying the user on the PC's primary use and Internet connection type. Answer those two simple questions, choose from a short list of options, and you're ready to virus check and scan for spyware, registry errors, and start-up problems, or clean your PC of history and surfing traces. The app's strength is the detailed information the scan provides, which lists each item with a description of its value based on trustworthiness. Unknown items can be identified online or ignored. The Tools menu checks five primary areas of your PC for rogue processes, services, and incorrectly installed options.
Since registry entries are involved, we wish the fixes recommended were more detailed. The program includes a quick restore point option for system safety, but repairs can't be made in the trial edition, drastically circumscribing the app's effectiveness unless you buy it sight unseen. The app's uncomplicated and straightforward usage is appreciated, but the since the main selling point has been cut off at the knees, we're hesitant to give this a more glowing recommendation. ( Seth Rosenblatt )

[Mediafire]

Internet Download Manager v6.07 Build 5

New! Internet Download Manager v6.07. Fixed compatibility problems with different browsers including Internet Explorer 9 Final, Mozilla Firefox 4, Mozilla Firefox 5 and Mozilla Firefox 6, Google Chrome.

Improved FLV grabber to save videos from web players on YouTube, WOW! Google Video, MySpace TV, and other popular sites.

Credit to: xpertz@syok.org

Download Link [Mediafire]

Split and Join Tools

Some movie or PV are splited. You can join it using this tools.

HJSPLIT

7ZIP

Xilisoft Video Converter Ultimate 6.0.3 Full Version

Xilisoft Video Converter Ultimate 6 can convert between almost all video and audio formats including AVI, MPEG, WMV, DivX, MP3, WAV, even HD videos. It supports various preset profiles for iPod, PSP, and other digital devices. It even supports outputting lossless FLAC/WAV/WMA audio. You can set multiple output formats simultaneously from the same source for output to save more time.

Supports adding video effects: you can adjust subtitle, add watermark; you can preview video customized, or compare source video with it. Offer handy clip, split, and merge functions: you can even select some clips from a source file and change their sequences, and merge; all actions can be done by drag and drop.

Download Link [Mediafire]

Wednesday 17 August 2011

[E-book] Cooking The Japanese Way

Easy Menu Ethnic Cookbooks

Download Link [Mediafire]

[E-book] Making Out In Japanese

Download Link [Mediafire]

[E-book] Learning Hiragana Katakana

There is some link to download e-book about learning Hiragana Katakana.

Remembering The Kana

Teach Yoursefl Japanese Script

[E-book] Vocab U Learn

Textbook Level 1 - 3

[Video] Nihongo Dekimasu - Erin ga Choosen

[e-book] Minna no Nihongo II

Download from divxupfile

TextBook :

e-book

Audio CD :

CD 1 | CD 2 | CD 3 | CD 4

Choukai CD 1 | Choukai CD 2

Video :

Kaiwa 39-48

[Software] WinDjView

Some E-book maybe use DJVU Files. To open those files you need WinDjView. It doesn't need to be install. After After opening you can read, print etc... like Arcobat Reader.

You can download it here : sourceforge.net or Megaupload