Halâ Tasarım Aşamasında !
.com
Türkçe, Delphi ve SQL konusundaki deneyimler, örnek kodlar, eleştiri ve düşünceler...

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]

Hatırlatma

Bu sayfa test aşamasında olup deneysel veriler içermektedir.

Cıvıltılar

  • Yüzyılın Soykırımı
  • Sparkfun elektronik 7 Ocak 2010'da 1000 kişiye 100$ değerinde hediye dağıtacakmış...
  • Muharrem Ankara'da işbaşı yaptı, kendisine başarılı ve müreffeh bir iş hayatı diliyoruz :)
  • Erhan'a da huzurlu ve mutlu bir ömür diliyoruz.
  • MikroPascal'ın LCD kütüphanesinde ne tür bir sorun olabilir AÇAPAAAA! Nerede bu kodlar, nerede bu kaynak kodlar !!!
  • Allah'ım sana şükürler olsun, bugünleri de gördük.
  • BAYRAMINIZ KUTLU OLSUN :)
  • Yarın bayram, erken kalkın çocuklar :)
  • "İndiririz biz onun havasını" zihniyetini kınadık...

Sayfa Seç

1 2 3 4 5 6 7 8 9 10 11 12
Toplam 12 sayfa var. Siz 1. sayfadasınız ve 36 kayıt içinden 1 ile 3 arasını görmektesiniz

Yazı Miktarı

Bu sayfada kaç adet yazı görmek istiyorsunuz? Aşağıdakilerden birini seçiniz
1 2 3 5 10 15 20 30 50 75 100 200 300