Posts

Posted on

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…

Posted on

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?

Posted on

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')

Posted on

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,
@DLAST_MONTHLY AS date,
@ILAST_MONTH AS int,
@IMONTHLY_DAY AS int,
@DTODAY AS date,
@IYEARDIFF AS int,
@IMONTHDIFF AS int,
@IDAYDIFF AS int

SET @DTODAY = GETDATE()
SET @DDATE = DATEFROMPARTS(@IYEAR, @IMONTH, @IDAY)

SET @ILAST_MONTH = MONTH(DATEADD(MM, -1, @DTODAY))
SET @IMONTHLY_DAY = CASE WHEN @ILAST_MONTH = 2 AND DAY(@DDATE) > 28 THEN 28
WHEN @ILAST_MONTH IN (4,6,9,11) AND DAY(@DDATE) > 30 THEN 30
ELSE DAY(@DDATE) END
SET @DLAST_MONTHLY = DATEFROMPARTS(YEAR(@DTODAY), @ILAST_MONTH, @IMONTHLY_DAY)

SET @IYEARDIFF = DATEDIFF(YY, @DDATE, @DTODAY)
- CASE WHEN DATEADD(YY, DATEDIFF(YY, @DDATE, @DTODAY), @DDATE) > @DTODAY THEN 1 ELSE 0 END
SET @IMONTHDIFF = (DATEDIFF(MM, @DDATE, @DTODAY)
- CASE WHEN DATEADD(MM, DATEDIFF(MM, @DDATE, @DTODAY), @DDATE) > @DTODAY THEN 1 ELSE 0 END) % 12
SET @IDAYDIFF = DATEDIFF(DD, @DLAST_MONTHLY, @DTODAY)
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 @DLAST_MONTHLY AS date,
@ILAST_MONTH AS int,
@IMONTHLY_DAY AS int,
@DTODAY AS date,
@IRETURN AS int
 
SET @DTODAY = GETDATE()

SET @ILAST_MONTH = MONTH(DATEADD(MM, -1, @DTODAY))
SET @IMONTHLY_DAY = CASE WHEN @ILAST_MONTH = 2 AND DAY(@DDATE) > 28 THEN 28
WHEN @ILAST_MONTH IN (4,6,9,11) AND DAY(@DDATE) > 30 THEN 30
ELSE DAY(@DDATE) END
SET @DLAST_MONTHLY = DATEFROMPARTS(YEAR(@DTODAY), @ILAST_MONTH, @IMONTHLY_DAY)

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 = DATEDIFF(DD, @DLAST_MONTHLY, @DTODAY)
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

Posted on

Chillax

verb (intransitive) slang
to take rest or recreation, as from work or effort
origin: chill + relax

(tʃɪˈlæks)

Collins English Dictionary. Copyright © HarperCollins Publishers

Posted on

Mediocracy

(ˌmiːdɪˈɒkrəsɪ)
noun plural -cies

rule by mediocre people

Collins English Dictionary. Copyright © HarperCollins Publishers