February 1, 2016 at 2:52 am
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)
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
February 1, 2016 at 6:17 am
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)
end
return @diff
end
select dbo.AnzahlArbeitstage( '01.01.2016','08.01.2016')
February 1, 2016 at 6:23 am
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
February 1, 2016 at 7:34 am
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?
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
February 1, 2016 at 7:42 am
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
IF (@Tag = 26) Or ((@Tag = 25) And (@d = 28) And (@a > 10)) BEGIN
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
February 1, 2016 at 8:05 am
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
IF (@Tag = 26) Or ((@Tag = 25) And (@d = 28) And (@a > 10))
BEGIN
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
February 1, 2016 at 8:18 am
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".
February 1, 2016 at 8:22 am
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].
February 1, 2016 at 8:31 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy