August 25, 2016 at 4:01 am
I mark The Dixie Flatline answer as solution, because is a good idea
to implement it i need to add a as many columns to my holidays table as distinct parameters that i need in all functions i'll be using (about 5 new columns)
But was not really the point, the discussion is not about calculating dates, holidays or any date manipulation
The discussion i want is about having a column where you have a statment, or an UDF, in a form of a VARCHAR, and with an SProc have that column calculated and returned in a new column.
Is there a way to do it, without using dynamic sql, and with no columns added to the table?
the perfect solution will be something like:
SELECT Column1
, Column2
, ...
, FunctionColumn
, EXEC(FunctionColumn) AS FunctionResult
FROM MyTable
August 25, 2016 at 5:40 am
ChrisM@Work (8/25/2016)
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.
That function is not good even for US:
http://www.timeanddate.com/holidays/us/martin-luther-king-day
http://www.cityclerk.nyc.gov/html/about/holidays.shtml
As about me - I'm everywhere, our offices are in more than 20 counries around the world.
Every country has its own set of holidays.
And there are local public holidays.
Many of big cities have their own "Day of the City" - public holiday for city residents only.
I'm sure such a practice exists in US and UK as well.
Sometimes public holidays get shifted, or week-end days-off around them get shifted not to interrupt a sequence of holidays with a single working day in between them.
Governments of different levels introduce new holidays or remove them.
How many functions do you want to create to deal with all of these cases?
And how often you plan to deploy them to every production server where they are used?
How do you deal with medical software, where the environment is closed and code changes must go through comprehensive testing and get approved?
How much resources (read - money) will your function consume?
Compare it with a single function reading the data from a single Calendar table having holiday records specific for different locations, and simple user interface which allows users update the recods in the table themselves, when local government decides to change the schedule of public holidays.
No risk of bugs, no failing deployments, no programmers involved.
List of public holidays is not a routine. It's knowledge base.
Which means - data.
Hardcoding data - it's a very bad approach.
It's bad every time and every where.
_____________
Code for TallyGenerator
August 25, 2016 at 5:57 am
Sergiy (8/25/2016)
ChrisM@Work (8/25/2016)
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.
That function is not good even for US:
http://www.timeanddate.com/holidays/us/martin-luther-king-day
http://www.cityclerk.nyc.gov/html/about/holidays.shtml
...
Compare it with a single function reading the data from a single Calendar table having holiday records specific for different locations, and simple user interface which allows users update the recods in the table themselves, when local government decides to change the schedule of public holidays.
No risk of bugs, no failing deployments, no programmers involved.
...
This is exactly what I'd advocate in almost every case. It's a heck of a lot more sensible than "something more dynamic, something like a computed column with dynamic parameters". When a calendar table isn't available and isn't likely to be made available, then a locally-scoped iTVF will often suffice.
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 25, 2016 at 9:25 am
Compare it with a single function reading the data from a single Calendar table having holiday records specific for different locations, and simple user interface which allows users update the records in the table themselves, when local government decides to change the schedule of public holidays.
No risk of bugs, no failing deployments, no programmers involved.
Agreed! The shipping industry have books of all the worlds holidays. They are available in machine-readable form by country and political unit. The problem is if you bring a cargo ship into a port, and nobody's working that day you have to pay for parking your super ship. It is much cheaper to keep it at sea. You have to plan your schedules so the cargo arrives when there are actually dockworkers.
I used to post a list of the various rules classifying holidays; there is about 25 of them. Then, given all of that, as you said, a local decree adds a holiday. Frankly, trying to compute calendrical functions is a waste.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 25, 2016 at 9:45 am
can you go back to the point of the discussion as i ask?
this is not about hollidays, it was a example, the focus is what i post before
paulo.margarido (8/25/2016)
The discussion i want is about having a column where you have a statment, or an UDF, in a form of a VARCHAR, and with an SProc have that column calculated and returned in a new column.Is there a way to do it, without using dynamic sql, and with no columns added to the table?
the perfect solution will be something like:
SELECT Column1
, Column2
, ...
, FunctionColumn
, EXEC(FunctionColumn) AS FunctionResult
FROM MyTable
August 25, 2016 at 10:16 am
The discussion I want is about having a column where you have a statement, or an UDF, in a form of a VARCHAR, and with an SProc have that column calculated and returned in a new column.
Please look at my credentials. I can tell you with certainty that we (ANSI standards committee) never intended SQL to be used this way. SQL is a database language, whose purposes are to maintain data integrity, standardize access to the data for other languages, and allow queries to the data. It was never meant for computations or symbolic manipulationd
I would guess that the best languages for constructing and executing functions on the fly would be LISP, Mathematica etc. .
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 25, 2016 at 10:24 am
ok, now can we go back to the discussion?
August 25, 2016 at 11:43 am
EXEC(FunctionColumn) AS FunctionResult
Sorry, but functions can't call procedures. They can only call other functions. Period.
If I understand you correctly now, you want to be able to pass a string that is itself a calculation, and have the EXEC run that string to produce a results calculation. You aren't going to get there without doing dynamic SQL and performance is probably going to suffer some.
Could you explain why you feel the need to do this? What is so dynamic about the calculation strings you are dealing with?
__________________________________________________
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 25, 2016 at 1:22 pm
paulo.margarido (8/25/2016)
ok, now can we go back to the discussion?
You're confusing the purpose and capabilities of SQL Server with those of Excel.
Use Excel for this.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 25, 2016 at 8:31 pm
The Dixie Flatline (8/25/2016)
Sorry, but functions can't call procedures. They can only call other functions. Period.
Any bets on that? 😉
--Jeff Moden
August 26, 2016 at 2:09 am
paulo.margarido (8/25/2016)
ok, now can we go back to the discussion?
Here is what solves your problem.
SELECT h.Date
FROM dbo.Holidays h
WHERE Territory = @Territory
AND Level = @Level
AND Date >= DATEADD(YY, DATEDIFF(YY, 0, @ReuestedDate-2), 0)
AND Date < DATEADD(YY, DATEDIFF(YY, 0, @ReuestedDate+2), 0)
If you after some functions, not the actual solution - feel free to create as many functions as you wish.
_____________
Code for TallyGenerator
August 26, 2016 at 3:41 am
i know that, was an try to explain of what i want
The Dixie Flatline (8/25/2016)
EXEC(FunctionColumn) AS FunctionResultSorry, but functions can't call procedures. They can only call other functions. Period.
If I understand you correctly now, you want to be able to pass a string that is itself a calculation, and have the EXEC run that string to produce a results calculation. You aren't going to get there without doing dynamic SQL and performance is probably going to suffer some.
Could you explain why you feel the need to do this? What is so dynamic about the calculation strings you are dealing with?
i know you cant do EXEC(FunctionColumn) AS FunctionResult in a select statment
i even try something like this, even knowing that it may not work
create function dbo.calc_statment_in_column (@statment varchar(100), @param1 varchar(10))
returns varchar(100)
as
begin
declare @result varchar(100)
declare @query varchar(400)
set @query = N'SELECT @res = ' + @statment
EXEC sp_executesql @query
, N'@param varchar(10), @res varchar(100) OUTPUT'
, @param = @param1, @res = @result OUTPUT
return @result
end
--and use it like this, where the functionColumn has statments like "dbo.dummyfunction(@param)"
select column1
, functionColumn
, dbo.calc_statment_in_column( functionColumn, @param1 ) as functionResult
from mytable
and it didn't
but is this kind of aproach that i want to know if is possible
of corse excell does it better 🙂
August 26, 2016 at 8:19 am
Jeff Moden (8/25/2016)
The Dixie Flatline (8/25/2016)
Sorry, but functions can't call procedures. They can only call other functions. Period.Any bets on that? 😉
I'd probably lose the bet. Care to show us an example, Jeff?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply