June 12, 2012 at 9:33 am
Hi,
My report graph has trouble filtering some averages because of the varied format of data. It is actually a very long complicated procedure, and here is a small snippet.
No , I can not try IN clause. The filter statement where the Function is used below is where i need help. rightnow I am trying using char(255) and filter my report side graph parameter as (JOIn (parameter!name.value, char(255)) but the graph fails. If I try
(JOIn (parameter!name.value, ",")) then names do not get filtered right. Any insight would be appreciated
Thank you.
Declare @Name varchar(max)
set @Name='O''brian,O''Neill, , a,b.'
create table #name
(
name varchar(max), average decimal(10,2)
)
INSERT INTO #name (Name, average)
SELECT 'O''brian', 1.0 --dates are in mmddyy
UNION ALL
SELECT 'O''Neill',2.0
UNION ALL
SELECT Null ,3.0
UNION ALL
SELECT 'Joe brown',4.0
UNION ALL
SELECT 'Jane-Smith',5.0
union all
select 'a,b.',6.0
update #name
set name= (select case when name is null then SPACE(1)else name end)
set @Name = REPLACE(@Name,'''''','''' )
select average from #Name
where Name IN (SELECT result FROM dbo.fnSplitString(@name, CHAR(255))) or @Name is null -----???
--where Name IN (SELECT result FROM dbo.SplitString(@name, ',')) -----------------???
drop table #name
----------------------------------------------------------------------------------------------------
--Function
ALTER FUNCTION [dbo].[fnSplitString](@Text varchar(max),@Delimiter varchar(2)= ' ')
RETURNS @Strings TABLE
(
Position int IDENTITY PRIMARY KEY,
result varchar(max)
)
AS
BEGIN
DECLARE @Index int
SET @Index = -1
WHILE (LEN(@Text) > 0)
BEGIN
SET @Index = CHARINDEX(@Delimiter,@Text)
IF (@Index = 0) And (LEN(@Text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@Text)
BREAK
END
IF (@Index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@Text,@Index - 1))
SET @Text = RIGHT(@Text, (LEN(@Text) - @Index))
END
ELSE
SET @Text = RIGHT(@Text, (LEN(@Text) - @Index))
END
RETURN
END
June 12, 2012 at 10:00 am
First thing I would suggest is to replace your split function with the one in the article I reference in my signature. Then to get your data you will want to use APPLY. Paul White has a couple of great articles on APPLY.
http://www.sqlservercentral.com/articles/APPLY/69953/%5B/url%5D
http://www.sqlservercentral.com/articles/APPLY/69953/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 6:46 am
Thank you Sean. Very simple and informative article.
June 14, 2012 at 7:21 am
You are welcome. Did you also read the string splitter article? That splitter will blow the doors off just about anything for performance. It should be in everyone's arsenal of sql tricks. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply