February 12, 2009 at 12:21 am
Comments posted to this topic are about the item Data Profilng Column Functional Dependency T-SQL
[font="Comic Sans MS"]Ira Warren Whiteside[/font]
February 14, 2009 at 4:51 am
I really don't know what is showig this pie chart !
This is some creasy idea to use pie chart to this data, is't it?
February 14, 2009 at 5:08 am
I don't know why you make it in so complikated way.
Below you can find my version of your procedure.
CREATE PROCEDURE [dbo].[ColumnDependency2]
@TableName VARCHAR(128) ,
@Determinant VARCHAR(128) ,
@Dependent VARCHAR(128)
AS
Declare @sql VarChar(MAX);
SELECT @sql = 'WITH all_ AS (
SELECT '+@Determinant+'
,'+@Dependent+'
,count(*) cnt
FROM '+@TableName+'
GROUP BY '+@Determinant+'
,'+@Dependent+'
)
, fraction_ AS (
SELECT '+@Determinant+'
,max(cnt) support_count
,sum(cnt) frac_cnt
FROM all_
GROUP BY '+@Determinant+'
)
SELECT v.'+@Determinant+'
,'+@Dependent+'
,cnt [Dependeny Counts]
,case when cnt < support_count then ''Violation Percentage''
ELSE ''Support Percentage''
end [Dependency type]
,CAST(CAST(cnt AS DECIMAL ) / frac_cnt * 100 AS DECIMAL (5, 2)) AS PercentViolation
FROM all_ v
,fraction_
WHERE fraction_.'+@Determinant+' = v.'+@Determinant+'
ORDER BY 1, 3 DESC ';
Print @sql
Exec (@SQL)
and execution:
Exec ColumnDependency2
@TableName = 'Addresses',
@Dependent = 'StateProvinceName',
@Determinant = 'CountryRegionCode '
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy