Solved

Having problems with UDF that uses If statements and while loop

Posted on 2004-07-30
3
786 Views
Last Modified: 2012-08-13
I just need some guru to fix my syntax. I spent over an hour trying to find the problem, but it just isn't obvious to me.

---------------------------------
CREATE   FUNCTION dbo.DaysOff
  (
     
      @dtStartDate smallDatetime,
      @dtEndDate smalldatetime,
      @strStartTime varchar(8),
      @strEndTime varchar(8)
   
      
  )
RETURNS decimal
AS
begin
DECLARE @dtStartTime DateTime,
      @dtEndTime DateTime,
      @sgHours decimal

If DateDiff(day,@dtStartDate , @dtEndDate) > 1
       While @dtStartDate >= @dtEndDate
            Begin
                  If DatePart(Weekday,@dtStartDate) > 1 and DatePart(Weekday,@dtStartDate) < 7                              
                  Set @sgHours = @sghours + 8            
                  set @dtStartDate = DateAdd(day,1,@dtStartDate)                  
            end
Else
      If Isdate(@strStartTime)
      
            Set @dtStartTime = Convert(datetime,@strStarttime,108)
            If IsDate(@strEndTime)
                  
                  Set @dtEndTime = Convert(DateTime,@strEndTime,108)
                  Set @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
                  
            Else
                  Set @sgHours = 8
      
      Else
            Set @sgHours = 8

return @sgHours

end
0
Comment
Question by:Mach1pro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
BulZeyE earned 300 total points
ID: 11681104
If you have more than one statement inside of an if statement you have to use BEGIN and END.  I also added an ' = 1 ' to your checks for Isdate..  Try the following:

CREATE   FUNCTION dbo.DaysOff
  (
     
     @dtStartDate smallDatetime,
     @dtEndDate smalldatetime,
     @strStartTime varchar(8),
     @strEndTime varchar(8)
   
     
  )
RETURNS decimal
AS
begin
DECLARE @dtStartTime DateTime,
     @dtEndTime DateTime,
     @sgHours decimal

If DateDiff(day,@dtStartDate , @dtEndDate) > 1
BEGIN
      While @dtStartDate >= @dtEndDate
          Begin
               If DatePart(Weekday,@dtStartDate) > 1 and DatePart(Weekday,@dtStartDate) < 7                        
               Set @sgHours = @sghours + 8          
               set @dtStartDate = DateAdd(day,1,@dtStartDate)              
          end
END
Else


     If Isdate(@strStartTime) = 1
     BEGIN
     
          Set @dtStartTime = Convert(datetime,@strStarttime,108)
          If IsDate(@strEndTime) = 1
          BEGIN
               
               Set @dtEndTime = Convert(DateTime,@strEndTime,108)
               Set @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
          END    
          Else
          BEGIN
               Set @sgHours = 8
          END
     END
     Else
     BEGIN
          Set @sgHours = 8
     END

return @sgHours

end


hth.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11681127
Also, please follow-up on your abandoned questions.  Here are all your open questions:
1 03/30/2004 500 how to use temptable as row source for c...  Open Microsoft Access
2 04/30/2004 250 Subform loses filter in adp when enterin...  Open Microsoft Access
3 05/05/2004 250 How to retain linked value in field when...  Open Microsoft Access
4 07/30/2004 300 Having problems with UDF that uses If st...  Open Microsoft SQL Server
5 07/15/2004 250 Bug crawling script  Open JavaScript

Thanks.
0
 
LVL 4

Expert Comment

by:BulZeyE
ID: 11681225
after looking at your logic, I think this would return the desired results:

CREATE FUNCTION dbo.DaysOff
(
  @dtStartDate  SMALLDATETIME,
  @dtEndDate    SMALLDATETIME,
  @strStartTime VARCHAR(8),
  @strEndTime   VARCHAR(8)
)
RETURNS DECIMAL
AS
BEGIN
  DECLARE @dtStartTime DATETIME,
          @dtEndTime   DATETIME,
          @sgHours     DECIMAL
 
  IF DATEDIFF(DAY,@dtStartDate , @dtEndDate) > 1
  BEGIN
    WHILE @dtStartDate <= @dtEndDate
    BEGIN
      IF DATEPART(WEEKDAY,@dtStartDate) > 1 AND DATEPART(WEEKDAY,@dtStartDate) < 7                        
        SET @sgHours = @sghours + 8          

      SET @dtStartDate = DATEADD(DAY,1,@dtStartDate)    
    END
  END
  ELSE
  IF ISDATE(@strStartTime) = 1 AND ISDATE(@strEndTime) = 1
  BEGIN
    SET @dtStartTime = CONVERT(DATETIME,@strStarttime,108)
    SET @dtEndTime = CONVERT(DATETIME,@strEndTime,108)
    SET @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
  END
  ELSE
  BEGIN
    SET @sgHours = 8
  END
 
  RETURN @sgHours

END
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question