[SQL] Tellen van aantal werkdagen tussen 2 datums.

[Anoniem: 35302]

Ik heb een invoerdatum en een huidige datum. Als op de huidige datum meer dan x werkdagen verstreken zijn moet er een melding komen.

Nu heb ik al lopen prutsen met DATEDIFF(weekday, date1, date2) maar dat levert niet het gewenste resultaat.

code:

Overschrijding = datediff(dw, aanmelddatum, CONVERT(CHAR(30), getdate(), 0))

ps werk met SQL Server 2000.

[whoami]

Wat levert de code op die je gepost hebt? Waarom werkt het niet, wat werkt er niet etc....
Kortom:
Welkom in P&W -> Quickstart (update 2/10/2002)

[Anoniem: 68553]

Ik heb zelf onderstaande geschreven (is trouwens makelijker te doen m.b.v. modulo (%))

CREATE FUNCTION aantalWerkdagen (@Datumbegin datetime, @Datumeind datetime, @tempCurrent datetime)
RETURNS int
AS
BEGIN
DECLARE @tempDatumB datetime
DECLARE @tempDatumE datetime

IF Datepart(yyyy, @Datumbegin) < (Datepart(yyyy, @tempCurrent))
AND Datepart(yyyy, @Datumeind) >(Datepart(yyyy, @tempCurrent))
AND @Datumbegin < @Datumeind

BEGIN
SET @tempDatumB = '01-01-' + CAST(Datepart(yyyy, @tempCurrent) AS CHAR(4))
SET @tempDatumE = '12-31-' + CAST(Datepart(yyyy, @tempCurrent) AS CHAR(4))
END

ELSE

BEGIN
IF Datepart(yyyy, @Datumbegin) < Datepart(yyyy, @tempCurrent)
AND Datepart(yyyy, @Datumeind) = Datepart(yyyy, @tempCurrent)
BEGIN
SET @tempDatumB = '01-01-' + CAST(Datepart(yyyy, @tempCurrent) AS CHAR(4))
SET @tempDatumE = @Datumeind
END

ELSE
BEGIN
IF Datepart(yyyy, @Datumbegin) = Datepart(yyyy, @tempCurrent)
AND Datepart(yyyy, @Datumeind) > Datepart(yyyy, @tempCurrent)
BEGIN
SET @tempDatumB = @Datumbegin
SET @tempDatumE = '12-31-' + CAST(Datepart(yyyy, @tempCurrent) AS CHAR(4))
END

ELSE
BEGIN
SET @tempDatumB = @Datumbegin
SET @tempDatumE = @Datumeind
END
END
END

DECLARE @wDagen int
SET @wDagen = 0
WHILE @tempDatumB <= @tempDatumE
BEGIN
IF DATEPART(dw, @tempDatumB) BETWEEN 1 AND 5
BEGIN
SET @wDagen = @wDagen + 1
SET @tempDatumB = @tempDatumB + 1
END
ELSE
BEGIN
SET @tempDatumB = @tempDatumB + 1
END
CONTINUE
END
RETURN @wDagen

END


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[Anoniem: 35302]

Bedankt! Ziet er in de gauwigheid goed uit en wordt nu door de mangel gehaalt door mij en m'n collega!

[cameodski]

Heb even naar de function gekeken en er vallen me een paar dingen op:

Afhankelijk van de taal is het 12-31 of 31-12, dus onderstaande regel kan zomaar in de soep lopen.

code:

SET @tempDatumE = '12-31-' + CAST(Datepart(yyyy, @tempCurrent) AS CHAR(4))

Onderstaande methodes interpreteert MSSQL wel altijd goed en is ook taal afhankelijk

code:

SET @tempDatumE = CAST(Datepart(yyyy, @tempCurrent) AS CHAR(4)) + '1231'

Een vergelijkbaar probleem is er met de weekday in onderstaande code

code:

IF DATEPART(dw, @tempDatumB) BETWEEN 1 AND 5

Dit probleem heb ik opgelost in onderstaande user defined function

code:

CREATE FUNCTION dbo.GetWeekDayNumber
(
    @Date   datetime
)
RETURNS int
AS
BEGIN
RETURN ((((@@DATEFIRST + DATEPART (weekday, @Date)) + 5) % 7))
END

Deze geeft onafhankelijk van de taal de volgende resultaten:
maandag = 0
dinsdag = 1
......
zondag = 6

[Janoz]

Volgens mij zijn bv 1e en 2e kerstdag helemaal geen werkdagen. Da's mischien nog makkelijk, maar wat dacht je van 1e paasdag en 2e paasdag?

[Heras]

feestdagen zijn inderdaad lastig... ik heb in access een functie werkdagen geschreven...
kwam via google op deze page uit:

http://members.lycos.nl/bouwzelf/paasdata.htm#calc

daar staat hoe je de datum voor pasen kunt berekenen

[Knutselsmurf]

Feestdagen lijkt mij iets dat er bijna niet automatishc uit te halen is. Om er even een paar te noemen:
Pasen, pinksteren, koninginnedag, bevrijdingsdag.

Van de eerste 2 is het lastig om de datum te bepalen en van de laatste 2 is het niet duidelijk of het een officiele feestdag is. Vooral bevrijdingsdag wil nog wel eens van het jaar afhangen......

[Heras]

Vooral bevrijdingsdag wil nog wel eens van het jaar afhangen......

Volgens mij is bevrijdingsdag om de 5 jaar een feestdag, dus in 2000,2005,2010,2015,2020 enz

[Anoniem: 35302]

typisch geval van doorschieten....
En omdat ik weet dat klanten dat ook kunnen heb ik in de begrippenlijst van mijn FO ook staan dat werkdagen alle dagen van het jaar zijn m.u.v. zaterdag en zondag. (begonnen ze uiteraard gelijk over de werkdagen volgens hun CAO...aaaargh!)

Voor mij werkt dit dus goed.

[Dido]

Janoz schreef op 07 november 2002 @ 13:28:
Volgens mij zijn bv 1e en 2e kerstdag helemaal geen werkdagen. Da's mischien nog makkelijk, maar wat dacht je van 1e paasdag en 2e paasdag?

Eerste paasdag is nooit een werkdag. Net zo min als Vaderdag, Moederdag en alle andere zondagen in het jaar.

[Annie]

Voorbeeldje van een udf voor het bepalen van de paasdatum:

SQL:

CREATE FUNCTION dbo.GetEasterDate (@Year int)
  RETURNS datetime /* first day of Easter for the given year */
AS
BEGIN
  DECLARE @a int, @b int, @c int, @d int, @e int, @f int
  DECLARE @Easter datetime
  
  /* based on Karl Friedrich Gauss' (1777-1855) algorithm */
  SET @a = @Year % 19
  SET @b = @Year % 4
  SET @c = @Year % 7
  SET @d = (19 * @a + 23) % 30
  SET @e = (2 * @b + 4 * @c + 6 * @d + 5) % 7
  
  IF @e = 0
    SET @e = 7
  
  IF (@d + @e) <= 9
  BEGIN
    SET @f = 22 + @d + @e
    SET @Easter = CAST(
                    (CAST(@Year AS varchar(4)) + '-3-' + CAST(@f AS varchar(2)))
                    AS datetime
                  )
  END
  ELSE
  BEGIN
    SET @f = @d + @e - 9
    SET @Easter = CAST(
                    (CAST(@Year AS varchar(4)) + '-4-' + CAST(@f AS varchar(2)))
                    AS datetime
                  )
  END
    
  IF @Year = 1954 OR @Year = 1981
    SET @Easter = DATEADD(wk, -1, @Easter)

  RETURN @Easter
END

En daarmee kan je dan weer andere data berekenen:

SQL:

DECLARE @Easter datetime
SELECT @Easter = dbo.GetEasterDate(2002)

SELECT 'Goede vrijdag'  = DATEADD(d, -2, @Easter)
SELECT 'pasen'          = @Easter
SELECT '2e paasdag'     = DATEADD(d, 1, @Easter)
SELECT 'Hemelvaart'     = DATEADD(d, 39, @Easter)
SELECT '1e pinksterdag' = DATEADD(d, 49, @Easter)
SELECT '2e pinksterdag' = DATEADD(d, 50, @Easter)

Eventueel kan je al deze data (plus de kerstdagen, koninginnedag, enz.) nog in een udf stoppen die een table returned en dan kan je daar fijn op joinen met je andere tables.

[ Voor 0% gewijzigd door Annie op 07-11-2002 19:31 . Reden: bugfix ]

[Annie]

Dido schreef op 07 november 2002 @ 15:39:
[...]

Eerste paasdag is nooit een werkdag. Net zo min als Vaderdag, Moederdag en alle andere zondagen in het jaar.

Vertel dat maar aan alle mensen in (bijv.) de horeca

[cameodski]

Annie, ook jouw code is niet taalonafhankelijk. Zie eerdere post van mij.

[Dido]

cameodski schreef op 07 november 2002 @ 17:28:
Annie, ook jouw code is niet taalonafhankelijk. Zie eerdere post van mij.

Als je het dan goed wilt doen moet je ook rekening houden met het orthodoxe paasfeest...

Annie: Horeca is een andere wereld. Daar is iedere dag een werkdag, dus werkt een simpele datediff prima.

[Anoniem: 35302]

ERLINDEN: Waarom moet eigenlijk die @TempCurrent als input parameter worden meegegeven?

[Annie]

cameodski schreef op 07 november 2002 @ 17:28:
Annie, ook jouw code is niet taalonafhankelijk. Zie eerdere post van mij.

Ik kan eigenlijk niet zo goed ontdekken waar hij op fout zou moeten gaan. Toch niet de CAST naar datetime? Dat is toch gewoon een veilige yyyy-m-d date notatie?

Ik zag overigens wel nog een ander foutje in de code:
DATEADD(w, ..) moet natuurlijk DATEADD(wk, ...) zijn

Weet eigenlijk niet eens meer waar ik deze rekenregels ooit vandaan gehaald heb, maar als ik het algoritme van Gauss op de link uit een eerdere post bekijk dan is dit een of andere variatie daarop.

Dido: don't take everything serious

[TweakersOnly]

Voor de belangstellenden:

Pasen valt altijd op zondag NA de eerste volle maan na het begin van de lente. Uitzondering hierop zijn twee jaren, ik dacht 1988 en een jaar in de negentiende eeuw. Volgende simpele functie in ASP bepaalt de datum van paaszondag. Aan de hand van deze datum kunnen de volgende feestdagen worden bepaald:
- Carnaval
- Pasen
- Hemelvaart
- Pinksteren

ASP:

FUNCTION getPaasdatum (Jaar)
    dim a, b, c, d, e, dag
    Jaar = CInt(Jaar)
    a = Jaar mod 19
    b = Jaar mod 4
    c = Jaar mod 7
    d = (19*a + 23) mod 30
    e = (2*b + 4*c + 6*d + 5) mod 7
    IF (e = 0) THEN
        e = 7
    END IF
    IF ((d + e) <= 9) THEN
        dag = 22 + d + e
        getPaasdatum = dag & "-03-" & Jaar  
    ELSE
        dag = d + e - 9
        IF (dag > 9) THEN
            getPaasdatum = dag & "-04-" & Jaar
        ELSE
            getPaasdatum = "0" & dag & "-04-" & Jaar
        END IF          
    END IF
END FUNCTION

[cameodski]

Annie schreef op 07 november 2002 @ 19:29:
Ik kan eigenlijk niet zo goed ontdekken waar hij op fout zou moeten gaan. Toch niet de CAST naar datetime? Dat is toch gewoon een veilige yyyy-m-d date notatie?

Zo'n beetje de enige veilige manier in MSSQL om met datum te werken is als je het formaat yyyymmdd gebruikt. Zo gauw als je streepjes, slashes e.d. gaat gebruiken is volgens mij altijd taal afhankelijk

Ik zag overigens wel nog een ander foutje in de code:
DATEADD(w, ..) moet natuurlijk DATEADD(wk, ...) zijn

Persoonlijk heb ik een bloedhekel aan al die vage afkortingen. Gelukkig ondersteund MSSQL ook de wat minder verwarrende woorden:
year, month, day, weekday, hour, minute, second en millisecond. Het is iets meer werk om in te typen, maar het bespaart je soms een heleboel ellende.

[Annie]

cameodski schreef op 07 november 2002 @ 22:58:
[...]

Zo'n beetje de enige veilige manier in MSSQL om met datum te werken is als je het formaat yyyymmdd gebruikt. Zo gauw als je streepjes, slashes e.d. gaat gebruiken is volgens mij altijd taal afhankelijk

Hmm... MS is het met je eens.
Heb altijd gedacht dat yyyy-mm-dd ook language-safe was. Liet me daarbij een beetje leiden door de tabel uit de BOL bij de CONVERT styles. Vind het wel vreemd dat ze niet gewoon de ISO date notatie aanhouden. Hopelijk is het gewoon een 'foutje' in de documentatie en is de parser slim genoeg om in te zien dat yyyymmdd hetzelfde is als yyyy-mm-dd. Waarbij de laatste in mijn ogen gewoon de voorkeur heeft (of zou moeten hebben).

cameodski schreef op 07 november 2002 @ 22:58:
[...]

Persoonlijk heb ik een bloedhekel aan al die vage afkortingen. Gelukkig ondersteund MSSQL ook de wat minder verwarrende woorden:
year, month, day, weekday, hour, minute, second en millisecond. Het is iets meer werk om in te typen, maar het bespaart je soms een heleboel ellende.

Je hebt gelijk, ik ga m'n leven beteren