Solved

Having problems with UDF that uses If statements and while loop

Posted on 2004-07-30
3
751 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now