Converting some VBA code to SQL Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting some VBA code to SQL Function

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-06 : 02:24:49

Hi,
I'm new to this SQL thing, and I need some help with s stored procedure/function.
First, the scenario:
We are a social service agency, and like all such organizations, we have requirements for perodic reports about our clients. There are lots of them, but if I figure how to do one, I think I can apply the theory to the others.

First, this is some sample VBA code I've tested and which proved satisfactory for the basic task, just for a form. It is passed a date (dteDOP), then adds 3 months, (quarterly report)and loops until it exceeds the current date, thus generating the a due date for the the next report.

'initialize the due date
dteDueDate = dteDOP

'Add quarterly intervals, starting with (DOP + 3 months), until
'you exceed today's date
Do Until dteDueDate > Date
dteDueDate = DateAdd("m", 3, dteDueDate)
Loop
'set a text box to the next due date after today
Me.txtNextQuarDue = dteDueDate

Obviously, this isn't really a function at the moment, but it worked as a test of the logic, with instantly visible results.

This works, but I'd like to do it on the server end, so I can send out notifications. How would this be accomplished as an SQL stored procedure/function? Obviously, for that I'll need to again do a comparison of the current date with the due date for timing concerns, but that should be relatively simple. Also, I know that the '@' symbol is somehow part of variables in SQL Procedures/functions, could you give me a very basic explanation of this, especially the difference between @xxx and @@xxx?

Thanks in advance,
Stephen

-----------------------------------------------
-----------------------------------------------
Gary Getsum: What happened to my mule?
DM: It's dead; It got stung by a giant wasp.
Gary: Can't I heal it?
DM: I'm sorry, I know you were fond of the mule, but it just got attacked by a wasp the size of a Volkswagen!
Gary: So?
DM: Dude, look- you're going to have to carry all your own treasure now... Your mule gives new meaning to the term "Puff Daddy".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 02:39:09

Something like this?

declare @dop datetime

select @dop = '20060315'

SELECT	DATEADD(month, (3 - DATEDIFF(month, @dop, GETDATE()) % 3) % 3 + DATEDIFF(month, @dop, GETDATE()), @dop)

Peter Larsson
Helsingborg, Sweden

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-06 : 02:58:36

quote:
Something like this?

Ummm. Gee, I have no idea.
Let me try to parse this.
declare @dop datetime
Ok, that's fairly obvious.
select @dop = '20060315'
I'm not sure what this is... initializing the value of @dop to March 15th, 2006?

SELECT DATEADD(month, (3 - DATEDIFF(month, @dop, GETDATE()) % 3) % 3 + DATEDIFF(month, @dop, GETDATE()), @dop)
Ok, This lost me completely. Hmmm...Add months equal to 3 minus the difference in months between @dop and today.. What is %3?

Do tell, please. I'm not dense, just not much of a coder. I do ok for someone that taught himself, I guess.

-----------------------------------------------
-----------------------------------------------
Gary Getsum: What happened to my mule?
DM: It's dead; It got stung by a giant wasp.
Gary: Can't I heal it?
DM: I'm sorry, I know you were fond of the mule, but it just got attacked by a wasp the size of a Volkswagen!
Gary: So?
DM: Dude, look- you're going to have to carry all your own treasure now... Your mule gives new meaning to the term "Puff Daddy".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 03:21:08

@dop is the dteDOP variable.

aka next line initializes the date to a start value.

third and last line calculates how many months there are between dteDOP and today, and adds a number of months so that the added value is evenly divided with 3 (a quarter).

test run the code with different dates for @dop and you will see that the query will return the same date as your VBA code, without looping.

Peter Larsson
Helsingborg, Sweden

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-09-06 : 03:51:07

quote:
the difference between @xxx and @@xxx?

@xxx is for local variables
@@xxx is for Global variables. There are quite a few system ones that can sometimes be useful.

quote:
select @dop = '20060315'
I'm not sure what this is... initializing the value of @dop to March 15th, 2006?

Yes your right here, the date is in ISO format i.e. yyyymmdd which in my opinion is the best way to represent dates without causing confusion

I suggest you get (if you haven't already) Books Online (BOL) installed as these are the SQL Server help files and contain masses of useful information. When that doesn't make sense you can always leave a message here.

The other thing that's useful to bear in mind is that SQL is SET based i.e. it's very good at doing the same thing to a set of data at a time rather than linearly as you may do in VBA for example, again people here will help if you get stuck.

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-06 : 12:21:41

quote:
Originally posted by Peso

...third and last line calculates how many months there are between dteDOP and today, and adds a number of months so that the added value is evenly divided with 3 (a quarter).

Thanks! I did install the BOL, and looked up the % thing, modulo.
I'm sure it's a much more elegant solution, which just goes to show that taking BASIC on a Commodore VIC-20 in 1982 didn't exactly prepare me to become a professional developer.

quote:
test run the code with different dates for @dop and you will see that the query will return the same date as your VBA code, without looping.

I see that, yes. It works splendidly, which is not at all surprising. It returns the column as expr<1>. I fiddled around with it, trying to make it return the column as 'DueDate', but couldn't get it to work.

After some BOL review, I'm still a bit confused. I'd like this to be a function, so (I know this is wrong)...but any comments would be hepful
CREATE FUNCTION DueDate (@DOP AS DATETIME, @INTERVAL AS TINYINT)
RETURNS DATETIME

BEGIN
declare @dop datetime
declare @interval tinyint

select @dop = '20060315'
select @Interval = 6

RETURN DATEADD(month, (3 - DATEDIFF(month, @dop, GETDATE()) %@Interval) % @interval + DATEDIFF(month, @dop, GETDATE()), @dop)END

-----------------------------------------------
-----------------------------------------------
Gary Getsum: What happened to my mule?
DM: It's dead; It got stung by a giant wasp.
Gary: Can't I heal it?
DM: I'm sorry, I know you were fond of the mule, but it just got attacked by a wasp the size of a Volkswagen!
Gary: So?
DM: Dude, look- you're going to have to carry all your own treasure now... Your mule gives new meaning to the term "Puff Daddy".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 12:35:56

Use this function

CREATE FUNCTION fnDueDate
(
	@DOP AS DATETIME,
	@INTERVAL AS TINYINT,
	@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
	RETURN DATEADD(month, (3 - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
END

and call with

SELECT dbo.fnDueDate('20060315', 3, GETDATE())

Peter Larsson
Helsingborg, Sweden

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-06 : 13:06:21

Before I go ahead with this, should that first "3" in

quote:
RETURN DATEADD(month, (3 - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop

be replaced with @interval to return the correct date if the interval is not quarters, but say 6 months, as in
SELECT dbo.fnDueDate('20060315', 6, GETDATE()), and... if the front end uses a different date format, MM/DD/YYYY, will it still work?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 01:28:45

Yes it should, my mistake.

CREATE FUNCTION fnDueDate
(
	@DOP AS DATETIME,
	@INTERVAL AS TINYINT,
	@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
	RETURN DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
END

And it doesn't matter which format for the date you use. SQL Server interprets the date correctly anyway, as long as you don't violate the SET DATEFORMAT setting, which in most cases is MDY in US of A.

As Elwoos wrote, ISO format always work; YYYYMMDD.

Call with SELECT dbo.fnDueDate(<your date>, <interval>, <today or any other date you want to compare with>).
SELECT dbo.fnDueDate('20060811', 4, GETDATE())

Peter Larsson
Helsingborg, Sweden

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-09-07 : 03:48:38

quote:
And it doesn't matter which format for the date you use.

Peso is absolutely correct here, I didn't explain what I meant very well. What I was getting at is that (particularly on forums) the ISO format for dates is far less ambiguous.

The mod function can be very useful for this sort of thing. It's certainly a function that is useful to be aware of.

quote:
I'm sure it's a much more elegant solution, which just goes to show that taking BASIC on a Commodore VIC-20 in 1982

On the contrary...

quote:
It returns the column as expr<1>. I fiddled around with it, trying to make it return the column as 'DueDate', but couldn't get it to work.

In a normal SELECT statement if you want to return a different field name than that in the table you would use an alias e.g.

SELECT
SomeField as MyField
From Mytable

would return the fieldname as MyField. So if you are returning one column from your function as in this case you could use

SELECT dbo.tmp_fnDueDate('20060811', 4, GETDATE()) as MyAlias

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-07 : 11:02:07

I tried to paste this code into the designer in Access; it insists that it has errors, so it won't save. This is the message: " ADO Error: Incorrect Syntax near the keyword CREATE. CREATE FUNCTION must the first statement in a query batch. Must Declare the variable @Interval.

This is very strange.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 11:10:33

No, it's not. This is a SQL Server query, as the FORUM name tells you.

It's an ACCESS query you want help with? There is a special forum for that.
Stick with the VBA code you originally had, and put a FUNCTION wrapper around it.

Peter Larsson
Helsingborg, Sweden

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-07 : 11:15:15

Oh, hey. Nevermind! The problem is MS-ACCESS (big surprise). I created the function directly in DBAMgr2K (I love free tools that look and perform exactly like expensive ones!). It even prompts for the arguments when it runs. Since this is all for a nonprofit (with the eventual goal of selling it to other nonprofits), I am using MSDE and the free management tool. All of the prospective customers already have Office, so they won't need big servers and expensive software to use it. I's rather use VB, but I just don't have the skill...

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-07 : 15:30:36

quote:
Originally posted by Peso

It's an ACCESS query you want help with? There is a special forum for that.

To clarify:
The project is an Access ADP, connecting to MSDE, which as near as I can tell is a fully SQL-Server compatible platform (not that I'm much of a judge). I'm attempting to use the back-end as much as possible for queries, functions, procedures, and security, so that the application side isn't so dependent on the forms and modules within the front-end. I'm hoping this will mean that it will be more scalable, convert to true SQL Server easily, if necessary, and lend itself to conversion to VB, ASP, etc. on the front end, if and when that becomes an option. Access is the easist way to provide basic functionality/proof of concept, and also is a platform most businesses already are licensed to use. Any comments on this theory are welcome, of course, but you can at least now understand why I was posting here and not in the Access forum.

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-11 : 10:02:19

Hey Peso,
If you're still watching this thread, I found a bug, and I can't seem to fix it. If DOP and ThisDate (today) are the same, it returns the next interval as today, instead of ThisDate + interval. I played around with it a little, but I can't make it work. Could you help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 10:13:11

You mean something like this?

CREATE FUNCTION fnDueDate
(
	@DOP AS DATETIME,
	@INTERVAL AS TINYINT,
	@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
	DECLARE	@Temp DATETIME

	IF @dop = @ThisDate
		SELECT	@Temp = @ThisDate
	ELSE
		SELECT	@Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)

	RETURN @Temp
END

Peter Larsson
Helsingborg, Sweden

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-11 : 11:27:47

That code returned the same thing, but... It helped a lot.
This worked-

CREATE FUNCTION dbo.fnNewDueDate
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
DECLARE @Temp DATETIME
IF @dop = @ThisDate
SELECT @Temp = DATEADD(month, @Interval, @dop)
ELSE
SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
RETURN @Temp
END

It was simple, really. I think my original attempts to fix it violated Occams Razor, i.e. KISS. Since the original issue was to generate lots of future dates, the solution for the *first* date was just to add the interval. Silly me.
Thanks for

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-11 : 11:35:46

OOPS!
I kept it a little too simple!
The code I posted previously only worked for today. If ThisDate was changed to tomorrow, or any other day less than one month from DOP, the original problem returned.

Solution:
ALTER FUNCTION dbo.fnNewDueDate
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
DECLARE @Temp DATETIME
IF @dop <= @ThisDate +(DATEADD(month,1,@dop))
SELECT @Temp = DATEADD(month, @Interval, @dop)
ELSE
SELECT @Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
RETURN @Temp
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources