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