Ge mig datumet för den tredje lördagen i maj 2013


I många fall har man nytta av att ta reda på vilket datum den en viss veckodag infaller i en viss månad, t.ex den tredje lördagen i maj 2013. Det kanske är för att ta fram mötesdagar eller att planera återkommande besök.

Det finns många som tycker att man ska använda sig av en kalendertabell och skapa en sju nya kolumner för detta. Jag tycker inte det. Dels för att det skapar onödigt redundant data i tabellen samt att det kommer att generera mycket mer IO i databasen än vad som behövs.

Alternativet är då att beräkna sig fram till vilken datum den tredje lördagen i maj 2013 är. Och hur gör man det på ett effektivt sätt?

Lösningen ligger i att veta att den första lördagen, eller vilken veckodag som helst, infaller mellan den 1 och 7 i varje månad. Den andra lördagen infaller mellan den 8 och 14, den tredje lördagen mellan den 15 och 21, den fjärde lördagen mellan den 22 och 28 samt till slut den eventuellt femte lördagen som infaller mellan den 29 och 29/30/31 i den önskade månaden.

För att effektivt kunna göra en beräkning på detta behöver vi totalt tre parametrar till vår funktion.

  1. Ett datum (vilket som helst) som infaller den månad som önskas.
  2. Ett datum med samma veckodag som det datum som önskas. Denna parameter skulle kunna ändras till ett hårdkodat värde, men då är det absolut viktigt att det dokumenteras vilket värde som används till vilken veckodag.
  3. Ett värde för första, andra, tredje, fjärde eller femte veckodagen som önskas.

Först beräknar vi den förste i den önskade månaden med följande algoritm.

DATEADD(MONTH, DATEDIFF(MONTH, ‘19000101’, @WantedDate), ‘19000101’)

Ovanstående algoritm är ganska enkel. Först beräknar man antalen kalendermånader som passerat sedan ett fixerat datum (i det här fallet 1900-01-01) och den månad som önskas. Det blir 1 360 månader för maj 2013.

Dessa månader läggs sedan till ett annat fixerat datum (för enkelhetens skull är även detta 1900-01-01). Då blir resultatet av algoritmen 2013-05-01. En ganska bra början.

Och nu till tricket. Jag har valt att inte göra en CASE för alla sju datumen som kan komma i fråga, utan skapar en virtuell tabell med sju rader med alla de datum som ligger inom det önskade intervallet.

VALUES      (DATEADD(DAY, 7 * @Nth 7, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 6, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 5, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 4, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 3, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 2, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 1, b.FirstDateOfWantedMonth))

  

Nu vet vi att det önskade datumet är något av ovanstående. Så hur ska vi validera resultatet? Det behövs totalt tre kontroller för detta

  1. Kontrollera så att det önskade intervallet är 1, 2, 3, 4 eller 5.
  2. Kontrollera vilket av ovanstående datum som har den önskade veckodagen.
  3. Kontrollera så att datumen ovan verkligen är i samma månad som önskas. Det kan bli problem annars om man önskar den femte lördagen och det visar sig att den egentligen ligger i efterföljande månad.

Kontrollerna kan se ut som dessa

WHERE       DATEDIFF(DAY, f.theDate, @WeekDay) % 7 = 0
            AND DATEDIFF(MONTH, f.theDate, @WantedDate) = 0
            AND @Nth BETWEEN 1 AND 5

Den första kontrollen ser till så att det datum vi vill ha tillbaka har samma veckodag som det önskade datumet genom att beräkna differens i dagar mellan dessa och ta det datumet som är en multipel av sju dagar. Den andra kontrollen ser till så att inget datum spiller över till följande månad. Den tredje kontrollen ser till så att värdet på parametrarna ligger inom ett giltigt intervall.

Och svårare än så här blir det inte. Sätter vi ihop allting till en funktion så kommer den att se ut så här

CREATE FUNCTION dbo.fnGetNthWeekday
(
            @WantedMonth DATE,
            @WantedWeekday DATE,
            @Nth TINYINT
)
RETURNS DATE
AS
BEGIN
        RETURN  (
                       
SELECT          f.theDate
                        FROM            (
                                                VALUES  (DATEADD(MONTH, DATEDIFF(MONTH, ‘19000101’, @WantedMonth), ‘19000101’))
                                        ) AS b(FirstDateOfWantedMonth)
                        CROSS APPLY     (
                                                VALUES  (DATEADD(DAY, 7 * @Nth 7, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 6, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 5, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 4, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 3, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 2, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 1, b.FirstDateOfWantedMonth))
                                        ) AS f(theDate)
                        WHERE           DATEDIFF(DAY, f.theDate, @WantedWeekday) % 7 = 0
                                        AND DATEDIFF(MONTH, f.theDate, @WantedMonth) = 0
                                        AND @Nth BETWEEN 1 AND 5
                )
END

Hur gör man sedan för att ta fram den tredje lördagen i maj 2013?

SELECT      dbo.fnGetNthWeekday(‘20130501’, ‘19690906’, 3)

Så nu vet du att den 18 maj 2013 är den tredje lördagen i maj 2013.

Have any Question or Comment?

Leave a Reply