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

Ira Warren Whiteside

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?

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 '