select with a while loop

  • Hey,

    is there a way to bring a select and a a while loop for a row together?

    What I like to do is a select the bring me the following:

    Nr || DateBegin || DateEnd || Datediff via function (count weekdays, without weekend and holiday days)

    I have a loop that works fine.

    declare @start datetime, @End date, @diff int, @C int

    select @start ='20.12.2015',

    @End = '03.01.2016',

    @diff = 0,

    @C = datediff(dd,@start,@End)

    while @C >0

    Begin

    if left(dbo.isarbeitstag(@End,3),1)=9

    begin

    set @diff = @diff +1

    end

    set @end =dateadd(d,-1,@end)

    set @C = @C-1

    end

    print @diff

    Now the @Start should be the row DateBegin and @End should be DateEnd.

    How can i bring the while loop in the select with the result in a row?

    Thanks for help

    Olaf

  • Solution:

    Add a new function.

    create FUNCTION AnzahlArbeitstage(@Start datetime , @End datetime)

    RETURNS int

    AS BEGIN

    declare @diff int, @C int

    select @diff = 0,

    @C = datediff(dd,@start,@End)

    while @C >0

    Begin

    if left(dbo.isarbeitstag(@End,3),1)=9

    begin

    set @diff = @diff +1

    end

    set @end =dateadd(d,-1,@end)

    set @C = @C-1

    end

    return @diff

    end

    select dbo.AnzahlArbeitstage( '01.01.2016','08.01.2016')

  • Well done on solving your problem.

    Generally though, we aren't big fans of two of the techniques you have employed here: namely WHILE loops and scalar functions, for performance reasons.

    Should you have any desire to learn some new and probably faster ways of solving your problem, you'll need to post more information so that people can understand what you are trying to do.

    Along with this narrative, if you include DDL, sample data and desired results (see the link in my signature for details), you'll likely get a working solution to your problem.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • As Phil has pointed out the performance of this code will be poor. Can you post the code for function dbo.isarbeitstag? Does it reference a calendar table?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey, here the second function.

    It´s just for reporting, so the performance is not my top point 🙂

    Whats better then while?

    create FUNCTION isArbeitstag(@cDatum DATETIME, @typus AS INTEGER)

    /*

    @cdatum= Datum, das kontrolliert werden soll

    @typus = 0 (nur Wochenenden)

    @typus = 1 (Bundesweite Feiertage und Wochenenden)

    @typus = 2 (Alle Feiertage und Wochenenden)

    Beispielhafte Übergabewerte:

    ----------------------------

    '1,Neujahr'

    '2,Heilige drei Könige'

    '0,Samstag'

    '0,Sonntag'

    '9,Arbeitstag'

    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Bitte die Einstellungen von "Datepart" kontrollieren

    Für Sonntage muss datepart(weekday,@cdatum)=7 gelten

    Wenn dies nicht zutrifft, dann müssen alle Aufrufe

    dieser Funktion entsprechend angepasst werden

    (Auch beim Buß- und Bettag)

    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    */

    RETURNS VARCHAR(27)

    AS BEGIN

    DECLARE @m AS INTEGER

    DECLARE @N AS INTEGER

    DECLARE @a AS INTEGER

    DECLARE @b-2 AS INTEGER

    DECLARE @C AS INTEGER

    DECLARE @d AS INTEGER

    DECLARE @e AS INTEGER

    DECLARE @tag AS INTEGER

    DECLARE @mon AS INTEGER

    DECLARE @xJahr AS INTEGER

    DECLARE @TagDerWoche AS INTEGER

    DECLARE @strJahr AS VARCHAR(4)

    DECLARE @Datum AS DATETIME

    DECLARE @datOsterSonntag AS DATETIME

    IF @typus=0 BEGIN -- nur Wochenenden

    IF DATEPART(weekday,@cdatum)=6 BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7 BEGIN

    RETURN '0,Sonntag'

    END

    END

    ELSE BEGIN -- Feiertage und Wochenenden

    SET @xJahr=YEAR(@cDatum)

    SET @strJahr=CONVERT(VARCHAR(4),@xJahr)

    SET @m = 23

    SET @N = 3

    IF @xJahr > 1799 BEGIN

    SET @N = 4

    END

    IF @xJahr > 1899 BEGIN

    SET @N = @N + 1

    SET @m = 24

    END

    IF @xJahr > 2099 BEGIN

    SET @N = @N + 1

    END

    SET @a = @xJahr % 19

    SET @b-2 = @xJahr % 4

    SET @C = @xJahr % 7

    SET @d = ((@a * 19) + @m) % 30

    SET @e = ((@B * 2) + (@C * 4) + (@d * 6) + @N) % 7

    SET @tag = @d + @e + 22

    IF @tag > 31 BEGIN

    SET @tag = @tag - 31

    IF (@Tag = 26) Or ((@Tag = 25) And (@d = 28) And (@a > 10)) BEGIN

    SET @tag = @tag - 7

    END

    SET @mon = 4

    END

    ELSE BEGIN

    SET @mon = 3

    END

    SET @datum = CONVERT(DATETIME,CONVERT(VARCHAR(2),@tag) +'.'

    + CONVERT(VARCHAR(2),@mon) + '.' + @strJahr)

    --Ostersonntag

    SET @datOsterSonntag = @Datum

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Ostersonntag'

    END

    --Karfreitag

    SET @datum = DATEADD(DAY,-2,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Karfreitag'

    END

    --Ostermontag

    SET @datum = DATEADD(DAY,1,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Ostermontag'

    END

    --Pfingstsonntag

    SET @datum = DATEADD(DAY,49,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Pfingstsonntag'

    END

    --Pfingstmontag

    SET @datum = DATEADD(DAY,50,@datOsterSonntag)

    IF @cdatum=@datum BEGIN

    RETURN '1,Pfingstmontag'

    END

    --Chr.Himmelfahrt

    SET @datum = DATEADD(DAY,39,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Christi Himmelfahrt'

    END

    --Fronleichnam

    SET @datum = DATEADD(DAY,60,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    IF @typus=2 BEGIN

    RETURN '2,Fronleichnam'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6 BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7 BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    --Buß+Bettag

    SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

    SET @TagDerWoche = 0

    WHILE @TagDerWoche <>3 BEGIN

    SET @TagDerWoche=DATEPART(weekday,@datum)

    IF @TagderWoche=3 BEGIN

    SET @datum=DATEADD(DAY,14,@datum)

    END

    ELSE BEGIN

    SET @datum=DATEADD(DAY,1,@datum)

    END

    END

    IF DAY(@datum) < 16 BEGIN

    SET @datum = DATEADD(DAY,7,@datum)

    END

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    IF @typus=2 BEGIN

    RETURN '2,Buß- und Bettag'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6 BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7 BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    -- feste Feiertage

    SET @datum = CONVERT(DATETIME,'01.01.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Neujahr'

    END

    --SET @datum = CONVERT(DATETIME,'06.01.' + @strJahr)

    --IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    -- IF @typus=2 BEGIN

    -- RETURN '2,Heilige drei Könige'

    -- END

    -- ELSE BEGIN

    -- IF DATEPART(weekday,@cdatum)=6 BEGIN

    -- RETURN '0,Samstag'

    -- END

    -- IF DATEPART(weekday,@cdatum)=7 BEGIN

    -- RETURN '0,Sonntag'

    -- END

    -- END

    --END

    SET @datum = CONVERT(DATETIME,'01.05.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Maifeiertag'

    END

    SET @datum = CONVERT(DATETIME,'03.10.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Tag der deutschen Einheit'

    END

    SET @datum = CONVERT(DATETIME,'15.08.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    IF @typus=2 BEGIN

    RETURN '2,Mariä Himmelfahrt'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6 BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7 BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    SET @datum = CONVERT(DATETIME,'31.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Silvester'

    END

    SET @datum = CONVERT(DATETIME,'24.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,Heiligabend'

    END

    SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    IF @typus=2 BEGIN

    RETURN '2,Allerheiligen'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6 BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7 BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    SET @datum = CONVERT(DATETIME,'25.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,1. Weihnachtstag'

    END

    SET @datum = CONVERT(DATETIME,'26.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    RETURN '1,2. Weihnachtstag'

    END

    IF DATEPART(weekday,@cdatum)=6 BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7 BEGIN

    RETURN '0,Sonntag'

    END

    END

    RETURN '9,Arbeitstag'

    END

  • Here is a formatted version.

    create FUNCTION isArbeitstag ( @cDatum DATETIME

    , @typus AS INTEGER )

    /*

    @cdatum= Datum, das kontrolliert werden soll

    @typus = 0 (nur Wochenenden)

    @typus = 1 (Bundesweite Feiertage und Wochenenden)

    @typus = 2 (Alle Feiertage und Wochenenden)

    Beispielhafte Übergabewerte:

    ----------------------------

    '1,Neujahr'

    '2,Heilige drei Könige'

    '0,Samstag'

    '0,Sonntag'

    '9,Arbeitstag'

    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Bitte die Einstellungen von "Datepart" kontrollieren

    Für Sonntage muss datepart(weekday,@cdatum)=7 gelten

    Wenn dies nicht zutrifft, dann müssen alle Aufrufe

    dieser Funktion entsprechend angepasst werden

    (Auch beim Buß- und Bettag)

    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    */

    RETURNS VARCHAR(27)

    AS

    BEGIN

    DECLARE @m AS INTEGER

    DECLARE @N AS INTEGER

    DECLARE @a AS INTEGER

    DECLARE @b-2 AS INTEGER

    DECLARE @C AS INTEGER

    DECLARE @d AS INTEGER

    DECLARE @e AS INTEGER

    DECLARE @tag AS INTEGER

    DECLARE @mon AS INTEGER

    DECLARE @xJahr AS INTEGER

    DECLARE @TagDerWoche AS INTEGER

    DECLARE @strJahr AS VARCHAR(4)

    DECLARE @Datum AS DATETIME

    DECLARE @datOsterSonntag AS DATETIME

    IF @typus=0

    BEGIN -- nur Wochenenden

    IF DATEPART(weekday,@cdatum)=6

    BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7

    BEGIN

    RETURN '0,Sonntag'

    END

    END

    ELSE BEGIN -- Feiertage und Wochenenden

    SET @xJahr=YEAR(@cDatum)

    SET @strJahr=CONVERT(VARCHAR(4),@xJahr)

    SET @m = 23

    SET @N = 3

    IF @xJahr > 1799

    BEGIN

    SET @N = 4

    END

    IF @xJahr > 1899

    BEGIN

    SET @N = @N + 1

    SET @m = 24

    END

    IF @xJahr > 2099

    BEGIN

    SET @N = @N + 1

    END

    SET @a = @xJahr % 19

    SET @b-2 = @xJahr % 4

    SET @C = @xJahr % 7

    SET @d = ((@a * 19) + @m) % 30

    SET @e = ((@B * 2) + (@C * 4) + (@d * 6) + @N) % 7

    SET @tag = @d + @e + 22

    IF @tag > 31

    BEGIN

    SET @tag = @tag - 31

    IF (@Tag = 26) Or ((@Tag = 25) And (@d = 28) And (@a > 10))

    BEGIN

    SET @tag = @tag - 7

    END

    SET @mon = 4

    END

    ELSE BEGIN

    SET @mon = 3

    END

    SET @datum = CONVERT(DATETIME,CONVERT(VARCHAR(2),@tag) +'.'

    + CONVERT(VARCHAR(2),@mon) + '.' + @strJahr)

    --Ostersonntag

    SET @datOsterSonntag = @Datum

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Ostersonntag'

    END

    --Karfreitag

    SET @datum = DATEADD(DAY,-2,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Karfreitag'

    END

    --Ostermontag

    SET @datum = DATEADD(DAY,1,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Ostermontag'

    END

    --Pfingstsonntag

    SET @datum = DATEADD(DAY,49,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Pfingstsonntag'

    END

    --Pfingstmontag

    SET @datum = DATEADD(DAY,50,@datOsterSonntag)

    IF @cdatum=@datum

    BEGIN

    RETURN '1,Pfingstmontag'

    END

    --Chr.Himmelfahrt

    SET @datum = DATEADD(DAY,39,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Christi Himmelfahrt'

    END

    --Fronleichnam

    SET @datum = DATEADD(DAY,60,@datOsterSonntag)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    IF @typus=2

    BEGIN

    RETURN '2,Fronleichnam'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6

    BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7

    BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    --Buß+Bettag

    SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

    SET @TagDerWoche = 0

    WHILE @TagDerWoche <>3

    BEGIN

    SET @TagDerWoche=DATEPART(weekday,@datum)

    IF @TagderWoche=3

    BEGIN

    SET @datum=DATEADD(DAY,14,@datum)

    END

    ELSE BEGIN

    SET @datum=DATEADD(DAY,1,@datum)

    END

    END

    IF DAY(@datum) < 16

    BEGIN

    SET @datum = DATEADD(DAY,7,@datum)

    END

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    IF @typus=2

    BEGIN

    RETURN '2,Buß- und Bettag'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6

    BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7

    BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    -- feste Feiertage

    SET @datum = CONVERT(DATETIME,'01.01.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Neujahr'

    END

    --SET @datum = CONVERT(DATETIME,'06.01.' + @strJahr)

    --IF DATEDIFF(DAY,@cdatum,@datum) = 0 BEGIN

    -- IF @typus=2 BEGIN

    -- RETURN '2,Heilige drei Könige'

    -- END

    -- ELSE BEGIN

    -- IF DATEPART(weekday,@cdatum)=6 BEGIN

    -- RETURN '0,Samstag'

    -- END

    -- IF DATEPART(weekday,@cdatum)=7 BEGIN

    -- RETURN '0,Sonntag'

    -- END

    -- END

    --END

    SET @datum = CONVERT(DATETIME,'01.05.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Maifeiertag'

    END

    SET @datum = CONVERT(DATETIME,'03.10.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Tag der deutschen Einheit'

    END

    SET @datum = CONVERT(DATETIME,'15.08.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    IF @typus=2

    BEGIN

    RETURN '2,Mariä Himmelfahrt'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6

    BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7

    BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    SET @datum = CONVERT(DATETIME,'31.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Silvester'

    END

    SET @datum = CONVERT(DATETIME,'24.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,Heiligabend'

    END

    SET @datum = CONVERT(DATETIME,'01.11.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    IF @typus=2

    BEGIN

    RETURN '2,Allerheiligen'

    END

    ELSE BEGIN

    IF DATEPART(weekday,@cdatum)=6

    BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7

    BEGIN

    RETURN '0,Sonntag'

    END

    END

    END

    SET @datum = CONVERT(DATETIME,'25.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,1. Weihnachtstag'

    END

    SET @datum = CONVERT(DATETIME,'26.12.' + @strJahr)

    IF DATEDIFF(DAY,@cdatum,@datum) = 0

    BEGIN

    RETURN '1,2. Weihnachtstag'

    END

    IF DATEPART(weekday,@cdatum)=6

    BEGIN

    RETURN '0,Samstag'

    END

    IF DATEPART(weekday,@cdatum)=7

    BEGIN

    RETURN '0,Sonntag'

    END

    END

    RETURN '9,Arbeitstag'

    END

    What's better than WHILE? No WHILE at all (ie, get rid of loops if at all possible)

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • It could be as simple as having a calendar table with holidays and workdays included. Then the query would come like this:

    SELECT COUNT(*)

    FROM Calendar

    WHERE cal_date BETWEEN @start AND @End

    AND IsWorkDay = 1

    --Maybe IsHoliday = 0 depending on logic

    Of course, I can't give much advice as my German goes as far as "guten tag".

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • orenk (2/1/2016)


    Hey, here the second function.

    It´s just for reporting, so the performance is not my top point 🙂

    Whats better then while?

    Ouch 😉

    When you can make something run better, why would you ever choose to not do so? Take a look at Jeff Moden's article "The 'Numbers' or 'Tally' Table: What it is and how it replaces a loop" (http://www.sqlservercentral.com/articles/T-SQL/62867/)[/url].


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • One thing I noticed is when you create the date with this line of code

    SET @datum = CONVERT(DATETIME,CONVERT(VARCHAR(2),@tag) +'.'

    + CONVERT(VARCHAR(2),@mon) + '.' + @strJahr)

    you could use the DateFromParts function that was new in 2012

    SET @datum=DateFromParts(@tag,@mon,@xJahr)

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply