Using a Function in SQL statement

[kwfrazier]

I need to be able to run a function as part of my SQL statement. The function is running some calculations that (when completed) will be averaged by using groups. I have no problems running the function separately, however, I need to create reports that are grouped by user locations. with this particular piece of information the result of the grouping, here's what I have in Access:

SELECT Region, Marketplace, Branch, Avg(Workday([TimeStamp],Now())) AS DaysIgnored
FROM TBL_MasterData
WHERE (((Status)="New&quot)
GROUP BY Region, Marketplace, Branch;

My problem is when I try to run the function in ASP - I can play with the "& WorkDay &" stuff and get some things to return, however, I can't get it to run the actual calculation... The Workday calculation takes two inputs, StartDate and EndDate.

Here's what I've tried (simplified - extra fields removed, just trying to get it to work)

Basic try - just tells me that it is an undefined function
Set rst1 = cnn1.Execute("SELECT Workday(TimeStamp,Now()) AS DaysIgnored FROM TBL_MasterData;&quot

By opening and closing the function in quotes... works, but doesn't pull the TimeStamp field from the table for calculation
Set rst1 = cnn1.Execute("SELECT " & Workday(TimeStamp,Now()) &" AS DaysIgnored FROM TBL_MasterData;&quot

By messing with the quotes to try and get the TimeStamp field, it doesn't like the quotes in the function
Set rst1 = cnn1.Execute("SELECT " & Workday( &" TimeStamp "& ,Now()) &" AS DaysIgnored FROM TBL_MasterData;&quot

Eventually, I'll need to do an AVG on this field then return the results grouped by location info. That's where the dilemma comes in for me - I can run the function outside the SQL statement - but it's rather hard averaging dates, especially when I'm removing holidays from the date difference... the calculation gives me an number that I can then use the AVG on...

Any help is greatly appreciated!



Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer

 

[rudejohn]

Clarification: Workday() is a function in your VB code, or a function of the database?

What database software are you using?

Thanks!

RJ

************
RudeJohn
************

 

[kwfrazier]

Workday() is a function in the ASP code, it is also available in the DB itself which is in Access 2000.

Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer

 

[Chopstik]

If I understand correctly, the TIMESTAMP variable is coming from your database and needs to be referenced when you are executing your SQL statement. If this is so, you need to first retrieve that value into your ASP page separately and then reference it in your SQL statement.

First, create a separate recordset which will pull the TIMESTAMP value you need and set an ASP variable value equal to your returned value. After you have that value, then write your SQL statement and use the value you have just set as your first variable for the Workday() function.

Example:

dim rsTS, myTS, rst1
set rsTS = conn.execute("SELECT timestamp FROM myTable WHERE [condition goes here]")

myTS = rsTS.Fields("timestamp")

Set rst1 = cnn1.Execute("SELECT Workday(myTS,Now()) AS DaysIgnored FROM TBL_MasterData;")

The reason you are having the problem is that you are attempting to run SQL code with a parameter value that doesn't exist yet for a function in the ASP page. It won't have the variable value until you go to retrieve it separately, or unless you call a sub-select in your query (which is another option that just occurred to me). Either way, I hope this helps.

--------------------------------------------------------------------------------------------------------------------------
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
--Douglas Adams

 

[rudejohn]

My first inclination was to say exactly what Chopstik said, which is: you cannot create a query based on part of the query itself. But then again, I don't know much about Microsoft Access. You claim that the Workday() function is available in Access too? I haven't found any record of that function. If it's a function YOU defined in your ASP code, then yes... you must do two separate queries or a subquery.

But, much like the dateadd() function, databases can perform manipulation of the data as it's being pulled out. So the following query:
"Select dateadd('dd',4,myDate) from myTable" would retrieve values which are 4 days ahead of whatever values myDate has (we use this a lot to accomodate for time changes in our appliations).

So really, for a thorough answer, I need more information about the Workday() function itself.

RJ

************
RudeJohn
************

 

[kwfrazier]

Chopstick,

I had actually tried this but ran into a logistical problem. Here's a sample of data:

State City Address TimeStamp
FL Orlando 123Here 8/1/2003
FL Orlando 123Here 8/11/2003
AL Mobile 234There 8/5/2003
AL Mobile 567Not 8/7/2003

I need the results to be the following:
State City Address DaysIgnored(AVG)
FL Orlando 123Here 4
AL Mobile 234There 5
AL Mobile 567Not 3

The problem is doing the grouping with dates involved. It's hard to get an AVG for 8/1/2003 and 8/11/2003 - so I was trying to get the function to run within the SQL statement. With the function running, I can get the integer return value and run the AVG as part of that. I'll retry what you suggest and see if I messed something up. I appreciate the help!

Thanks,

Kenneth Frazier, MCSE, CCA
Network Engineer

 

[Chopstik]

Ok, I might not ken this real well, but here's a shot. The function does exist in your ASP page. It was failing previously because you did not have one of your perimeter values - which should be corrected if you take my earlier suggestion and retrieve it separately. You should then be able to run the Access AVG() function on the resulting integer value once you execute the SQL statement. Essentially, there are two separate actions, the first part to determine the result of the Workday() function and then the SQL statement which would then use that value and return the AVG.

Also, before I forget, I noticed an error in my earlier code. I believe your SQL statement should read something similar to this:

set rst1 = conn.Execute("SELECT Region, Marketplace, Branch, Avg(" & Workday(myTS,Now()) & ") AS DaysIgnored
FROM TBL_MasterData
WHERE (((Status)="New"))
GROUP BY Region, Marketplace, Branch;")

That could probably be changed to be made more efficient, but hopefully it conveys the idea. Hope this helps.

--------------------------------------------------------------------------------------------------------------------------
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
--Douglas Adams