Data Profilng Column Functional Dependency T-SQL

• Ira Warren Whiteside

  SSCrazy

  Points: 2146

  More actions

  February 12, 2009 at 12:21 am

  #199919

  Comments posted to this topic are about the item Data Profilng Column Functional Dependency T-SQL

  [font="Comic Sans MS"]Ira Warren Whiteside[/font]

• tomasz.cwajda

  SSC Journeyman

  Points: 79

  More actions

  February 14, 2009 at 4:51 am

  #944062

  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?

• tomasz.cwajda

  SSC Journeyman

  Points: 79

  More actions

  February 14, 2009 at 5:08 am

  #944064

  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 '