T-SQL ile Dakikayı Metne Çevirmek
- 23 Kasım 2009 Pazartesi
- Uğur Parlayan
Bazı işleri veritabanına yaptırmak uygulamalara zaman kazandırabiliyor... Veriler üzerinde süre hesaplamak da bunların arasında sayılabilir, çünkü uygulama üzerinde süre hesaplamaktansa veritabanı tablosunu çekerken sürenin hesaplanmış olarak gelmesi hem hız, hem de performans açısından daha kârlıdır. Bununla birlikte bu tür işlere giriştiğinizde sunucunuzun da güçlü olması sıkıntılarınızı azaltabilir...
Aşağıda tanıtacağım kod, dakika (tamsayı) cinsinden verilen bir süreyi "Yıl + Ay + Gün + Saat + Dakika" cinsinden yazabilmenizi sağlıyor. Bir nevi dakikayı okunabilir bir metne çeviriyor. Kıytırık bir iş gibi gözükebilir fakat bu tür küçük şeylerle uğraşmak büyük sistemlerde kodların okunabilirliğini ve hataların çabuk giderilmesini sağlaması açısından gayet faydalıdır.
Şu an kullanmakta olduğumuz takvim konusundaki bilgiyi vikipedi'den edinebilirsiniz. Gevelemeden koda geçelim;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/********************************************************************************\
|* Dakika cinsinden süreyi metne çevirir. Küllim beleş ve paylaşıma açıktır... *|
|* Uğur PARLAYAN © 23.11.2009 Pazartesi *|
\********************************************************************************/
ALTER FUNCTION [dbo].[FN_STR_SureYaz] ( @Parametre INT )
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @Sonuc VARCHAR(MAX)
, @ToplamDakika INT
, @ArtanDakika INT
, @ToplamSaat INT
, @ArtanSaat INT
, @ToplamGun INT
, @ArtanGun INT
, @ToplamAy INT
, @ArtanAy INT
, @ToplamYil INT
SELECT @ToplamDakika = ISNULL(@Parametre, 0) /* Tutarlılık için küçük bir önlem :) */
, @ArtanDakika = @ToplamDakika % 60
, @ToplamSaat =(@ToplamDakika - @ArtanDakika) / 60
, @ArtanSaat = @ToplamSaat % 24
, @ToplamGun =(@ToplamSaat - @ArtanSaat) / 24
, @ArtanGun = @ToplamGun % 30.436875
, @ToplamAy =(@ToplamGun - @ArtanGun) / 30.436875
, @ArtanAy = @ToplamAy % 12
, @ToplamYil =(@ToplamAy - @ArtanAy) / 12
SELECT
@Sonuc = CASE WHEN @Parametre<=0 THEN 'yok'
ELSE CASE WHEN @ToplamYil > 0 THEN CAST(@ToplamYil AS VARCHAR)+' yıl ' ELSE '' END
+ CASE WHEN @ArtanAy > 0 THEN CAST(@ArtanAy AS VARCHAR)+' ay ' ELSE '' END
+ CASE WHEN @ArtanGun > 0 THEN CAST(@ArtanGun AS VARCHAR)+' gün ' ELSE '' END
+ CASE WHEN @ArtanSaat > 0 THEN CAST(@ArtanSaat AS VARCHAR)+' saat ' ELSE '' END
+ CASE WHEN @ArtanDakika>0 THEN CAST(@ArtanDakika AS VARCHAR)+' dk' ELSE '' END
END
RETURN (RTRIM(@Sonuc))
END
Şimdi bir de bu fonksiyonun nasıl kullanıldığına dair kısa ve öz bir kaç örnek verelim...
/* Mesela 1 Ocak 1753'den şimdiye kadar olan süreyi yazdıralım */
SELECT dbo.FN_STR_SureYaz(DATEDIFF(minute,'17530101',GETDATE())) as [Geçen Süre]
/* Veya belli iki tarih arasındaki süreyi yazdıralım */
SELECT dbo.FN_STR_SureYaz(DATEDIFF(minute,'20090101','20091010')) as [Geçen Süre]
Geçen Süre
---------------------------------
256 yıl 7 ay 22 gün 14 saat 12 dk
(1 row(s) affected)
Geçen Süre
---------------------------------
9 ay 7 gün
(1 row(s) affected)
T-SQL ile Metinleri Parçalarına Ayrıştırmak
- 01 Eylül 2009 Salı
- Uğur Parlayan
Pascal ile metinleri en küçük parçasına ayrıştırmak başlıklı yazımda Parse işlemine yönelik bir teknikten bahsetmiştim. İlgili yazıya şuradan ulaşabilirsiniz. Aynı konuyu SQL'de nasıl yaparız sorusunu bazı arkadaşlar sormaya başlamış olabilir. Bu konudaki merakı gidermek adına doğrudan bir Kullanıcı Tanımlı Fonksiyon (UDF) tanımlayıp kaynak kodunu aşağıda veriyoruz. Gerekli açıklamaları kod içerisinden takip edebilirsiniz.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*********************************************************/
/* */
/* Bu fonksiyon, verilen bir metni kelimelerine ayrış- */
/* tırır ve sonucu bir tablo olarak kullanıcıya sunar. */
/* */
/* Uğur PARLAYAN */
/* */
/*********************************************************/
ALTER FUNCTION [dbo].[FN_TBL_KelimeListesi] (
@Parametre VARCHAR(MAX)
, @Ayraclar VARCHAR(100)
)
RETURNS @Sonuc TABLE (
[Sıra] INT IDENTITY(1,1)
, [Boy] INT DEFAULT((0))
, [Ayrac] VARCHAR(1) DEFAULT('')
, [Kelime] VARCHAR(MAX) DEFAULT('')
) AS BEGIN
DECLARE @Basamak INT
, @Boy INT
, @Ayrac VARCHAR(1)
, @Kelime VARCHAR(MAX)
, @Paragraf VARCHAR(MAX)
SELECT @Basamak = 1
, @Ayraclar = ISNULL(@Ayraclar, '%[ ,.:; '
+ CHAR(10)+CHAR(13)
+ '!?"()<&>={}\/*+`_-]%')
, @Paragraf = ISNULL(@Parametre, '')
WHILE @Basamak <> 0 BEGIN
/* En soldan başlayıp ilk ayracımızın hangi basamakta olduğunu buluyoruz */
/* Ardından bu haltı hangi ayraç yemiş onu buluyoruz */
SELECT @Basamak = PATINDEX(@Ayraclar, @Paragraf)
, @Ayrac = SUBSTRING(@Paragraf, @Basamak, 1)
/* Kelimeyi Soldan ayraca kadar alıyoruz */
IF (@Basamak <> 0) SELECT @Kelime = RTRIM(LTRIM(LEFT(@Paragraf, @Basamak - 1)))
ELSE SELECT @Kelime = RTRIM(LTRIM(@Paragraf))
/* Kelimenin boyunu posunu ölçüyoruz */
SET @Boy = LEN(@Kelime)
IF (@Boy > 0) BEGIN
/* Kelimemizi ve boyunu posunu tablomuza yazıyoruz */
INSERT INTO
@Sonuc ( Kelime
, Boy
, Ayrac
)
VALUES ( @Kelime
, @Boy
, @Ayrac
)
END
/* Aldığımız kelimeyi paragraftan siliyoruz */
SELECT @Paragraf = RIGHT(@Paragraf,LEN(@Paragraf) - @Basamak)
/* Paragrafta kelime kalmadıysa döngüden kaçıyoruz */
IF LEN(@Paragraf) = 0 BREAK
END
RETURN
END
Bu kodu kullanabilmek için ise şöyle bir SQL cümlesi kullanmamız yeterli;
SELECT *
FROM dbo.FN_TBL_KelimeListesi('Test,amaçlı!deneysel&veriler.', NULL)
Yukarıdaki sorgumuz ise şöyle bir sonuç tablosu üretecektir;
Sıra Boy Ayrac Kelime
----------- ----------- ----- ----------------------
1 4 , Test
2 6 ! amaçlı
3 8 & deneysel
4 7 . veriler
T-SQL ile Sonuçlarda Sorgulamak
- 01 Eylül 2009 Salı
- Uğur Parlayan
Şu SQL ne menem bir dil yeni yeni kavramaya başlıyorum... Bazen öyle durumlarla karşılaşıyoruz ki içinden çıkmak için tam anlamıyla destan yazmak gerekebiliyor. Bu "yazımda!" size T-SQL'in WITH komutununun kullanımına yönelik bir iki ufak örnek göstereceğim;
WITH ile çözeceğiniz sorunları başka yöntemlerle de çözebileceğinizi unutmamanız gerekir, burada amacımız aynı sorunlara farklı yaklaşımlar sergilemekten başka bir şey değil.
Gelelim sadede; WITH, temelde geçiçi bir tablonun sonuç kümesini temsili bir tanımlama ile devamındaki SQL cümlesinde kullanmanıza olanak verir. Bu sayede çok karmaşık olan SQL cümlelerinizi daha okunaklı hale getirir ve olayı basite indirgersiniz. Böylece SQL kodunuzu okuyan diğerleri de olaya çabuk uyum sağlarlar.
"Neden Temp tablo kullanmıyoruz ki?" gibi bir şey sorulabilir; WITH'in kullanımı temp tablodan daha kolaydır çünkü temp tabloyu kullanmak için önce onu tanımlamak, sonra da değerleri o tabloya aktarmak gerekir, bir sürü külfet... Halbuki WITH öyle değildir. WITH'i yazarken en başta bir isim verirsiniz ve sonrasında karmaşık SQL cümlenizi yazarsınız o kadar...
Diyelim ki bir "Kişiler" tablonuz var ve bu tabloda müşterilerinizin kimlik ve ticaret sicil kayıtlarını tutuyorsunuz. Üstelik bu tablonuzdaki bazı alanları diğer tablolarda referans olarak kullanıyor ve muhasebe kayıtlarınızı da bu referanslara atıflar yaparak tutuyorsunuz...
Basit bir örnekle işe başlayalım;
Bu örnekler serisinde önce basit 2 sorgu oluşturacağız, sonra bunları birleştirip tek bir SQL sorgusu haline getireceğiz. Ardından bu tek sorguyu WITH ile temsili bir sabite atayacağız ve en sonunda da o sabiti kullanarak daha karmaşık sorgular yazmaya çalışacağız...
İlk sorgumuz bize, adının ilk harfi 'A','E','I','İ','O','Ö','U','Ü' işaretleri ile başlayan bireylerin listesini versin;
SELECT KS.[Ref]
, KS.[Hesap]
, KS.[Kategori]
, KS.[Tür]
, FR.Hesap AS Firma
, Left(KS.[Hesap],1) AS Harf
FROM dbo.[Kisiler] AS KS
LEFT OUTER JOIN dbo.Kisiler AS FR ON KS.[Bağlı Olduğu Şirket] = FR.[Ref]
WHERE Left(KS.[Hesap],1) in ('A','I','U','Ö')
AND KS.[Tür] in ('Gerçek', 'Personel')
Görüldüğü gibi, yukarıdaki kodumuzda cezbedici hiç bir unsur yok... Olabildiğinde sade bir yapısı var.
Şimdi gelelim ikinci sorgumuza. Olayı bu sefer biraz daha çetrefellendirelim ve ünvanının soldan 3. harfinin 'A','I','U','Ö' işaretlerinden birisi olan "firmaları" listeleyelim;
SELECT KS.[Ref]
, KS.[Hesap]
, KS.[Kategori]
, KS.[Tür]
, FR.Hesap AS Firma
, Right(Left(KS.[Hesap], 3), 1) AS Harf
FROM dbo.[Kisiler] AS KS
LEFT OUTER JOIN dbo.Kisiler AS FR ON KS.[Bağlı Olduğu Şirket] = FR.[Ref]
WHERE Right(Left(KS.[Hesap], 3), 1) in ('A','E','I','İ','O','Ö','U','Ü')
AND KS.[Tür] in ('Tüzel')
Bu kodda da cezbedici herhangi bir unsur yok ama dikkat ederseniz çektiğimiz alan adları aynı ve farklar sadece koşulu belirttiğimiz kısımda. Şimdi bu iki sorguyu kullanarak bileşik bir sorgu cümlesi kuralım;
SELECT KS.[Ref]
, KS.[Hesap]
, KS.[Kategori]
, KS.[Tür]
, FR.Hesap AS Firma
, Left(KS.[Hesap],1) AS Harf
FROM dbo.[Kisiler] AS KS
LEFT OUTER JOIN dbo.Kisiler AS FR ON KS.[Bağlı Olduğu Şirket] = FR.[Ref]
WHERE Left(KS.[Hesap],1) in ('A','I','U','Ö')
AND KS.[Tür] in ('Gerçek', 'Personel')
UNION
SELECT KS.[Ref]
, KS.[Hesap]
, KS.[Kategori]
, KS.[Tür]
, FR.Hesap AS Firma
, Right(Left(KS.[Hesap], 3), 1) AS Harf
FROM dbo.[Kisiler] AS KS
LEFT OUTER JOIN dbo.Kisiler AS FR ON KS.[Bağlı Olduğu Şirket] = FR.[Ref]
WHERE Right(Left(KS.[Hesap], 3), 1) in ('A','E','I','İ','O','Ö','U','Ü')
AND KS.[Tür] in ('Tüzel')
Yaptığımız tek şey, iki sorgunun arasına bir "UNION" ifadesi koymak oldu. İşte WITH, bundan sonra eğer yeni bir koşul eklememiz gerekiyor ve bu bütünlüğü bozmak istemiyorsak devreye giriyor. Şimdi bunu biraz daha zorlaştıralım ve her ikisi için de geçerli olacak yeni bir koşul belirleyelim. Bu öyle bir koşul olsun ki her iki sorgunun da sadece bir kısmını etkileyecek cinsten olsun, veya ortak bir kıstası belirtsin...
İşte WITH'i ilk kez kullanıyoruz;
WITH Osman_Hamdi_Efendi_Sorgusu AS (
SELECT KS.[Ref]
, KS.[Hesap]
, KS.[Kategori]
, KS.[Tür]
, FR.Hesap AS Firma
, Left(KS.[Hesap],1) AS Harf
FROM dbo.[Kisiler] AS KS
LEFT OUTER JOIN dbo.Kisiler AS FR ON KS.[Bağlı Olduğu Şirket] = FR.[Ref]
WHERE Left(KS.[Hesap],1) in ('A','I','U','Ö')
AND KS.[Tür] in ('Gerçek', 'Personel')
UNION
SELECT KS.[Ref]
, KS.[Hesap]
, KS.[Kategori]
, KS.[Tür]
, FR.Hesap AS Firma
, Right(Left(KS.[Hesap], 3), 1) AS Harf
FROM dbo.[Kisiler] AS KS
LEFT OUTER JOIN dbo.Kisiler AS FR ON KS.[Bağlı Olduğu Şirket] = FR.[Ref]
WHERE Right(Left(KS.[Hesap], 3), 1) in ('A','E','I','İ','O','Ö','U','Ü')
AND KS.[Tür] in ('Tüzel')
)
SELECT * FROM Osman_Hamdi_Efendi_Sorgusu
Hepsi bu, ama burada bitmiyor. WITH'den faydalanmayacaksak onu da kullanmamızın bir anlamı yok elbet. Gelin bu örneği biraz daha geliştirelim ve her iki sorgu için de ortak başka bir kriter daha belirtelim; Mesela;
- "FİRMA" alanımız hiç boş değer içermesin,
- "FİRMA" alanı 'A' harfiyle başlayanlar olsun,
- "Kategori" alanımızda sadece "Müvekkil" ve "Karşı Taraf" olsun,
- "Hesap" adı ')' parantezle bitmesin,
- "Tür"ü Personel olan herkes de gözüksün...
bu liste uzar gider, bir yerde dur demek lazım, koda gelince o da aşağıdaki gibi olmalı
WITH ...
...
)
SELECT * FROM Osman_Hamdi_Efendi_Sorgusu
WHERE Firma is not NULL
AND Kategori in ('Müvekkil', 'Karşı Taraf')
OR [Tür] = 'Personel'
Bu örnekte kullandığımız örnek tabloyu oluşturmak için şu kodu kullanabilirsiniz;
CREATE TABLE [dbo].[Kisiler](
[Ref] [int] IDENTITY(1,1) NOT NULL
, [Tür] [varchar](8) COLLATE Turkish_CI_AS NULL
, [Kategori] [varchar](50) COLLATE Turkish_CI_AS NULL
CONSTRAINT [DF_Kisiler_O_Kategori] DEFAULT ((0))
, [Hesap] [varchar](255) COLLATE Turkish_CI_AS NOT NULL
CONSTRAINT [DF_Kisiler_O_HesapAdi] DEFAULT ('Belirtilmemiş')
, [Bağlı Olduğu Şirket] [int] NULL
, CONSTRAINT [PK_Kisiler] PRIMARY KEY CLUSTERED
(
[Ref] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]