May 7, 2014 at 2:51 pm
Hi,
I am trying to use sum function where a field falls into specific category, but so far no luck.
I have:
Wait_Status column: this can be "missing", "above target" and "within target"
Total: it is counted =Sum(Fields!Surgical_Case_Count.Value)
This Total gives me a number of cases per each wait_status.
What I need is to use the Sum function to get surgical_case_count where wait_status is "within target"...
Any ideas?
Thank you.
May 7, 2014 at 3:06 pm
You have just written what you need:
masarzdenka (5/7/2014)
Sum ... surgical_case_count where wait_status is "within target"...
select Sum(surgical_case_count)
where wait_status = 'within target'
May 7, 2014 at 3:53 pm
I am building the report using SQL report builder and "where" is not an option I thought. It doesn't work when I use: =Sum(Fields!Surgical_Case_Count.Value) where (Fields!Wait_Status.Value="Within Target")
May 7, 2014 at 3:58 pm
I think it needs to be done through lookup, so it looks where wait_status="within target" and takes a value from "surgical_case_count" (which is another column) and sums it up.
I just don't know how to write the expression...
May 8, 2014 at 6:15 am
Try using a case statement:
select
Total_within_target = sum(case when Wait_Status.Value="Within Target"
then Fields!Surgical_Case_Count.Value
else 0
end),
Total_missing = sum(case when Wait_Status.Value="Missing"
then Fields!Surgical_Case_Count.Value
else 0
end),
Total_above_target = sum(case when Wait_Status.Value="above_target"
then Fields!Surgical_Case_Count.Value
else 0
end)
from ...
May 8, 2014 at 7:13 am
Try
=Sum(IIf(Fields!Wait_Status.Value="Within Target",Fields!Surgical_Case_Count.Value,0))
Far away is close at hand in the images of elsewhere.
Anon.
May 8, 2014 at 9:31 am
This one gives me an error:
=Sum(IIf(Fields!Wait_Status.Value="Within Target",Fields!Surgical_Case_Count.Value,0))
I think I've tried it before.
May 8, 2014 at 9:33 am
The IIf function is new to SQL Server 2012.
May 8, 2014 at 9:36 am
David, I am using 2008
May 8, 2014 at 9:44 am
Then the case code I gave you should work. I've used similar code many times in 2008 R2. You may have to tweak on the syntax a little.
May 8, 2014 at 9:52 am
masarzdenka (5/8/2014)
This one gives me an error:=Sum(IIf(Fields!Wait_Status.Value="Within Target",Fields!Surgical_Case_Count.Value,0))
I think I've tried it before.
That is an SSRS expression not T-SQL
What error are you getting?
If it is in SSRS then the only error I know that Sum and IIf gives is datatype incompatibility and to fix that convert both the field value and the zero to integer (CInt), decimal (CDec) or double (CDbl)
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply