Solved

Having problems with UDF that uses If statements and while loop

Posted on 2004-07-30
3
761 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.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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