Zaman zaman yazilim projelerde tarihlere göre islemler yapariz. Tarih ile ilgili .NET framework zengin bir içerik tasimaktadir ancak bazen bu tür islemleri veritabaninda yapmak gerekebilir. Elbette MS SQL Server da bize tarih islemleri ile ilgili çok sayida fonksiyon sunmaktadir. Iste bunlardan bazilarini kullanarak bize ihtiyacimiz olan herhangi iki tarih araligindaki günleri ve tarihleri, bu tarihlere ait bilgileri verebilen dinamik bir takvim fonksiyonu.
Bu fonksiyona "fun_FreeCalendar" ismini verdim. StartDate ve FinishDate adi altinda iki tane DateTime tipinde parametre bekliyor.
 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fun_FreeCalendar]
(
    @StartDate Datetime,
    @FinishDate Datetime
)
RETURNS @CalendarTable TABLE (
    [Date] Datetime,
    [DayOfWeekName] NVarChar(10),
    [DayOfWeek] Tinyint,
    [DayOfYear] Integer,
    [Year] Integer,
    [Quarter] Integer,
    [Month] Integer,
    [Week] Integer,
    [ISOWeek] Integer,
    [Day] Integer
)
AS
BEGIN

    SET @StartDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
    SET @FinishDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))

    ;WITH CTE_FreeCalendar
    AS
    (
        SELECT @StartDate AS [Date]
            ,DATENAME(dw,@StartDate) AS [DayOfWeekName]
            ,DATEPART(dw,@StartDate) AS [DayOfWeek]
            ,DATEPART(dy,@StartDate) AS [DayOfYear]
            ,DATEPART(yy,@StartDate) AS [Year]
            ,DATEPART(qq,@StartDate) AS [Quarter]
            ,DATEPART(mm,@StartDate) AS [Month]
            ,DATENAME(wk,@StartDate) AS [Week]
            ,DATEPART(ISOWK,@StartDate) AS [ISOWeek]
            ,DATEPART(dd,@StartDate) AS [Day]
        UNION ALL
        SELECT DATEADD(dd, 1, [Date])
            ,DATENAME(dw,DATEADD(dd, 1, [Date]))
            ,DATEPART(dw,DATEADD(dd, 1, [Date]))
            ,DATEPART(dy,DATEADD(dd, 1, [Date]))
            ,DATEPART(yy,DATEADD(dd, 1, [Date]))
            ,DATEPART(qq,DATEADD(dd, 1, [Date]))
            ,DATEPART(mm,DATEADD(dd, 1, [Date]))
            ,DATENAME(wk,DATEADD(dd, 1, [Date]))
            ,DATEPART(ISOWK,DATEADD(dd, 1, [Date]))
            ,DATEPART(dd,DATEADD(dd, 1, [Date]))
        FROM CTE_FreeCalendar
        WHERE DATEADD(dd, 1, [Date]) <= @FinishDate
    )
    INSERT INTO @CalendarTable ([Date],[DayOfWeekName],[DayOfWeek],[DayOfYear],[Year],[Quarter],[Month],[Week],[ISOWeek],[Day])
    SELECT [Date],[DayOfWeekName],[DayOfWeek],[DayOfYear],[Year],[Quarter],[Month],[Week],[ISOWeek],[Day]
    FROM CTE_FreeCalendar
    OPTION (MAXRECURSION 0)

    RETURN
END


GO

Basit olarak söyle kullanilabilir; Burada 30 Haziran 1995 tarihi ile bugün arasindaki tarihlerin tablosunu görebiliriz.

DECLARE @StartDate DATETIME 
DECLARE @EndDate DATETIME 
SET @StartDate = CONVERT(DATETIME,'06/30/1995',101) 
SET @EndDate = GETDATE() 
SELECT * FROM dbo.fun_FreeCalendar(@StartDate,@EndDate)

Örnek çikti ise;

Bu çiktiyi dilediginiz bir tablodaki tarih kolonu ile join yaparak, herhangi bir tarih araliginda tablonuzda bulunmayan kayitlari bulabilirsiniz. Asagidaki örnek sorguda MyTable tablosundaki tüm kayitlar fun_FreeCalendar fonksiyonundan gelen tablodaki Date kolonu ile islestirildi ve bu eslestirmeden haftasonlari çikarildi. Sonuçta MyTable da olmayan veriler NULL gelecektir ve istedigimizi bu sekilde bulabiliriz.

SELECT C.Date,C.DayOfWeekName,SH.Date,SH.TableId 
FROM dbo.fun_FreeCalendar(@StartDate,@EndDate) AS C 
LEFT JOIN (     
    SELECT TableId, CONVERT(VARCHAR(10),Date,101) AS Date     
    FROM MyTable (NOLOCK) 
) AS SH ON CONVERT(VARCHAR(10),SH.Date,101) = CONVERT(VARCHAR(10),C.Date,101) 
WHERE C.DayOfWeek NOT IN(1,7)  -- Sadece is günleri

 

Saglicakla kalin :)

If you like this, follow my RSS channel!