WordPress Block Editor

This is just about testing block editor… Ignore it if you don’t find it interesting…

#shortcode
println(“Hello world!”)

“Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit…”

“There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain…”

Column 1: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur vitae nisl ut enim rhoncus dictum in et ex. Aliquam et sem ligula. Vivamus at porta magna. Integer pharetra ornare luctus. Morbi efficitur, dolor non tempus pharetra, ante nulla vestibulum nunc, sit amet semper odio ante a mauris. Nam rhoncus, nisi eu vehicula fermentum, mauris eros blandit arcu, id tincidunt augue justo ac velit. Donec accumsan scelerisque est. Etiam imperdiet quam facilisis faucibus dapibus. Mauris nec diam justo. Integer imperdiet orci in est congue, mattis aliquam ex gravida. Aliquam in malesuada justo, at ullamcorper sapien. Donec consequat luctus posuere. Ut vitae sodales felis.

Column 2: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur vitae nisl ut enim rhoncus dictum in et ex. Aliquam et sem ligula. Vivamus at porta magna. Integer pharetra ornare luctus. Morbi efficitur, dolor non tempus pharetra, ante nulla vestibulum nunc, sit amet semper odio ante a mauris. Nam rhoncus, nisi eu vehicula fermentum, mauris eros blandit arcu, id tincidunt augue justo ac velit. Donec accumsan scelerisque est. Etiam imperdiet quam facilisis faucibus dapibus. Mauris nec diam justo. Integer imperdiet orci in est congue, mattis aliquam ex gravida. Aliquam in malesuada justo, at ullamcorper sapien. Donec consequat luctus posuere. Ut vitae sodales felis.

Column 3: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur vitae nisl ut enim rhoncus dictum in et ex. Aliquam et sem ligula. Vivamus at porta magna. Integer pharetra ornare luctus. Morbi efficitur, dolor non tempus pharetra, ante nulla vestibulum nunc, sit amet semper odio ante a mauris. Nam rhoncus, nisi eu vehicula fermentum, mauris eros blandit arcu, id tincidunt augue justo ac velit. Donec accumsan scelerisque est. Etiam imperdiet quam facilisis faucibus dapibus. Mauris nec diam justo. Integer imperdiet orci in est congue, mattis aliquam ex gravida. Aliquam in malesuada justo, at ullamcorper sapien. Donec consequat luctus posuere. Ut vitae sodales felis.

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,
@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

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