Dynamic Pivot in SQL

Berikut adalah alternatif query untuk menampilkan pivot table dari 2 (dua) tabel. Contoh kedua tabel adalah sebagai berikut:

Table_1

kodenama
T1Tunjangan Allowance
T2Tunjangan THR
T3Tunjangan Jabatan

Table_2

kodenilai
T11000
T22000
T3500

Hasil dari pivot yang diinginkan adalah sebagai berikut:

Tunjangan Allowance Tunjangan THR Tunjangan Jabatan
10002000500

Untuk mendapatkan hasil tersebut gunakan query berikut:

DECLARE @CQUERY AS VARCHAR(MAX)
DECLARE @CKODE VARCHAR(50),
@CNAMA VARCHAR(50)
DECLARE C_Cursor INSENSITIVE CURSOR
FOR
SELECT kode, nama
FROM Table_1
SET @CQUERY = 'SELECT '
OPEN C_Cursor
FETCH NEXT FROM C_Cursor INTO @CKODE, @CNAMA
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CQUERY = @CQUERY + 'SUM(CASE WHEN kode = ''' + @CKODE + ''' THEN nilai ELSE 0 END) AS [' + @CNAMA + ']'
FETCH NEXT FROM C_Cursor INTO @CKODE, @CNAMA
IF @@FETCH_STATUS = 0 SET @CQUERY = @CQUERY + ', ' + CHAR(13)
END
CLOSE C_Cursor
DEALLOCATE C_Cursor
SET @CQUERY = @CQUERY + CHAR(13) + 'FROM Table_2 '
EXECUTE(@CQUERY)

Semoga bermanfaat…

Get Last Item Per Group

I have a table A with a trigger for insert to update or insert if not exists, another table B with the last information for a defined combination of a group of foreign key. But, sometimes things aren’t going well and somehow the last information kept in B is not the actual last information inserted in A. So, to fix the data error, I need to get the last record from A for each group of foreign key.

Here’s how (credit to Stackoverflow.com user Bill Karwin in https://stackoverflow.com/questions/1505549/how-to-make-a-sql-query-for-last-transaction-of-every-account):


SELECT A.CAPPS_CODE, A.CCUSTOMER_CODE, A.CSERVER_TYPE, A.CSERVER_UID
FROM LAT_SERVER_REG A
LEFT OUTER JOIN LAT_SERVER_REG B
ON B.CAPPS_CODE = A.CAPPS_CODE
AND B.CCUSTOMER_CODE = A.CCUSTOMER_CODE
AND B.CSERVER_TYPE = A.CSERVER_TYPE
AND B.CREGISTRATION_ID > A.CREGISTRATION_ID
WHERE B.CAPPS_CODE IS NULL

The key is in the last line WHERE B.CAPPS_CODE IS NULL. CREGISTRATION_ID is a record id which hold the information of time in a format of yyyymmdd-hhMMss, so by comparing the field with the other “imaginary” set of records from the same table, we get that the last CREGISTRATION_ID does not have any counterpart record, hence B.CAPPS_CODE IS NULL. Brilliant isn’t it?

Convert Delimited String To Table

If you have a delimited string (usually comma-delimited) in a SQL-based application, sometimes it would be easier to process if it’s converted into table. In this post, I will show you a user-defined table-valued function in SQL that I developed for the purpose mentioned before. The function uses three parameters: the delimited string itself, the delimiter, and sorting order (‘A’ for ascending, ‘D’ for descending, and anything for original).

Here’s the code:

CREATE FUNCTION [dbo].[FT_Delimited_String_To_Table]
(
@CDELIMITED_STRING VARCHAR(MAX),
@CDELIMITER CHAR(1),
@CSORT_BY CHAR(1)
)
RETURNS @EntriesTable TABLE
(IENTRY_NO INT IDENTITY(1,1), CENTRY VARCHAR(MAX))
WITH ENCRYPTION
AS
BEGIN
DECLARE @CENTRY AS VARCHAR(MAX),
@IDELIMITER_INDEX AS INT,
@IDELIMITER_START AS INT
DECLARE @TempEntries AS TABLE (
CENTRY VARCHAR(MAX)
)

SELECT @IDELIMITER_INDEX = 1,
@IDELIMITER_START = 1

WHILE @IDELIMITER_INDEX <= LEN(@CDELIMITED_STRING) BEGIN
SET @IDELIMITER_INDEX = CHARINDEX(@CDELIMITER, @CDELIMITED_STRING, @IDELIMITER_INDEX)
IF @IDELIMITER_INDEX = 0 BEGIN
SELECT @IDELIMITER_INDEX = LEN(@CDELIMITED_STRING) + 1
END
SET @CENTRY = SUBSTRING(@CDELIMITED_STRING, @IDELIMITER_START, @IDELIMITER_INDEX - @IDELIMITER_START)
INSERT INTO @TempEntries VALUES (LTRIM(RTRIM(@CENTRY)))
SELECT @IDELIMITER_INDEX = @IDELIMITER_INDEX + 1
SELECT @IDELIMITER_START = @IDELIMITER_INDEX
END

IF @CSORT_BY = 'A' BEGIN
INSERT INTO @EntriesTable
SELECT CENTRY FROM @TempEntries ORDER BY CENTRY ASC
END
ELSE BEGIN
IF @CSORT_BY = 'D' BEGIN
INSERT INTO @EntriesTable
SELECT CENTRY FROM @TempEntries ORDER BY CENTRY DESC
END
ELSE BEGIN
INSERT INTO @EntriesTable
SELECT CENTRY FROM @TempEntries
END
END


RETURN
END

Now try it with this line:

SELECT * FROM FT_Delimited_String_To_Table('Apple, Cherry, Orange, Blackberry', ',', 'A')

SQL User-defined Function for Calculating Age as of Today

Every now and then we need  an easy way to calculate age of something in a database application, e.g. employee age. Here I propose two SQL user-defined functions that can be used for the job. The first one is a table-valued function, and the second one is scalar-valued.

First function: FT_Age (table-valued)

CREATE FUNCTION [dbo].[FT_Age]
(
@IYEAR INT,
@IMONTH INT,
@IDAY INT
)
RETURNS @AgeTable TABLE
(IYEAR INT, IMONTH INT, IDAY INT)
WITH ENCRYPTION
AS
BEGIN
DECLARE @DDATE AS DATE,
@DNEAREST AS DATE,
@IYEARDIFF AS INT,
@IMONTHDIFF AS INT,
@IDAYDIFF AS INT
SET @DDATE = DATEFROMPARTS(@IYEAR, @IMONTH, @IDAY)
SET @DNEAREST = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(@DDATE))
SET @IYEARDIFF = DATEDIFF(YY, @DDATE, GETDATE())
- CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END
SET @IMONTHDIFF = (DATEDIFF(MM, @DDATE, GETDATE())
- CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END) % 12
SET @IDAYDIFF = CASE WHEN @DNEAREST <= GETDATE() THEN DATEDIFF(DD, @DNEAREST, GETDATE())
ELSE DATEDIFF(DD, DATEADD(MM, -1, @DNEAREST), GETDATE()) END
INSERT INTO @AgeTable VALUES (@IYEARDIFF, @IMONTHDIFF, @IDAYDIFF)
RETURN
END

Usage example:
SELECT * FROM FT_Age(1945, 8, 17)

Second function: FN_Age (scalar-valued)
CREATE FUNCTION [dbo].[FN_Age]
(
@DDATE DATE,
@CDATE_PART CHAR(2)
)
RETURNS INT
WITH ENCRYPTION
AS
BEGIN
DECLARE @DNEAREST AS DATE,
@IRETURN AS INT
SET @DNEAREST = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(@DDATE))
IF @CDATE_PART = 'YY' BEGIN
SET @IRETURN = DATEDIFF(YY, @DDATE, GETDATE())
- CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END
END
IF @CDATE_PART = 'MM' BEGIN
SET @IRETURN = (DATEDIFF(MM, @DDATE, GETDATE())
- CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, GETDATE()), @DDATE) > GETDATE() THEN 1 ELSE 0 END) % 12
END
IF @CDATE_PART = 'DD' BEGIN
SET @IRETURN = CASE WHEN @DNEAREST <= GETDATE() THEN DATEDIFF(DD, @DNEAREST, GETDATE())
ELSE DATEDIFF(DD, DATEADD(MM, -1, @DNEAREST), GETDATE()) END
END

RETURN @IRETURN
END

Usage example:
SELECT RTRIM(CONVERT(CHAR(4), dbo.FN_Age(CONVERT(DATE, '19450817', 112), 'YY'))) + 'y '
+ RTRIM(CONVERT(CHAR(2), dbo.FN_Age(CONVERT(DATE, '19450817', 112), 'MM'))) + 'm '
+ RTRIM(CONVERT(CHAR(2), dbo.FN_Age(CONVERT(DATE, '19450817', 112), 'DD'))) + 'd ' AS Age

Windows Live Writer

Windows Live Writer

Post ini saya tulis dengan Windows Live Writer, memungkinkan saya membuat post tanpa login ke dashboard blog. Diharapkan dengan tools ini bakal lebih rajin posting…

 

Table

Dengan tools ini lebih mudah membuat table, seperti di bawah ini:

Kolom 1 Kolom 2
Data 1 Data 2

Mengubah table properties sangat mudah, cukup klik kanan di table dan pilih properties yang hendak diubah.

 

Text Format

Mengubah format teks pun sangat mudah, semudah menggunakan aplikasi office. Bisa menggunakan toolbar maupun shorcut keys yang umum dipakai seperti Ctrl-B, Ctrl-I, dan sebagainya.

 

Quote

Small opportunities are often the beginning of great enterprises. – Demosthenes

Drawbacks

Sayangnya saya masih menemukan masalah ketika publish. Artikel dapat dipublish tetapi server mengembalikan pesan error, sehingga status di client tetap draft.

Sebagai workaround bisa dengan cara open dari website/blog kemudian setelah setelah penyuntingan selesai publish dan hapus draft.

Picture Editor

Tools ini juga sudah dilengkapi picture editor sederhana yang dilengkapi efek sederhana seperti B/W, sepia, temperature, gaussian blur dan emboss. Logo di atas adalah contoh yang sudah diberi efek tilt dan gaussian blur.

10 prinsip desain yang baik menurut Dieter Rams (Rams’ 10 principles of good design)

Bagi kita yang berkarya atau memiliki perusahaan yang mengembangkan produk sendiri, mengetahui atau mengembangkan wawasan seputar pengembangan produk adalah hal yang penting. Salah satu yang harus kita pelajari adalah mengenai desain produk. Saat ini, desain produk yang baik sangat berpengaruh terhadap persepsi pelanggan atau pengguna, yang pada gilirannya akan mempengaruhi kinerja penjualan produk yang bersangkutan.

Lalu, bagaimana suatu desain dapat dikatakan baik? Apakah ada standardisasi yang bisa menjamin bahwa desain suatu produk akan membantu produk tersebut sukses di pasaran? Mungkin jawabannya adalah tidak ada. Tetapi banyak pendapat-pendapat atau teori-teori yang bisa dijadikan pedoman bagi kita yang ingin mengembangkan produk dengan desain yang bisa dikatakan baik.

Salah satu teori yang bisa digunakan adalah Rams’ 10 principles of good design. Prinsip-prinsip ini dirumuskan oleh Dieter Rams, seorang pakar desain industri berkebangsaan Jerman. Beliau adalah desainer produk-produk buatan Braun. Pemikirannya banyak mempengaruhi desainer-desainer papan atas, salah satunya Jony Ive, direktur desain industri Apple. Apa saja kesepuluh prinsip desain yang baik tersebut?

  1. Inovatif. Desain harus memanfaatkan perkembangan teknologi. Contohnya desain komputer, dulu tidak mungkin mendesain komputer dengan dimensi serba kecil seperti notebook ultra tipis, karena ukuran komponen-komponen di dalamnya seperti keping prosesor atau memori masih besar. Tapi sekarang hal tersebut bisa dilakukan karena perkembangan teknologi memungkinkan membuat keping-keping semikonduktor berukuran kecil.
  2. Menjadikan produk bernilai guna. Produk tidak hanya bermanfaat karena fungsinya, tapi juga ada unsur psikologis dan estetis. Misalnya, kita mendapatkan sebuah mesin pembuat kopi sebagai hadiah ulang tahun. Tetapi karena kita tidak suka warnanya atau bentuknya, mesin itu akhirnya hanya menjadi penghuni lemari gudang. Apakah mesin pembuat kopi itu bisa dikatakan bermanfaat?
  3. Estetis. Sebagaimana sudah dijelaskan di prinsip kedua, nilai estetika suatu desain akan mempengaruhi psikologi pengguna.
  4. Menjadikan produk mudah dipahami. Dengan kata lain, desain produk harus intuitif, produk dapat digunakan dengan penjelasan yang sesedikit mungkin.
  5. Sederhana, memberi kesempatan kepada pengguna untuk berekspresi.
  6. Jujur, tidak berusaha melebih-lebihkan kemampuan atau fitur dari produk yang bersangkutan.
  7. Bertahan lama, tidak mudah ketinggalan jaman.
  8. Sempurna sampai detail terkecil. Kesempurnaan desain adalah penghargaan terhadap pengguna.
  9. Ramah lingkungan. Desain produk harus memperhatikan pengaruh terhadap lingkungan, baik selama masih memiliki nilai guna, maupun setelah tidak berguna lagi (dapat didaur ulang).
  10. Minimalis. Fokus pada aspek-aspek penting saja, jangan membebani produk dengan detil-detil yang tidak perlu.

*Sumber: wikipedia*

Asal-usul Nama Komputer Apple Macintosh

Anda yang bergelut di dunia IT pasti tahu atau pernah mendengar tentang Macintosh atau yang sekarang lebih dikenal dengan Mac, lini produk komputer personal dari Apple. Tapi tahukah Anda dari mana nama tersebut berasal?

Proyek Macintosh dimulai akhir tahun 70-an ketika Jef Raskin, seorang karyawan Apple, bercita-cita membuat komputer murah yang mudah digunakan. Raskin ingin menamai komputer tersebut dengan tipe apel kesukaannya: McIntosh. Apel McIntosh sendiri memperoleh namanya dari sang petani yang menemukannya, yaitu John McIntosh, pada tahun 1811 di Ontario, Kanada. Tetapi komputer tersebut akhirnya dinamai Macintosh karena nama McIntosh sudah digunakan sebagai merek perangkat audio.

Bayangkan kalau Mr. Raskin adalah penggemar Granny Smith…. No, no, no..

(Source: Wikipedia)