February 10, 2011 at 1:27 pm
Forgive me for being new at SQL and for having one of those foggy-headed, 'i know my brain works better than this' days. :blink:
I have a variable that is a Sum of some other variables. However we shouldn't have that sum if too many values are null or less than 0.
So, I need to count the number of these particular variables in the current record that have values that are => 0, so I can then determine what the Sum variables should be.
So I want to do something like this
declare @NumVars int
if Satis>= 0 then @NumVars=@NumVars+1
if DropAct>= 0 then @NumVars=@NumVars+1
if Empty>= 0 then @NumVars=@NumVars+1
case NumVars > 1 then Sum = [the sum of those columns]
else Sum = -2
I realize none of what I have written up there is right. I'm just trying to give you an idea of what I am going for.
I'm not at all sure of the best way to do this. I was looking at a computed column but it's too complicated. So then I was looking at a user defined function that could then be called by the computed column...
While I feel that in the end I could make it work somehow, I'd really like some input on the proper way to do this.
February 10, 2011 at 1:59 pm
the trick here is to the the SUM of a CASE which tests the columns you are after;
take a look at my example below;
based on your post, something like this will be a good model for you:
SELECT
SUM(CASE WHEN Satis >= 0 THEN 1 ELSE 0 END) AS TOTALSATISFIED,
SUM(CASE WHEN DropAct >= 0 THEN 1 ELSE 0 END) AS TOTALDROPACT,
SUM(CASE WHEN [EMPTY] >= 0 THEN 1 ELSE 0 END) AS TOTALEMPTY
FROM MYTABLE
Lowell
February 10, 2011 at 2:04 pm
sr,
While the trick above can perform some tasks, I don't think that's what you're looking for. These variables (I assume they're columns?) are per record, right?
So what you want to do is sum up when out of 10 columns on a row, and 5 are null (5 being the threshold in this example) then don't include the line in the aggregation, but if only 4 are null, include it?
I think we're going to have to see ddl and sample data here if that's the case to help you out of this. It's not going to be pretty.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2011 at 3:52 pm
Thank you both for your help.
So, this is what I ended up doing.
Created this function:
FUNCTION [dbo].[fnGDSTotal](@Satis int, @DropAct int, @EmpLife int)
RETURNS int
as
begin
declare @NumVars int
set @NumVars = 0
if @Satis>=0 set @NumVars =@NumVars + 1
if @DropAct>=0 set @NumVars =@NumVars + 1
if @EmpLife>=0 set @NumVars =@NumVars + 1
declare @GDSTotal int
if @NumVars = 3 set @GDSTotal = @Satis + @DropAct + @EmpLife
if @NumVars < 3 set @GDSTotal = -2
RETURN @GDSTotal
end
Then the computed column has this formula:
([dbo].[fnGDSTotal]([Satis],[DropAct],[EmpLife]))
Works fine. There are more than three variables that I want to add, I just started with three to figure out if I could get it working. The only thing I'm not thrilled about is the fact that the computed column can't be persisted.
Of course, I don't understand enough about the pros and cons of persisted vs non and it feels like even though I search for the information, I'm not getting a clear explanation. So if you have any information to share or know of a good explanation somewhere, let me know. 🙂
February 11, 2011 at 3:04 am
The result of persisted is stored with the table, as though it was a column, and is recomputed anytime the record is updated. Non-persisted is re-computed everytime the column is viewed.
This is one of those things you'll want to persist. You usually use non-persisted when you have variable items like GETDATE() as part of the equation.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 11, 2011 at 8:17 am
Thanks for replying again Craig.
I do know what persisted and non-persisted mean.
I don't understand what the advantage to one over the other is.
Why do you say I'd want to persist this particular calculated column? And if I can't (which I can't) is that a serious problem?
February 11, 2011 at 1:09 pm
srferson-957148 (2/11/2011)
Thanks for replying again Craig.I do know what persisted and non-persisted mean.
I don't understand what the advantage to one over the other is.
Why do you say I'd want to persist this particular calculated column? And if I can't (which I can't) is that a serious problem?
OOOOOoooohhh, sorry. Persisted takes less time to access because it's already calculated, and can be indexed. On a per row basis, not usually a big deal. If you're going to use it in your where clause, however, those extra milliseconds per row can add up quick.
So, persisted is better overall for speed performance. Now, if you can't persist, you can't persist. It won't kill you but it will take longer if you use it as part of your query restrictions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 11, 2011 at 2:57 pm
Nothing to be sorry for. 🙂
So persisted is better because it doesn't have to be recalculated every time the record is accessed, which saves time/processing power. That makes sense. I guess I thought there was more to it, but from what I've read, that really does seem to be the major difference.
Thanks Craig!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply