Re: Custom Function in Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Mike" <mikehoar@xxxxxxxxxxxxxxxxxxx> wrote in message
news:1127815905.019772.79200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> I am converting a mdb to an adp with sql server 2000 backend. I have a
> query in the mdb which uses a custom vba function in an expression
> field to work out the number of working days between two dates. This
> function also uses a table of Bank Holiday dates and a bit of ado to
> take Bank Holiday dates out if they fall within the period.
>
> I have figured out how to create a SQL Server Function to work out the
> weekdays between two dates fed in as arguments which I can then use in
> a View or Stored Procedure. However, I am stumped as to how to
> implement the bit about taking out the bank holidays as well, can
> anyone help? I appreciate I could hard code the dates into the SQL
> Server Function but I would prefer to use a table to store the dates so
> that the user can add or change dates as required.
>

You could create a table that contains a row for EVERY day for, say, the
next 50 years. Only about 18000 rows, not a problem. It would have two
columns:

the_date (primary key)
workday (bit)

It would be pretty easy to create a query which, for every date in the
table, sets the value of workday according to whether it's a weekday. Then,
all you need to do is to manually set the bank holidays for as far ahead as
you can/want to. Having done that, counting the working days between two
dates becomes a simple aggregate query.

Or, you could make it a bit smaller by making it a non-working-day table,
containing just Sats and Suns (pre-loaded) and bank holidays. The counting
queries would still be pretty simple.




.

Relevant Pages

  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Access to SQLServer GCE
    ... Actually, as Larry points out, the Access client does a good job of filtering only information that you need. ... If your query is bound to a report, and you open that report supplying a typical "where" for that report, then Access will NOT pull down all the records, but in most cases Access will only pull down the required records and respect your filter. ... So SQL server will respect the conditions and filtering placed into those queries, and therefore only pull down those records you require. ... The suggestion in these cases is to consider using a pass-through query since all of that summing is done before the row comes down the network pipe. ...
    (comp.databases.ms-access)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)