SQL Server ASP.NET C# Kitabı

Bu blogda doÄŸrudan yeni teknolojileri anlatmak yerine eski ve yeni teknolojileri gözetmeksizin günlük hayatta, projelerde ihtiyaç duyulan yöntemleri, ipuçlarını, konuları, örnekleri, yazılım geliÅŸtirme sürecinde dikkat edilmesi gereken konuları vermeye çalışıyoruz. Yazılım mühendisliÄŸiyle ve C# veya VB.NET programlama dilleriyle ilgili detaylı konuları yakın zamanda çıkacak olan kitaplarımda vermeye çalıştım. Bu küçük yazıda da özellikle veri ambarı raporlama sistemlerinde iÅŸlerimizi kolaylaÅŸtıracak Tarih tablosunun içeriÄŸini vereceÄŸiz. “Date dimension table” olarak isimlendirilen bu tablo tüm data warehouse sistemlerinde ortak olup sistemin kullanım amacına uygun olarak farklı detaylar içerir. Belli tarih aralığının detaylarını içeren bu tablo çalışma anında tarihsel hesaplamaların neden olacağı performans ve zaman kaybını engeller. Bu tablo aynı zamanda OLTP uygulamalarında lookup olarak ta kullanılabilir.

Örnek verdiğimiz aşağıda tabloda bir tarihin, tarih formatı, hafta ve ay bilgisi, haftanın, ayın ve yılın kaçıncı günü olduğu, haftasonuna denk gelip gelmediği gibi bilgiler verilmektedir.

CREATE TABLE L_Tarih(
	Bugun int NOT NULL, --YYYYMMDD formatında tarih. Tablonun primary key'i.
	FormatliBugun char(10) NULL,--DD.MM.YYYY formatında tarih
	GunAd char(10) NULL,
	AyAd char(10) NULL,
	TarihAd varchar(50) NULL,--'10 Aralık 2007 Pazartesi' gibi
	BuYil char(4) NULL,--Sene bilgisi
	BuAy char(6) NULL,--Yıl ve ay bilgisi. YYYYMM
	BuHafta char(6) NULL,--Yıl ve hafta bilgisi. YYYYWW
	Dun char(10) NULL,
	GecenHaftaBugun char(10) NULL,
	GecenAyBugun char(10) NULL,
	GecenYil char(4) NULL,
	GecenYilBugun char(10) NULL,
	GecenYilBuHafta char(6) NULL,
	GecenYilBuAy char(6) NULL,
	HaftaIlkGun char(10) NULL,--Bugünün bulunduğu haftanın başı
	HaftaSonGun char(10) NULL,--Bugünün bulunduğu haftanın sonu
	AyIlkGun char(10) NULL,
	AySonGun char(10) NULL,
	HaftaGunNo int NULL,--Haftanın kaçıncı günü
	AyGunNo int NULL,--Ayın kaçıncı günü
	YilGunNo int,--Yılın kaçıncı günü
	AyGunSayi int NULL,
	YilIlkGun char(10) NULL,
	YilSonGun char(10) NULL,
	HaftaSonu bit NULL--Haftasonu olup olmadığını belirtir
)

Åžeması bu ÅŸekilde olan tabloya aÅŸağıdaki script’i çalıştırarak iki tarih aralığındaki tarih detaylarını yazdıralım.

DECLARE @IlkTarih DATETIME
DECLARE @SonTarih DATETIME

SET @IlkTarih = '01-01-2007'
SET @SonTarih = '31-12-2008'

DECLARE @Bugun INT, @FormatliBugun CHAR(10), @GunAd CHAR(10), @AyAd CHAR(10),
	@TarihAd VARCHAR(50), @BuYil CHAR(4), @BuAy CHAR(6), @BuHafta CHAR(6),
	@Dun CHAR(10), @GecenHaftaBugun CHAR(10), @GecenAyBugun CHAR(10),
	@GecenYil CHAR(4), @GecenYilBugun CHAR(10),@GecenYilBuHafta CHAR(6),
	@GecenYilBuAy CHAR(6),@HaftaGunNo INT, @AyGunNo INT,@YilGunNo INT,
	@HaftaIlkGun CHAR(10), @HaftaSonGun CHAR(10),@AyGunSayi INT,
	@AyIlkGun CHAR(10), @AySonGun CHAR(10), @YilIlkGun CHAR(10),
	@YilSonGun CHAR(10), @HaftaSonu BIT

WHILE @IlkTarih <= @SonTarih
BEGIN
	SET @Bugun=CONVERT(VARCHAR(8), @IlkTarih, 112)
	SET @FormatliBugun=CONVERT(VARCHAR(10), @IlkTarih, 104)
	SET @GunAd=DATENAME(dw, @IlkTarih)
	SET @AyAd=DATENAME(mm, @IlkTarih)
	SET @TarihAd=CAST(DATEPART(dd, @IlkTarih) AS CHAR(2)) +' '+ DATENAME(mm, @IlkTarih) + ' '
			+ CAST(DATEPART(yy, @IlkTarih) AS CHAR(4))+' '+ @GunAd
	SET @BuYil=DATENAME(yy, @IlkTarih)
	SET @BuAy=LEFT(@Bugun,6)
	SET @BuHafta=CAST(DATEPART(ww, @IlkTarih) as CHAR(2))
	--Tek hane ise başına "0" ekleyelim
	SET @BuHafta=@BuYil+REPLICATE('0', 2 - LEN(@BuHafta))+@BuHafta
	SET @Dun= CONVERT(VARCHAR(8),DATEADD(dd,-1, @IlkTarih),112)
	SET @GecenHaftaBugun=CONVERT(VARCHAR(8),DATEADD(dd,-7, @IlkTarih),112)
	SET @GecenAyBugun=CONVERT(VARCHAR(8),DATEADD(mm,-1, @IlkTarih),112)
	SET @GecenYil=CONVERT(VARCHAR(8),DATEADD(yy,-1, @IlkTarih),112)
	SET @GecenYilBugun=CONVERT(VARCHAR(8),DATEADD(dd,-365, @IlkTarih),112)
	SET @GecenYilBuHafta=CAST(DATEPART(ww, @GecenYilBugun) as CHAR(2))
	--Tek hane ise başına "0" ekleyelim
	SET @GecenYilBuHafta=@GecenYil+REPLICATE('0', 2 - LEN(@GecenYilBuHafta))+@GecenYilBuHafta
	SET @GecenYilBuAy=LEFT(@GecenYilBugun,6)
	--Tarihin bulunduğu haftanın ilk günü
	SET @HaftaIlkGun=CONVERT(VARCHAR(8),DATEADD(dd,-(DATEPART(dw, @IlkTarih) - 1),@IlkTarih),112)
	--Tarihin bulunduğu haftanın son günü
	SET @HaftaSonGun=CONVERT(VARCHAR(8),DATEADD(dd,-(DATEPART(dw, @IlkTarih) - 7),@IlkTarih),112)
	--Ayın ilk günü
	SET @AyIlkGun=@BuAy+'01'
	--Ayın son günü
	SET @AySonGun=CONVERT(VARCHAR(8),DATEADD(d, -DAY(DATEADD(m,1,@IlkTarih)),DATEADD(m,1,@IlkTarih)),112)
	SET @YilIlkGun=@BuYil+'0101'
	SET @YilSonGun=@BuYil+'0131'
	--Ayın kaç gün olduğunu hesaplayalım
	SET @AyGunSayi=DAY(DATEADD(d, -DAY(DATEADD(m,1,@IlkTarih)),DATEADD(m,1,@IlkTarih)))
	--Tarihin haftanın kaçıncı günü olduğunu hesaplayalım
	SET @HaftaGunNo=DATEPART(dw , @IlkTarih)
	--Tarihin ayın kaçıncı günü olduğunu hesaplayalım
	SET @AyGunNo=DATEPART(day , @IlkTarih)
	--Tarihin yılın kaçıncı günü olduğunu hesaplayalım
	SET @YilGunNo=DATEPART(dy , @IlkTarih)

	--Tarihin haftasonu olup olmadığını bulalım
	SET @HaftaSonu= CASE WHEN @HaftaGunNo IN (6,7) THEN 1 ELSE 0 END

	--Elde ettiğimiz bilgileri lookup tablosuna aktaralım
	INSERT L_Tarih (Bugun, FormatliBugun, GunAd, AyAd, TarihAd,
			BuYil,BuAy,BuHafta,Dun,GecenHaftaBugun,GecenAyBugun,
			GecenYil,GecenYilBugun,GecenYilBuHafta,GecenYilBuAy,
			HaftaIlkGun,HaftaSonGun,AyIlkGun,AySonGun,HaftaGunNo,
			AyGunNo,YilGunNo,AyGunSayi,YilIlkGun,YilSonGun,HaftaSonu)
	VALUES(@Bugun, @FormatliBugun, @GunAd, @AyAd, @TarihAd,
			@BuYil,@BuAy,@BuHafta,@Dun,@GecenHaftaBugun,@GecenAyBugun,
			@GecenYil,@GecenYilBugun,@GecenYilBuHafta,@GecenYilBuAy,
			@HaftaIlkGun,@HaftaSonGun,@AyIlkGun,@AySonGun,@HaftaGunNo,
			@AyGunNo,@YilGunNo,@AyGunSayi,@YilIlkGun,@YilSonGun,@HaftaSonu)

	--Döngü içerisinde bir sonraki güne geçelim
	SET @IlkTarih = DATEADD(dd, 1, @IlkTarih)
END

Bu scriptleri dili Türkçe olan bir kullanıcıyla çalıştırdığımızda tarih tablosu Türkçe formatına göre oluşturulur. L_Tarih tablosuna 2007 ve 2008 yıllarına ait tarih bilgileri eklenmiş olur.

Bu tablo üzerinde bazı sorgular çalıştıralım. 2008 yılında hangi günden kaç adet bulunduğunu sorgulayalım.

SELECT Max(HaftaGunNo) [Gün Numarası],GunAd [Gün Adı], COUNT(*) Adet
FROM L_Tarih WHERE BuYil = 2008
GROUP BY GunAd ORDER BY 1

Görüldüğü gibi 2008 yılında 53 adet Salı ve Çarşamba günü bulunmaktadır. Aynı şekilde 2008 yılında ayların kaçar güne sahip olduğunu sorgulayalım.

SELECT BuAy [Ay], Max(AyAd) [Ay Adı], COUNT(Bugun) [Gün Sayısı]
FROM L_Tarih WHERE BuYil = 2008
GROUP BY BuAy ORDER BY 1

Bu tür tablolar OLAP uygulamalarında özetleme seviyeleri olarak kullanılır. Örneğin özellikle reel büyüme değerleri için haftalık, aylık, yıllık karşılaştırmalar herhangi bir tarih hesaplaması yapılmadan kolayca buradaki veriler kullanılabilir. Örneğin satış, sipatiş tablolarıyla JOIN edilerek farklı zaman dilimine göre satış veya sipariş analizleri yapılabilir.