Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23

    Unanswered: Datediff in ASP/Ultradev

    I have some code that returns the difference in days between 2 dates

    <%=Datediff("d", fromdate, nowdate) %>

    This works and the data returned in the browser is correct.

    If I add >=28 as below

    <%=Datediff("d", fromdate, nowdate) >=28 %>

    In the browser I see true or false instead of the number of days. This is not useful as I still want to return the number of days but only if they exceed 28.

    Can anybody tell me what I'm doing wrong or offer some assistance so that I can correct this.

    Many thanks in advance.

    Dave

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    nothing like doing things the easy way....
    Code:
    <%if Datediff("d", fromdate, nowdate) >=28 then response.write(Datediff("d", fromdate, nowdate)) %>

  3. #3
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    Thats a great help and has worked for me. Many thanks.

    Now I see no of days greater than 28. Presents another problem which should keep me busy.

    I need to hide all rows where no data is returned in no of days.

    Thanks again.

    Dave

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    happy to help, if you need assistance with your other problem just yell.

  5. #5
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    I'm yelling.

    Spent nearly all day on this. I guess I need to use IF and THEN, but not sure how to impliment this as the rows I want to hide are where the days are less than 28 and no data is returned. Difficult as this column does not form part of the recordest.

    If you can assist it would be much appreciated.

    Thanks again,

    Dave

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    How do you determine that no data is returned??

    What are the determining factors? Is it if the recordset is empty? If so you would use
    Code:
    if myRecordset.eof then
    myRecordset.eof (end of file) will be true if there are no records or if you have walked through the recordset to the end.

    if you want to combine this with the 28 days issue....
    Code:
    if (Datediff("d", fromdate, nowdate) >=28 ) and not(myRecordset.eof) then
     ' do xyz
    end if
    xyz will only be executed if the date difference is great then 28 days and there are no records...

  7. #7
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    Thanks for your help.
    I'm not sure if I explained myself very well.
    The DateDiff work just fine. The days are counted and displayed only if they match or exceed 28 days. If the number of days is less than 28 then the number of days is not displayed.
    To keep this tidy I would want to hide all rows where the number of days is less than 28.

    Sorry to keep bothering you, but if you can offer further help I would greatly appreciate it.

    Many thanks,

    Dave

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    still not sure that I really understand but.... I assume you currently have something like
    Code:
    <tr><td><%if Datediff("d", fromdate, nowdate) >=28 then response.write(Datediff("d", fromdate, nowdate)) %></td></tr>
    and that is the row bit you are talking about hiding.... if so, try this instead...
    Code:
    <%if Datediff("d", fromdate, nowdate) >=28 then response.write("<tr><td>" & Datediff("d", fromdate, nowdate)) & "</td></tr>"%>

  9. #9
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    At present the code is

    <% if DateDiff("d", fromdate, nowdate) >=28 then response.write ( Datediff ("d", fromdate, nowdate)) %>

    This gives me a table:

    Staff No | Name | Team | Sick Date | No of Days
    ****** | **** | **** | 15/09/02 | 763
    ****** | **** | **** | 11/02/04 | 614
    ****** | **** | **** | 06/10/04 |

    No of Days for Sick Date 06/10/04 shows no data as the no of sick days is less than 28. If the no of sick days is less than 28, I want to hide the row.

    I have played with your extra bit of code, but it does not hide the row.
    Many thanks for your help though, it has been very much appreciated.

    Dave
    Last edited by daveyy; 10-17-04 at 10:00.

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Okie, then at that start of your recordset loop you need to do you check to see if the number of days is > 28 and if it is create a row and if it is not skip to the next record.

    If the code you have given me I can't show you exactly how to do this because it does not contain the entire loop. If you attach allt he code I can give you a working example.

  11. #11
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    Many thanks for looking at this. I have attached the code for the page below, hopefully this will help.


    Code:
    <%@LANGUAGE="VBSCRIPT"%> <%
    set rsLTSick = Server.CreateObject("ADODB.Recordset")
    rsLTSick.ActiveConnection = "dsn=misp;uid=system;pwd=manager;"
    rsLTSick.Source = "SELECT a.staff_no, a.from_date,  (p.first_name || ' ' || p.surname), p.team_ref  FROM absent a, pdb p  WHERE a.to_date is null  and a.staff_no = p.staff_no  and p.leaving_date is null  ORDER BY 2"
    rsLTSick.CursorType = 0
    rsLTSick.CursorLocation = 2
    rsLTSick.LockType = 3
    rsLTSick.Open
    rsLTSick_numRows = 0
    %><%
    Dim Repeat1__numRows
    Repeat1__numRows = -1
    Dim Repeat1__index
    Repeat1__index = 0
    rsLTSick_numRows = rsLTSick_numRows + Repeat1__numRows
    %>
    <%
    '  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
    
    ' set the record count
    rsLTSick_total = rsLTSick.RecordCount
    
    ' set the number of rows displayed on this page
    If (rsLTSick_numRows < 0) Then
      rsLTSick_numRows = rsLTSick_total
    Elseif (rsLTSick_numRows = 0) Then
      rsLTSick_numRows = 1
    End If
    
    ' set the first and last displayed record
    rsLTSick_first = 1
    rsLTSick_last  = rsLTSick_first + rsLTSick_numRows - 1
    
    ' if we have the correct record count, check the other stats
    If (rsLTSick_total <> -1) Then
      If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
      If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
      If (rsLTSick_numRows > rsLTSick_total) Then rsLTSick_numRows = rsLTSick_total
    End If
    %>
    
    <%
    ' *** Recordset Stats: if we don't know the record count, manually count them
    
    If (rsLTSick_total = -1) Then
    
      ' count the total records by iterating through the recordset
      rsLTSick_total=0
      While (Not rsLTSick.EOF)
        rsLTSick_total = rsLTSick_total + 1
        rsLTSick.MoveNext
      Wend
    
      ' reset the cursor to the beginning
      If (rsLTSick.CursorType > 0) Then
        rsLTSick.MoveFirst
      Else
        rsLTSick.Requery
      End If
    
      ' set the number of rows displayed on this page
      If (rsLTSick_numRows < 0 Or rsLTSick_numRows > rsLTSick_total) Then
        rsLTSick_numRows = rsLTSick_total
      End If
    
      ' set the first and last displayed record
      rsLTSick_first = 1
      rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
      If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
      If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
    
    End If
    %>
     
    <SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>					
    function DoDateTime(str, nNamedFormat, nLCID)				
    	dim strRet								
    	dim nOldLCID								
    										
    	strRet = str								
    	If (nLCID > -1) Then							
    		oldLCID = Session.LCID						
    	End If									
    										
    	On Error Resume Next							
    										
    	If (nLCID > -1) Then							
    		Session.LCID = nLCID						
    	End If									
    										
    	If ((nLCID < 0) Or (Session.LCID = nLCID)) Then				
    		strRet = FormatDateTime(str, nNamedFormat)			
    	End If									
    										
    	If (nLCID > -1) Then							
    		Session.LCID = oldLCID						
    	End If									
    										
    	DoDateTime = strRet							
    End Function									
    </SCRIPT>
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF">
    <p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">There are <font color="#FF0033"><%=(rsLTSick_total)%></font> 
      staff with open sick records</font></b></p>
    <p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff below 
      w1th no of sick days have exceeded 28 days sickness and are now Long Term Sick.</font></b> 
    </p>
    <% If Not rsLTSick.EOF Or Not rsLTSick.BOF Then %> 
    
    
    <form name="form1" method="post" action="">
      <table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
        <tr> 
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff 
              No</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Team</b></font></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sick 
              From Date</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">No 
              of Sick Days</font></b></div>
          </td>
        </tr>
        <%
    While ((Repeat1__numRows <> 0) AND (NOT rsLTSick.EOF))
    %> 
        <tr> 
    	 <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("STAFF_NO").Value)%></font></div>
          </td>
          <td nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("(P.FIRST_NAME||''||P.SURNAME)").Value)%></font></td>
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("TEAM_REF").Value)%></font></div>
          </td>
          <td nowrap><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%= DoDateTime((rsLTSick.Fields.Item("FROM_DATE").Value), 1, 1033) %></font></td>
          <% fromdate = (rsLTSick.Fields.Item("FROM_DATE").Value) %> 
          <% nowdate = Now %> 
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><% if DateDiff("d", fromdate, nowdate) >=28 then response.write ( Datediff ("d", fromdate, nowdate)) %></font></div>
        </tr>
    
    
        <%
    Repeat1__index=Repeat1__index+1
    Repeat1__numRows=Repeat1__numRows-1
    rsLTSick.MoveNext()
    Wend
    %> 
      </table>
    </form>
    <% End If ' end Not rsLTSick.EOF Or NOT rsLTSick.BOF %><% If rsLTSick.EOF And rsLTSick.BOF Then %> 
    <form name="form2" method="post" action="">
      <b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">There are no 
      staff with open sick records.</font></b> 
    </form>
    <% End If ' end rsLTSick.EOF And rsLTSick %> 
    <p>&nbsp;</p>
    </body>
    </html>

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Note the added bold bits.... this should take care of things for you
    Code:
    <%@LANGUAGE="VBSCRIPT"%> <%
    set rsLTSick = Server.CreateObject("ADODB.Recordset")
    rsLTSick.ActiveConnection = "dsn=misp;uid=system;pwd=manager;"
    rsLTSick.Source = "SELECT a.staff_no, a.from_date,  (p.first_name || ' ' || p.surname), p.team_ref  FROM absent a, pdb p  WHERE a.to_date is null  and a.staff_no = p.staff_no  and p.leaving_date is null  ORDER BY 2"
    rsLTSick.CursorType = 0
    rsLTSick.CursorLocation = 2
    rsLTSick.LockType = 3
    rsLTSick.Open
    rsLTSick_numRows = 0
    %><%
    Dim Repeat1__numRows
    Repeat1__numRows = -1
    Dim Repeat1__index
    Repeat1__index = 0
    rsLTSick_numRows = rsLTSick_numRows + Repeat1__numRows
    %>
    <%
    '  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
    
    ' set the record count
    rsLTSick_total = rsLTSick.RecordCount
    
    ' set the number of rows displayed on this page
    If (rsLTSick_numRows < 0) Then
      rsLTSick_numRows = rsLTSick_total
    Elseif (rsLTSick_numRows = 0) Then
      rsLTSick_numRows = 1
    End If
    
    ' set the first and last displayed record
    rsLTSick_first = 1
    rsLTSick_last  = rsLTSick_first + rsLTSick_numRows - 1
    
    ' if we have the correct record count, check the other stats
    If (rsLTSick_total <> -1) Then
      If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
      If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
      If (rsLTSick_numRows > rsLTSick_total) Then rsLTSick_numRows = rsLTSick_total
    End If
    %>
    
    <%
    ' *** Recordset Stats: if we don't know the record count, manually count them
    
    If (rsLTSick_total = -1) Then
    
      ' count the total records by iterating through the recordset
      rsLTSick_total=0
      While (Not rsLTSick.EOF)
        rsLTSick_total = rsLTSick_total + 1
        rsLTSick.MoveNext
      Wend
    
      ' reset the cursor to the beginning
      If (rsLTSick.CursorType > 0) Then
        rsLTSick.MoveFirst
      Else
        rsLTSick.Requery
      End If
    
      ' set the number of rows displayed on this page
      If (rsLTSick_numRows < 0 Or rsLTSick_numRows > rsLTSick_total) Then
        rsLTSick_numRows = rsLTSick_total
      End If
    
      ' set the first and last displayed record
      rsLTSick_first = 1
      rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
      If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
      If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
    
    End If
    %>
     
    <SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>		
    function DoDateTime(str, nNamedFormat, nLCID)				dim strRet							dim nOldLCID							strRet = str							If (nLCID > -1) Then
    		oldLCID = Session.LCID
    	End If			
    
    	On Error Resume Next
    		
    	If (nLCID > -1) Then							Session.LCID = nLCID					End If															
    	If ((nLCID < 0) Or (Session.LCID = nLCID)) Then				strRet = FormatDateTime(str, nNamedFormat)			End If									
    	If (nLCID > -1) Then
    		Session.LCID = oldLCID
    	End If
    	DoDateTime = strRet
    End Function
    </SCRIPT>
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF">
    <p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">There are <font color="#FF0033"><%=(rsLTSick_total)%></font> 
      staff with open sick records</font></b></p>
    <p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff below 
      w1th no of sick days have exceeded 28 days sickness and are now Long Term Sick.</font></b> 
    </p>
    <% If Not rsLTSick.EOF Or Not rsLTSick.BOF Then %> 
    
    
    <form name="form1" method="post" action="">
      <table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
        <tr> 
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff 
              No</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Team</b></font></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sick 
              From Date</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">No 
              of Sick Days</font></b></div>
          </td>
        </tr>
        <%
    While ((Repeat1__numRows <> 0) AND (NOT rsLTSick.EOF))
      if DateDiff("d", fromdate, nowdate) >=28 then 
    %> 
        <tr> 
    	 <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("STAFF_NO").Value)%></font></div>
          </td>
          <td nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("(P.FIRST_NAME||''||P.SURNAME)").Value)%></font></td>
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("TEAM_REF").Value)%></font></div>
          </td>
          <td nowrap><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%= DoDateTime((rsLTSick.Fields.Item("FROM_DATE").Value), 1, 1033) %></font></td>
          <% fromdate = (rsLTSick.Fields.Item("FROM_DATE").Value) %> 
          <% nowdate = Now %> 
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><% if DateDiff("d", fromdate, nowdate) >=28 then response.write ( Datediff ("d", fromdate, nowdate)) %></font></div>
        </tr>
    
    
        <%
      end if
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rsLTSick.MoveNext()
    Wend
    %> 
      </table>
    </form>
    <% End If ' end Not rsLTSick.EOF Or NOT rsLTSick.BOF %><% If rsLTSick.EOF And rsLTSick.BOF Then %> 
    <form name="form2" method="post" action="">
      <b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">There are no 
      staff with open sick records.</font></b> 
    </form>
    <% End If ' end rsLTSick.EOF And rsLTSick %> 
    <p>&nbsp;</p>
    </body>
    </html>

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you could also do this which I think will work and is a little better....
    Code:
    <%@LANGUAGE="VBSCRIPT"%> <%
    set rsLTSick = Server.CreateObject("ADODB.Recordset")
    rsLTSick.ActiveConnection = "dsn=misp;uid=system;pwd=manager;"
    rsLTSick.Source = "SELECT a.staff_no, a.from_date,  (p.first_name || ' ' || p.surname), p.team_ref  FROM absent a, pdb p  WHERE a.to_date is null  and a.staff_no = p.staff_no  and p.leaving_date is null  ORDER BY 2"
    rsLTSick.CursorType = 0
    rsLTSick.CursorLocation = 2
    rsLTSick.LockType = 3
    rsLTSick.Open
    rsLTSick_numRows = 0
    %><%
    Dim Repeat1__numRows
    Repeat1__numRows = -1
    Dim Repeat1__index
    Repeat1__index = 0
    rsLTSick_numRows = rsLTSick_numRows + Repeat1__numRows
    %>
    <%
    '  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
    
    ' set the record count
    rsLTSick_total = rsLTSick.RecordCount
    
    ' set the number of rows displayed on this page
    If (rsLTSick_numRows < 0) Then
      rsLTSick_numRows = rsLTSick_total
    Elseif (rsLTSick_numRows = 0) Then
      rsLTSick_numRows = 1
    End If
    
    ' set the first and last displayed record
    rsLTSick_first = 1
    rsLTSick_last  = rsLTSick_first + rsLTSick_numRows - 1
    
    ' if we have the correct record count, check the other stats
    If (rsLTSick_total <> -1) Then
      If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
      If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
      If (rsLTSick_numRows > rsLTSick_total) Then rsLTSick_numRows = rsLTSick_total
    End If
    %>
    
    <%
    ' *** Recordset Stats: if we don't know the record count, manually count them
    
    If (rsLTSick_total = -1) Then
    
      ' count the total records by iterating through the recordset
      rsLTSick_total=0
      While (Not rsLTSick.EOF)
        rsLTSick_total = rsLTSick_total + 1
        rsLTSick.MoveNext
      Wend
    
      ' reset the cursor to the beginning
      If (rsLTSick.CursorType > 0) Then
        rsLTSick.MoveFirst
      Else
        rsLTSick.Requery
      End If
    
      ' set the number of rows displayed on this page
      If (rsLTSick_numRows < 0 Or rsLTSick_numRows > rsLTSick_total) Then
        rsLTSick_numRows = rsLTSick_total
      End If
    
      ' set the first and last displayed record
      rsLTSick_first = 1
      rsLTSick_last = rsLTSick_first + rsLTSick_numRows - 1
      If (rsLTSick_first > rsLTSick_total) Then rsLTSick_first = rsLTSick_total
      If (rsLTSick_last > rsLTSick_total) Then rsLTSick_last = rsLTSick_total
    
    End If
    %>
     
    <SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>					
    function DoDateTime(str, nNamedFormat, nLCID)				
    	dim strRet								
    	dim nOldLCID								
    										
    	strRet = str								
    	If (nLCID > -1) Then							
    		oldLCID = Session.LCID						
    	End If									
    										
    	On Error Resume Next							
    										
    	If (nLCID > -1) Then							
    		Session.LCID = nLCID						
    	End If									
    										
    	If ((nLCID < 0) Or (Session.LCID = nLCID)) Then				
    		strRet = FormatDateTime(str, nNamedFormat)			
    	End If									
    										
    	If (nLCID > -1) Then							
    		Session.LCID = oldLCID						
    	End If									
    										
    	DoDateTime = strRet							
    End Function									
    </SCRIPT>
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF">
    <p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="3">There are <font color="#FF0033"><%=(rsLTSick_total)%></font> 
      staff with open sick records</font></b></p>
    <p><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff below 
      w1th no of sick days have exceeded 28 days sickness and are now Long Term Sick.</font></b> 
    </p>
    <% If Not rsLTSick.EOF Or Not rsLTSick.BOF Then %> 
    
    
    <form name="form1" method="post" action="">
      <table width="80%" border="1" cellspacing="0" cellpadding="0" align="center">
        <tr> 
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Staff 
              No</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Name</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Team</b></font></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">Sick 
              From Date</font></b></div>
          </td>
          <td nowrap> 
            <div align="center"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">No 
              of Sick Days</font></b></div>
          </td>
        </tr>
        <%
    
    Dim iNumSickDays
    While ((Repeat1__numRows <> 0) AND (NOT rsLTSick.EOF))
      iNumSickDays = DateDiff("d", fromdate, nowdate) 
      if iNumSickDays >=28 then 
    %> 
        <tr> 
    	 <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("STAFF_NO").Value)%></font></div>
          </td>
          <td nowrap><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("(P.FIRST_NAME||''||P.SURNAME)").Value)%></font></td>
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=(rsLTSick.Fields.Item("TEAM_REF").Value)%></font></div>
          </td>
          <td nowrap><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><%= DoDateTime((rsLTSick.Fields.Item("FROM_DATE").Value), 1, 1033) %></font></td>
          <% fromdate = (rsLTSick.Fields.Item("FROM_DATE").Value) %> 
          <% nowdate = Now %> 
          <td nowrap> 
            <div align="center"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%=iNumSickDays%></font></div>
        </tr>
    
    
        <%
      end if
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rsLTSick.MoveNext()
    Wend
    %> 
      </table>
    </form>
    <% End If ' end Not rsLTSick.EOF Or NOT rsLTSick.BOF %><% If rsLTSick.EOF And rsLTSick.BOF Then %> 
    <form name="form2" method="post" action="">
      <b><font face="Verdana, Arial, Helvetica, sans-serif" size="2">There are no 
      staff with open sick records.</font></b> 
    </form>
    <% End If ' end rsLTSick.EOF And rsLTSick %> 
    <p>&nbsp;</p>
    </body>
    </html>

  14. #14
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Oh my.. why don't you just put the DATEDIFF functions in the SQL?

    SELECT DATEDIFF('d', fromdate, todate) AS SickDays, ... FROM ... WHERE DATEDIFF('d', fromdate, todate) >= 28 AND ...

    Note, the ellipses (...) are representative of the rest of your query. fromdate and todate are place holders for the actuall columns which contain dates to be evaluated. If you are evaluation from a stored date and the current date, replace "todate" with "GETDATE()" and the server will call the GETDATE() function to insert the current date/time.

    Now you only get the rows that are equal to or greater than 28 days, and it includes a column with that aggregate value. No ASP required! Much faster and efficient...
    That which does not kill me postpones the inevitable.

  15. #15
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I should note that the SQL presented would work with MS SQL Server.. I can't guarantee it will work elsewhere... but there should be adequate alternatives for whatever database you're using... I think even MS Access would work with that query, but not positive... DATEDIFF in this context is not an ASP function, but instead, a SQL function, they just so happen to have the same function name...
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •