Dalam tulisan kali ini saya ingin berbagi pengalaman saya tentang penggunaan CTE. dan pengalaman ini membuat saya CTE minded ...

I love CTE 

Sebelumnya saya sudah mengajarkan jurus ini kepada beberapa teman. Pada umumnya mereka bilang jurus ini ampuh. Tapi bagi saya jurus ini MTV Ampuh Banget.

Saya akan berbagi tentang jurus ini dengan menggunakan skenario pembuatan report untuk data penjualan.Pada report ini kita akan melihat penjualan untuk 1 bulan, penjualan pada bulan sebelumnya, penjualan dari awal kuartal sampai bulan sekarang, penjualan dari kuartal sebelumnya, dan penjualan dari awal tahun sampai bulan skarang.

Supaya lebih sederhana dan mudah dimengerti saya buat tabel Sales dengan 3 kolom saja yaitu Product, SalesDate dan Quantity. Berikut script untuk tabelnya:

CREATE TABLE #Sales (
    Product varchar(20),
    SalesDate datetime,
    Quantity int
)

Setelah itu kita buat beberapa data dummy data untuk table tersebut

INSERT INTO #Sales
    SELECT 'CD',cast('20080101' AS datetime),1 UNION
    SELECT 'DVD','20080102',1 UNION
    SELECT 'Flash Disk','20080103',1 UNION
    SELECT 'Hard Disk','20080104',1 UNION

    SELECT 'CD','20080201',2 UNION
    SELECT 'DVD','20080202',2 UNION
    SELECT 'Flash Disk','20080203',2 UNION
    SELECT 'Hard Disk','20080204',2 UNION

    SELECT 'CD','20080301',3 UNION
    SELECT 'DVD','20080302',3 UNION
    SELECT 'Flash Disk','20080303',3 UNION
    SELECT 'Hard Disk','20080304',3 UNION

    SELECT 'CD','20080401',4 UNION
    SELECT 'DVD','20080402',4 UNION
    SELECT 'Flash Disk','20080403',4 UNION
    SELECT 'Hard Disk','20080404',4 UNION

    SELECT 'CD','20080501',5 UNION
    SELECT 'DVD','20080502',5 UNION
    SELECT 'Flash Disk','20080503',5 UNION
    SELECT 'Hard Disk','20080504',5

Skarang kita sudah punya sumber data. Berikutnya adalah bagian query untuk reportnya. Disinilah saya akan menunjukkan cara saya memanfaatkan CTE yang ada pada SQL Server 2005 ke atas. Pada query ini saya menggunakan range periode bulan,kuartal dan tahun seperti pada postingan saya sebelumnya. Dan querynya seperti ini :

DECLARE @pRequestPeriod char(6)
SET @pRequestPeriod='200805'

DECLARE @pDate datetime
SET @pDate=@pRequestPeriod+'01'

DECLARE @startMonth datetime,@endMonth datetime,@startPrevMonth datetime,@endPrevMonth datetime,
    @startQTM datetime,@endQTM datetime,@startPrevQTM datetime,@endPrevQTM datetime,
    @startYTM datetime,@endYTM datetime,@startPrevYTM datetime,@endPrevYTM datetime,@startPrevMoY datetime,@endPrevMoY datetime;

SET @startMonth=datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'
SET @endMonth=dateadd(day,-1,dateadd(month,1,@startMonth))
SET @startPrevMonth=datename(year,dateadd(month,-1,@pDate))+'-'+datename(month,dateadd(month,-1,@pDate))+'-01'
SET @endPrevMonth=dateadd(day,-1,@startMonth)

SET @startQTM=datename(year,@pDate)+'-'+cast(((datepart(month,@pDate)-1)/3)*3+1 AS varchar(2))+'-01'
SET @endQTM=dateadd(day,-1,dateadd(month,1,datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'))
SET @startPrevQTM=datename(year,dateadd(month,-3,@pDate))+'-'+cast(((datepart(month,dateadd(month,-3,@pDate))-1)/3)*3+1 AS varchar(2))+'-01'
SET @endPrevQTM=dateadd(day,-1,dateadd(month,1,datename(year,dateadd(month,-3,@pDate))+'-'+datename(month,dateadd(month,-3,@pDate))+'-01'))

SET @startYTM=datename(year,@pDate)+'-01-01'
SET @endYTM=dateadd(day,-1,dateadd(month,1,datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'))

--disinilah CTE beraksi

;WITH
Sales(Product,SalesDate,Quantity) AS (
    SELECT * FROM #Sales
),
SalesMonth(Product,MonthQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startMonth AND @endMonth
    GROUP BY Product
),
SalesPrevMonth(Product,PrevMonthQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startPrevMonth AND @endPrevMonth
    GROUP BY Product
),
SalesQTM(Product,QTMQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startQTM AND @endQTM
    GROUP BY Product
),
SalesPrevQTM(Product,PrevQTMQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startPrevQTM AND @endPrevQTM
    GROUP BY Product
),
SalesYTM(Product,YTMQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startYTM AND @endYTM
    GROUP BY Product
),
Result(Product,MonthQuantity,PrevMonthQuantity,QTMQuantity,PrevQTMQuantity,YTMQuantity) AS (
    SELECT a.Product,
    b.MonthQuantity,c.PrevMonthQuantity,d.QTMQuantity,e.PrevQTMQuantity,f.YTMQuantity
    FROM (SELECT DISTINCT Product FROM Sales) a
        LEFT JOIN SalesMonth b ON a.Product=b.Product
        LEFT JOIN SalesPrevMonth c ON a.Product=c.Product
        LEFT JOIN SalesQTM d ON a.Product=d.Product
        LEFT JOIN SalesPrevQTM e ON a.Product=e.Product
        LEFT JOIN SalesYTM f ON a.Product=f.Product
)

SELECT * FROM Result

Output:

Gmn? Bingung kan  ... Intinya adalah CTE disini berfungsi seperti temporary table, hanya saja kita lebih mudah untuk membaca querynya karena code relatif lebih sedikit dibandingkan dengan menggunakan temporary table dengan mendeklarasikannya terlebih dahulu. Coba anda bayangkan jika SalesMonth,SalesPrevMonth,SalesQTM,SalesPrevQTM,dan Sales YTM dibuat menggunakan temporary variabel, untuk membuat coding mudah terbaca anda pasti membutuhkan lebih dari 2x jumlah baris code jika anda tidak menggunakan CTE. Inilah mengapa saya *** CTE karena banyak kasus yang jauh lebih rumit dari yang ini dapat diselesaikan dengan coding yang sedikit dan mudah dibaca.