|
SQL as breakfast, BI for lunch and dinner with MDX
April 2009 - Posts
-
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.
|
-
Seringkali saya menghadapi kasus pembuatan report untuk menampilkan data 1 bulan , 1 kuarter atau 1 tahun dan berbagai macam variasinya. Untuk menyelesaikannya diperlukan script untuk mendapatkan range tanggal sesuai dengan periode yang diminta. Beberapa baris script di bawah ini adalah beberapa koleksi saya ketika membuat report menggunakan periode-periode tersebut.
DECLARE @pDate datetime
SET @pDate='20090417'
DECLARE @startMonth datetime,@endMonth datetime,@startPrevMonth datetime,@endPrevMonth datetime,@startNextMonth datetime,@endNextMonth 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 @startNextMonth=dateadd(month,1,@startMonth)
SET @endNextMonth=dateadd(day,-1,dateadd(month,1,@startNextMonth))
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'))
SET @startPrevYTM=datename(year,dateadd(year,-1,@pDate))+'-01-01'
SET @endPrevYTM=dateadd(day,-1,dateadd(month,1,datename(year,dateadd(year,-1,@pDate))+'-'+datename(month,dateadd(year,-1,@pDate))+'-01'))
SET @startPrevMoY=dateadd(year,-1,@startMonth)
SET @endPrevMoY=dateadd(day,-1,dateadd(month,1,@startPrevMoY)) Keterangan: - @startMonth,@endMonth ==> untuk mendapatkan range tanggal 1 bulan - @start[Prev|Next]Month,@end[Prev|Next]Month ==> untuk mendapatkan range tanggal bulan sebelum/berikutnya - @startQTM,@endQTM,@startPrevQTM,@endPrevQTM ==> untuk mendapatkan range tanggal awal kuarter ini/sebelumnya sampai tanggal terakhir bulan. Contoh : jika @pDate='20090523' maka @startQTM='20090401' dan @endQTM='20090531' - @startYTM,@endYTM,@startPrevYTM,@endPrevYTM ==> untuk mendapatkan range tanggal awal tahun ini/sebelumnya sampai tanggal terakhir
bulan. Contoh : jika @pDate='20090523' maka @startYTM='20090101' dan
@endYTM='20090531' - @startPrevMoY,@endPrevMoY ==> untuk mendapatkan range tanggal bulan yang sama pada tahun sebelumnya.
|
More Posts
|
|
|