Unanswered: Loop in UDF not running?
I have a UDF that I call from several different stored procedures. It returns
a bit that tells me whether or not yesterday was a weekend or holiday.
It doesn't want to work when yesterday is a Monday holiday.
What I am trying to do is: if yesterday was a weekend, or holiday, then go back to the previous work day. In other words, on Monday morning, I want my report to show data for Friday, not Sunday. This UDF just tells me whether or not I need to look at a different day or not.
I copied the guts of it into query analyzer, and tried to get it to run. For some reason, the loop section seems to be stopping at 1, and that's what my problem is. I'd expect it to run from 1 to 27 (today's date).
Note: The @dtmDate variable is passed to the UDF, and is always just "today's" date.
declare @intCntDay as tinyint
declare @dtmTemp as smalldatetime
declare @bit1stDay as bit
DECLARE @DTMDATE AS SMALLDATETIME
SET @DTMDATE = GETDATE()
If datepart(d,@dtmdate) = 1
SET @intCntDay = 1
WHILE @intCntDay <= datepart(d, @dtmDate)
SET @dtmTemp = CAST(CAST(DatePart(m, @dtmDate) AS VARCHAR(2)) + '/' + CAST(@intCntDay AS VARCHAR(2)) + '/' + CAST(DatePart(yyyy, @dtmDate)AS CHAR(4))AS SMALLDATETIME)
set @bit1stday = (SELECT CASE
WHEN DATEpart(w, @dtmTemp) = 1 THEN 1
WHEN DATEpart(w, @dtmTemp) = 2 THEN 1
WHEN DATEpart(w, @dtmTemp)= 3 AND dateadd(d,-1,@dtmTemp) = dbo.getholiday(@dtmDate) THEN 1
WHEN DATEpart(w, @dtmTemp) = 7 THEN 1
else 0 end)
IF @BIT1STDAY = 0
set @dtmTemp = CAST(CAST(DatePart(m, @dtmtemp) AS VARCHAR(2)) + '/1/' + CAST(DatePart(yyyy, @dtmtemp)AS CHAR(4))AS SMALLDATETIME)
set @intCntDay = datediff(d,@dtmTemp,dateadd(m,1,@dtmTemp))+1
Inspiration Through Fermentation