Need Help Creating T-SQL Function from VBA

Question

Post reply

Need Help Creating T-SQL Function from VBA

syed087

Grasshopper

Points: 13
More actions
March 23, 2010 at 9:56 am

#156811

Hello Everyone,
I'm really hoping someone can help me. I need to convert a function that I wrote in an Access database using VB to a scalar function in SS2K5 using T-SQL. The function takes any given date and returns the date of the same day (monday, tuesday, etc.) from the prior year. I am using this in a forecasting model.
On a side note, currently in the Access db, this function is called in a query as a parameter defined by the user. So in SQL, I'll need my views to call the new T-SQL function.
This is the function:
Public Function PYWD(d As Date) As Date
Dim thisWd As Integer 'the day of the week d falls on
Dim thisWk As Integer 'the week of th year d falls on
Dim lastWd As Integer '
Dim lastSun As Date
Dim lastYr As Date
Dim finalDt As Date
thisWd = Weekday(d)
thisWk = Format(d, "ww")
lastYr = Format(DateAdd("yyyy", -1, d), "1/01/yyyy")
lastWk = DateAdd("ww", thisWk - 1, lastYr)
lastWd = Weekday(lastWk)
lastSun = DateAdd("d", (lastWd - 1) * -1, lastWk)
finalDt = DateAdd("d", thisWd - 1, lastSun)
PYWD = finalDt
End Function
Kind Regards,
Syed

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The main question is: what logic do you use to define the week number?

Are you using ISO week, SQL standard (January 1st = week 1), DATEFIRST dependent or not or something else?

Based on that information it's more (or less) complicated...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Just to be sure we are all on the same page, could you post an example of the input and expected output?

Also, could you show us how you see this function being used? I waould also write it as an in-line TVF instead of a scalar function.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is some sample code, pay attention to the second select query, it doesn't use the in-line TVF.

CREATE TABLE #TestTable

( id INT,

c1 DATETIME

)

INSERT INTO #TestTable

SELECT 1,GETDATE() UNION ALL

SELECT 2,GETDATE() -4

GO

create function dbo.PriorYearDayName(

@ThisDate datetime

)

returns table

as

return (select datename(dw,dateadd(yy, -1, @ThisDate)) as DayName);

go

select

tt.id,

tt.c1,

dateadd(yy,-1,tt.c1) as LastYear,

pydn.DayName

from

#TestTable tt

cross apply dbo.PriorYearDayName(tt.c1) pydn;

go

select

tt.id,

tt.c1,

datename(dw,dateadd(yy, -1, tt.c1)) as DayName

from

#TestTable tt;