select with a while loop

Question

Post reply

select with a while loop

orenk

Old Hand

Points: 388
More actions
February 1, 2016 at 2:52 am

#311235

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

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

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

orenk Old Hand Points: 388 More actions · Answer 1

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')

Phil Parkin SSC Guru Points: 246956 More actions · Answer 2

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

orenk Old Hand Points: 388 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246956 More actions · Answer 5

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

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

--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

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

-- 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

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

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

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

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

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/

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

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