Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL for Daily Quota (any)

    I am attempting to expand an existing SELECT Query to measure daily bandwidth usage against a monthly quota. As the monthly quota is fixed, the daily quota will vary. <!profile=MarkD>MarkD<!/profile> has posted a good first step in VBA viz.<pre>Public Function GetDaysInMonth(dtDate As Date) As Integer
    GetDaysInMonth = Day(DateSerial(Year(dtDate), Month(dtDate) + 1, 0))
    End Function</pre>

    The problem is that I need to extract the Days In A Month in SQL. Any suggestions?
    Gre

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL for Daily Quota (any)

    You can use VBA functions in Access queries, and you can execute SQL (including VBA functions) using DoCmd.RunSQL Does that help, or do you need something else?

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL for Daily Quota (any)

    No, that's fine. There was some comment about UDFs affecting performance. Presumably, the DB has to be fairly chunky (and resources stingy) before that matters?
    Gre

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL for Daily Quota (any)

    Since a VBA function has to be evaluated for each record, it can slow down execution of queries. How much depends on the complexity of the function and on the number of records. Your function is very simple, so even with thousands of records, you should hardly notice the effect. With hundreds of thousands of records, it might be noticeable.

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL for Daily Quota (any)

    In reference to UDF's & performance, MS sometimes notes possible adverse affect in MSKB articles. Example:

    Query performance decreases when the criteria in a Microsoft JET 4.0 query contains a user-defined function

    What I try to do where possible is to avoid UDF in query, where intrinsic VB/VBA functions can be used in the query expression directly. Presumably, the Jet db engine has been optimized for using the VB/VBA intrinsic functions. Here is example SQL, query based on Northwind.mdb Orders table:

    SELECT Orders.OrderID, Orders.OrderDate, Format$([OrderDate],"mmmm") AS [Order Month], Day(DateSerial(Year([OrderDate]),Month([OrderDate])+1,0)) AS [Days in Month]
    FROM Orders
    ORDER BY Orders.OrderDate;

    The 2 calculated fields display Order month (spelled out) and number of days in Order month. There is no noticeable delay when query executed (but then there's only 800 or so records). But with some UDF's or when using Domain Aggregate functions such as DLookup, etc, there will be a visible delay when query runs, even with a relatively few number of records, so these type of functions should be avoided if possible in query.

    HTH

Posting Permissions

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