August 24, 2016 at 3:22 am
Good Morning
I have a request to calculate de holidays of districts for a given year
To calculate them i have a formula in a column of the Districts table
I reach this solution, it works fine, but it seems uggly, i'm asking for a better solution
i can create any function to populate that column
Take this simplified example:
i have my table with the "code" of the function in the FNC column
i have 4 functions (could have many more)
than i have a procedure to list the table with the new calculated column
i tryed to create a function that uses sp_execsql to calculate the given function, but as you know is not possible ...
Any ideas?
CREATE FUNCTION dbo.dummyFuntion1(@YEAR INT) RETURNS INT
AS
BEGIN
DECLARE@ret INT
SET @ret = @YEAR
RETURN @ret
END
CREATE FUNCTION dbo.dummyFuntion2(@YEAR INT) RETURNS INT
AS
BEGIN
DECLARE@ret INT
SET @ret = @YEAR + 1
RETURN @ret
END
CREATE FUNCTION dbo.dummyFuntion3(@YEAR INT) RETURNS INT
AS
BEGIN
DECLARE@ret INT
SET @ret = @YEAR - 1
RETURN @ret
END
CREATE FUNCTION dbo.dummyFuntion4(@YEAR INT) RETURNS INT
AS
BEGIN
DECLARE@ret INT
SET @ret = @YEAR - 2
RETURN @ret
END
CREATE TABLE MainTable (ID INT identity(1,1), FNC VARCHAR(200))
INSERT INTO MainTable VALUES('dbo.dummyFuntion1(@YEAR)'),('dbo.dummyFuntion2(@YEAR)'),('dbo.dummyFuntion3(@YEAR)'),('dbo.dummyFuntion4(@YEAR)')
SELECT * FROM MainTable
CREATE PROCEDURE dbo.dummyProcedure
@YEAR INT
AS
BEGIN
DECLARE @query VARCHAR(MAX)
SET @query = ''
SELECT @query = @query + COALESCE(
' SELECT ID, FNC, ' + REPLACE( FNC, '@YEAR', CAST(@YEAR AS CHAR(4)) ) + ' AS Calculated '
+ ' FROM MainTable WHERE ID = ' + CAST(ID AS VARCHAR(100)) + '
UNION ALL '
, '')
FROMMainTable
SET @query = SUBSTRING(@query, 1, LEN(@query) - LEN(' UNIONALL '))
PRINT @query
EXEC (@query)
END
EXEC dbo.dummyProcedure 2016
DROP FUNCTION dbo.dummyFuntion1
DROP FUNCTION dbo.dummyFuntion2
DROP FUNCTION dbo.dummyFuntion3
DROP FUNCTION dbo.dummyFuntion4
DROP TABLE MainTable
DROP PROCEDURE dbo.dummyProcedure
August 24, 2016 at 5:56 am
Hats off to you for recognising that this solution can be improved and seeking advice.
Most folks would recommend a table holding the dates and districts, but calculating the holidays on the fly doesn't have to be costly (except for Easter, which is a fiendish calculation). Rather than using scalar UDF's for the calculation, have you considered using one or more inline table-valued functions? You could then join the function in the query just like any other table source without having to use messy dynamic sql.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 24, 2016 at 6:45 am
Thanks for the response
There are not big performance issues, the table has only 400 rows
but is for the sake of making it better, and learn something with it
what do you mean with "inline table-valued functions" ?
Just for an example one of the holidays to calculate is:
"Monday after 1º saturday of October"
and in function column i have
dbo.fncProcurarData( DATEADD(D, 0,DATEADD(M, 9, DATEADD(YY, @Year-YEAR(0), 0))) , 1 , 7 , 2 )
where the first parameter is to calculate the first day of October,
the second is the number of iteractions, (1º)
the third is the weekday to search for ( 7 = Saturday )
and the last one is the weekday to return ( 2 = Monday)
i have 4 UDF of this kind (one just for Easter)
how can i turn this into "inline table-valued functions" ?
August 24, 2016 at 6:54 am
paulo.margarido (8/24/2016)
Thanks for the responseThere are not big performance issues, the table has only 400 rows
but is for the sake of making it better, and learn something with it
what do you mean with "inline table-valued functions" ?
Just for an example one of the holidays to calculate is:
"Monday after 1º saturday of October"
and in function column i have
dbo.fncProcurarData( DATEADD(D, 0,DATEADD(M, 9, DATEADD(YY, @Year-YEAR(0), 0))) , 1 , 7 , 2 )
where the first parameter is to calculate the first day of October,
the second is the number of iteractions, (1º)
the third is the weekday to search for ( 7 = Saturday )
and the last one is the weekday to return ( 2 = Monday)
i have 4 UDF of this kind (one just for Easter)
how can i turn this into "inline table-valued functions" ?
Here's a starter for you[/url], covering both types of table-valued functions.
Can you show what output you require from this process?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 24, 2016 at 7:22 am
i'm aware of what "inline table-valued functions" are, i have a few of my own in the DB
what i ask is how can i do it that way
if you can, using that simple sample i posted, how would you turn that to a table-valued function ?
August 24, 2016 at 7:31 am
some sample return data:
FuncaoResultdate
------------------------------------------------------------------------------------------------------------------------------------------
DATEADD(d, 39, dbo.fncDiaPascoa(@Year))2016-05-05 00:00:00.000
dbo.fncProcurarData(DATEADD(D, 0,DATEADD(M, 9, DATEADD(YY, @Year-YEAR(0), 0))), 1, 7, 2 )2016-10-03 00:00:00.000
dbo.fncCalcularData(DATEADD(D, 0,DATEADD(M, 7, DATEADD(YY, @Year-YEAR(0), 0))), 2, 4)2016-08-29 00:00:00.000
fncDiaPascoa() gives easter day for given year
fncProcurarData() gives day after some other day, say "monday after 1º suturday of october"
fncCalcularData() gives nº weekday of mont, say "4º monday of August"
August 24, 2016 at 7:36 am
paulo.margarido (8/24/2016)
i'm aware of what "inline table-valued functions" are, i have a few of my own in the DBwhat i ask is how can i do it that way
if you can, using that simple sample i posted, how would you turn that to a table-valued function ?
I can't, because I've no idea what output you expect from this process.
Here's a holiday-calculating iTVF.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 24, 2016 at 7:38 am
paulo.margarido (8/24/2016)
some sample return data:
FuncaoResultdate
------------------------------------------------------------------------------------------------------------------------------------------
DATEADD(d, 39, dbo.fncDiaPascoa(@Year))2016-05-05 00:00:00.000
dbo.fncProcurarData(DATEADD(D, 0,DATEADD(M, 9, DATEADD(YY, @Year-YEAR(0), 0))), 1, 7, 2 )2016-10-03 00:00:00.000
dbo.fncCalcularData(DATEADD(D, 0,DATEADD(M, 7, DATEADD(YY, @Year-YEAR(0), 0))), 2, 4)2016-08-29 00:00:00.000
fncDiaPascoa() gives easter day for given year
fncProcurarData() gives day after some other day, say "monday after 1º suturday of october"
fncCalcularData() gives nº weekday of mont, say "4º monday of August"
This isn't return data, it's code.
What do you expect the process to return?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 24, 2016 at 8:36 am
But this is the return data, returned by my SProc similar to the one of the example
just the two columns, column with "code for the function" and columns with its result
as you can see in my sample, if you run
EXEC dbo.dummyProcedure 2016
it returns
ID FNC Calculated
---------- ---------------------------------------------------------------- -----------
1 dbo.dummyFuntion1(@YEAR) 2016
2 dbo.dummyFuntion2(@YEAR) 2017
3 dbo.dummyFuntion3(@YEAR) 2015
4 dbo.dummyFuntion4(@YEAR) 2014
In production the actual content of column Funcao is like "dbo.fncProcurarData(DATEADD(D, 0,DATEADD(M, 9, DATEADD(YY, @Year-YEAR(0), 0))), 1, 7, 2 )"
August 24, 2016 at 8:45 am
You are really better off working with a calendar table for these kinds of questions. It's better to have created and stored one on disk, but the following code will give you an idea of how simple a Calendar table can make your queries. The key is to precalculate all these columns you can select on, including DoWAsc which gives you the ordinal position of a given weekday in the month (1st Saturday). A lot of code follows, but keep in mind that if you've already created and indexed a Calendar table, you would only need the final query at the end.
--"Monday after 1st saturday of October"
WITH Dates (N, CalendarDate) as
(SELECT TOP(366) N, DATEADD(DAY,N-1, CONVERT(DATE,'1/1/2016')) FROM vTally) -- or your tally table of choice
,CalendarWork as (SELECT N as DateID, CalendarDate
,DATEPART(day,CalendarDate) as DayNo
,DATEPART(month,CalendarDate) as MonthNo
,DATEPART(quarter,CalendarDate) as QtrNo
,DATEPART(year,CalendarDate) as YearNo
,DATEPART(DW,CalendarDate) as [DayofWeek]
,CASE WHEN EOMONTH(CalendarDate) = CalendarDate
THEN 1
ELSE 0
END as EndOfMonth
FROM Dates d)
,Calendar as (SELECT *,CONVERT(bit, CASE WHEN ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek]
ORDER BY DateID DESC) = 1
THEN 1 ELSE 0 END) as LastDowInMonth
,CONVERT(tinyint, ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek]
ORDER BY DateID)) as DoWAsc
,CONVERT(tinyint,((DayNo-1)/7)+1) as WeekNoAlt
FROM CalendarWork)
-- get the calendar date for the first Saturday and add two days to it
select dateadd(day,2,CalendarDate) as Monday
from Calendar
where YearNo = 2016
and MonthNO = 10-- October
and [DayofWeek] = 7-- Saturday
and DoWAsc = 1-- First [DayOfWeek] of Month
You can also create columns to flag holidays. If different districts have different holidays, use the above code to help you create a holidays table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 9:53 am
Thanks
That was no quite the point, but it helped and give me a nice starting point to go
As i say, i have no performance problems, its a small table, i know my first aproach its uggly, and oi wanted to learn something more, and i do 🙂
if the data is about dates and so, this is a really nice aproach.
But imagine that you have any kinf of UDF in that column, dealing with all kinds of data
you have to add more and more columns to your table ...
but i wanted something more dynamic, something like a computed column with dynamic parameters
a query to a table that returns all columns plus one that is result of the statment in other column
i dont know if i can explain my idea well, i'm not a english native speaker
August 24, 2016 at 10:19 am
I know of two ways to approach what I *believe* you are describing.
One is dynamic SQL, using values from tables to build a string to be executed.
The other is the VALUES clause. The values clause acts somewhat like a table only it can contain expressions, not just static values. In the example below, dbo.Firstchar is a function that returns the first character of a string. Maybe something like this is what you're looking for. You'd just keep altering this function to add new calculations, instead of doing normal table maintenance.
CREATE FUNCTION CalcX (@X INT, @Y INT, @CalculationType int = null)
RETURNS TABLE AS
RETURN
select @X as [@X], @Y as [@Y], CalculationType, CalculatedResult
from (Values (1,@X+1), (2,@X+@X),(3,@X*@X), (4,@X*@Y), (5, (select FirstChar from dbo.Firstchar(@X)))
) v (CalculationType, CalculatedResult)
where calculationType = @calculationType or @calculationtype is null
GO
select * from CalcX(1234, 2, 4)
select * from CalcX(1234, 2, null)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2016 at 8:49 pm
paulo.margarido (8/24/2016)
i know my first aproach its uggly, and oi wanted to learn something more, and i do
Very cool. The first step is to stop saying things like...
i have no performance problems, its a small table
😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2016 at 10:25 pm
ChrisM@Work (8/24/2016)
paulo.margarido (8/24/2016)
i'm aware of what "inline table-valued functions" are, i have a few of my own in the DBwhat i ask is how can i do it that way
if you can, using that simple sample i posted, how would you turn that to a table-valued function ?
I can't, because I've no idea what output you expect from this process.
Yesterday was the Independence Day and some of our staff had a statutory holiday.
Did you function handle it correctly?
_____________
Code for TallyGenerator
August 25, 2016 at 1:52 am
Sergiy (8/24/2016)
ChrisM@Work (8/24/2016)
paulo.margarido (8/24/2016)
i'm aware of what "inline table-valued functions" are, i have a few of my own in the DBwhat i ask is how can i do it that way
if you can, using that simple sample i posted, how would you turn that to a table-valued function ?
I can't, because I've no idea what output you expect from this process.
Yesterday was the Independence Day and some of our staff had a statutory holiday.
Did you function handle it correctly?
Of course not. The function calculates US holidays because most users of ssc are in or from the US. I'm in the UK and I've written a similar function for use here. They could easily be merged.
I don't know where in the world you are Sergiy but wherever you are, I'm sure you can do one for your country.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply