Custom Function in Query

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

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.
 
Can you not modify the SQL Server function you've created to read from a SQL
Server table?
 
Mike said:
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.
Click to expand...

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.
 
I thought you could not do this in a function. If I try putting a
SELECT statement in the function I get an error.
 
To be honest, I've never created functions in SQL Server, but I was under
the impression that you could use cursors and the like, just as in Stored
Procedures.
 
Mike said:
I thought you could not do this in a function. If I try putting a
SELECT statement in the function I get an error.
Click to expand...

You can not just execute a plain SELECT-Statement to create a
resultset within a function as you can within a stored procedure.

However you can use a SELECT-Satement to assign a value to a
variable and use that variable as return-value of the function.

simple sample:

CREATE FUNCTION dbo.GetFooCount (
@criteria varchar(100)
)
RETURNS int
AS
BEGIN

DECLARE @retVal int

SELECT @retVal = COUNT(*)
FROM dbo.tblFoo
WHERE Something = @criteria

RETURN @retVal

END
GO


You may also create a table-function that does return a a table
instead of a scalar value. The details of this approach are
beyond the scope of what I've time to explain, so please refer to
the documentation for durther details.

Cheers
Phil
 
Douglas J. Steele said:
To be honest, I've never created functions in SQL Server, but I was under
the impression that you could use cursors and the like, just as in Stored
Procedures.
Click to expand...

Up to a point, but it will not let you use non-deterministic functions
within your own functions, which may be what is causing his error. Or
possibly not, since he doesn't say what the error actually is.
 
Thanks, this was the solution, I was just not familiar with what you
could do in SQL Server
 
Back
Top