|
Is there an easy way to restrict values - returned by a SQL select - to certain boundaries?
I've got a some tables like this
dataTBL:
dt id x
2008-01-01 10:00 1 5.13
2008-01-01 10:00 2 14.00
2008-01-01 10:00 3 -2.10
2008-01-01 10:00 4 1.65
2008-01-01 11:00 1 5.19
...
compTBL (balancing computation rules):
resultID baseID k d minX maxX
100 1 +1.0 0 null null
100 2 -0.5 0 +3 null
100 3 -0.5 +5 -10 0
200 1 +1.0 0 null null
200 4 -1.0 10 0 100
...
And I want to calculate for ID 100 e.g. the values
5.13 * 1.0 + 0.0 (no limits)
+ 14.00 *-0.5 + 0.0 (but at least 3)
+ -2.10 *-0.5 + 5.0 (but at least -10 and maximal 0)
I'm working with Sql Server 2005 and at the moment I'm doing it this way
select
dt,
resultID,
sum(
case
when isnull(x,0) * k + d < minX then minX
when isnull(x,0) * k + d > maxX then maxX
else isnull(x,0) * k + d
end
)
from dataTBL join compTBL on dataID = baseID
group by dt, resultID
This works fine, but I don't like having the same expression three times just for testing <, > and else.
Is there no shorter way to write this case-expression? Or ist there some other function I could use? Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?
Thanks
Andy
|
|
|
|
|
ScruffR wrote: Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?
How about creating an scalar-valued function ClipToBoundaries ?
Regards,
Syed Mehroz Alam
|
|
|
|
|
Of course! I'm always thinking complicated
I thought of a C# function, but didn't like this idea, because our admins would have to activate .Net support on their precious server
SV-functions are a possible option, but how about performance? Is there a standard answer to that question SVF vs. CASE?
Thanks
Andy
|
|
|
|
|
ScruffR wrote: but how about performance?
Should be about the same, but it reduces maintenance and improves readability, both of which are worth a performance hit.
I use a lot of user defined functions, most of them I have in a database that contains only a library of them (and no data), that way I don't have to copy them to each database I use.
|
|
|
|
|
If you don't have the function you need in T-SQL (as in this case), create your own using either T-SQL or if you need heavier calculation yu can also build the function on CLR using C# and register it to SQL Server.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi all,
I have an Query that contains a column name a_b and also a column b_c.
column b_c have 2 values for every value of a_b means values of a_b is 2 then for this 2 b_c has two values i.e 7 and 9.
Can somebody help me out....
|
|
|
|
|
anoopazgar wrote: Can somebody help me out....
Probably, if we understood your problem. What is/isn't happening, what sql have you got so far?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: if we understood your problem
Yup. A big IF. Couldn't understand it at all.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Please post DDL for the tables in question, sample data, and expected results. It's a whole lot simpler to help you that way.
|
|
|
|
|
Can you post your query?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
Service broker and sql notification are normally associated with database events. If you are wanting to know about file system changes you need a filesystemwatcher[^]
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ok . i need this.
for example in my program :
i need that if user add or remove a book from database . changes display online.
<< never believe a lie >>
|
|
|
|
|
In which case, yes server broker & sql notification will do the job.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
how i can do this?
<< never believe a lie >>
|
|
|
|
|
Have a look at this CP article[^]
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Now I'm confused - and not a little annoyed. I point you at an article that gives loads of info about using notification services and you reply with a load of confused.
You could also try google, or even buy a book - there are several gopod ones on the market
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Have you studied SQL Server development at all?
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
I had an empty SQL database created on one of our internal machines. The admin gave me dbowner rights. At this point I need to know how to go about creating tables, queries, etc in that database. The last time I used MSSQL (8 years ago), I had physical access to the machine and could mess with it all I wanted. That's no longer the case. Is there something that I can install on my desktop to management it (so that I do not have to get the admin involved for every little change I need)?
Thanks.
DC
"Love people and use things, not love things and use people." - Unknown
"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch
|
|
|
|
|
I'm in the same boat you're in...but I have a few words for you as a gift from one SQL Server newbie to another: "Microsoft SQL Server Management Studio Express"
|
|
|
|
|
Yes, I had previously seen this tool and it looked promising. My only reservation was that it was for MSSQL 2005 Express. Currently I do not know what version of MSSQL we have.
Thanks for the confirmation.
"Love people and use things, not love things and use people." - Unknown
"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch
|
|
|
|
|
David is right, Management Studio is the tool you are looking for. I have a personal question for you. MVP, 5 years membership, 21 articles and you are a SQL newbie. Just what have you been doing, oh wait read the articles -
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Just what have you been doing...
Not that it's any of your concern, but what I've been doing does not involve databases of any sort.
"Love people and use things, not love things and use people." - Unknown
"The brick walls are there for a reason...to stop the people who don't want it badly enough." - Randy Pausch
|
|
|
|
|
You're right it is none of my business but I'm so steeped in LOB applications that are all database oriented I was curious is all.
Never underestimate the power of human stupidity
RAH
|
|
|
|