Get fill rates in another table - SQL Server
I am trying to create a script to calculate the fill rates for each column in a table
Data_table and insert it into a second table
Data_table has 30 columns in it, and some columns have 100% data in them, and some have less than 100% (due to nulls).
My code to calculate the fill rate looks like this,
select cast(sum(case when employee_id is null then 0 else 1 end) / cast(count(1) as float ) * 100 as decimal(8,3)) as employee_id_fill, .....--/*so on for 30 columns..*/ from [Data_table]
Metadata_table should look like this:
Table_name | Colmn_name | Fill_rate [Data_table]| Colomn_a | 100% [Data_table]| Colomn_b | 89% [Data_table]| Colomn_c | 100% and so on...
can work here, but i am unable to get the column names into the [Metadata_table] automatically.
I tried using this for automating the column names-
but this has not worked so far.
Any help is appreciated
You can use
sys.columns for grabbing the column names. You can join it to
sys.tables by the
object_id if you ever need to associate the two.
SELECT c.NAME FROM SYS.TABLES t INNER JOIN SYS.COLUMNS c ON t.OBJECT_ID = c.OBJECT_ID WHERE t.OBJECT_ID = OBJECT_ID('DBO.[Data_Table]');
You can generate SQL from here in the format you wanted by creating an expression to query your table and then unpivot it.
Another approach could be a while loop to do inserts into your metadata table. If you're working with a very large table this option will be more expensive so keep it in mind. I used an example table
dbo.Attendance_Records and this script will print out the example SQL, not execute it. You would want to change it to call sp_executesql on that text.
DECLARE @Table NVARCHAR(128) = 'DBO.[Attendance_Records]' ,@MetaTable NVARCHAR(128) = 'DBO.[Metadata_Table]' ,@ColumnName NVARCHAR(128) ,@Iterator INT = 1 ,@SQL NVARCHAR(MAX) SELECT c.NAME ,c.COLUMN_ID ,ROW_NUMBER() OVER (ORDER BY COLUMN_ID) AS RN INTO #Cols FROM SYS.COLUMNS c WHERE c.OBJECT_ID = OBJECT_ID(@Table); WHILE @Iterator <= (SELECT ISNULL(MAX(RN),0) FROM #Cols) BEGIN SET @ColumnName = (SELECT NAME FROM #Cols WHERE RN = @Iterator) SET @SQL = 'INSERT INTO ' + @MetaTable + ' (Table_Name, Column_Name, Fill_Rate) ' + 'SELECT ''' + REPLACE(@Table,'DBO.','') + ''', ''' + @ColumnName + ''', 100 * CONVERT(DECIMAL(8,3), SUM(CASE WHEN [' + @ColumnName + '] IS NULL THEN 0 ELSE 1 END)) / COUNT(1) AS [' + @ColumnName + '_fill]' + ' FROM ' + @Table PRINT @SQL SET @Iterator += 1 END
How To Find Fill Rate in a Table? – Madhivanan's sql blog, In the above blog, Temporary table is used to find out the result. Here I show another method without using temporary table. Consider the same� ARULMOUZHI, My colleague and Blogger, has written a blog post on To Find Fill Rate in a Table on both AZURE and On-Premise – Key Metric in Data Analysis. It is very good blog post on this subject and read it to know more about what this topic is about. In the above blog, Temporary table is used to find out the result.
Since you need to have the column names you would need to so something along these lines.
select ColumnName = 'Colomn_a' , FillRate = count(distinct Colomn_a) / count(*) * 1.0 --must multiply by 1.0 to avoid integer math from YourTable UNION ALL select 'Colomn_b' , count(distinct Colomn_b) / count(*) * 1.0 from YourTable
[MS SQL] Creating Line Fill Rate % query : SQL, Basically, for every day, I want it to calculate the Line Fill Rate % which is just previous day, but I can't seem to get a GETDATE()-1 or something to work. Update join syntax you dinosaur :) Also, you are aliasing a table with its own The need to maintain a different data storage stack when existing Sql.can do the job? Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse This topic describes how to copy columns from one table to another, copying either just the column definition, or the definition and data in SQL Server 2019 (15.x) by using SQL Server Management Studio or
Just an alternate method of Mike R's
CREATE OR ALTER PROCEDURE [dbo].[GetFillRate_new] -- EXEC [GetFillRate] 'TestEmp' ( @TableName NVARCHAR(128), @Include_BlankAsNotFilled BIT = 1 -- 0-OFF(Default); 1-ON(Blank As Not Filled Data) ) AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; IF NOT EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE [TYPE]='U' AND [NAME]=@TableName ) BEGIN SELECT Result = 1 , Reason ='Table not exists in this Database' ; RETURN 1; END; declare @sql varchar(max) set @sql='' select @sql=@sql+'select '''+c.column_name+''' as [Column Name], cast((100*(sum( case when ' + case when @include_blankasnotfilled = 0 then '[' + c.column_name + '] is not null' when c.collation_name is null then '[' + c.column_name + '] is not null' else 'isnull([' + c.column_name + '],'''')<>'''' ' end + ' then 1 else 0 end)*1.0 / count(*))) as decimal(5,2)) as [Fill Rate (%)] from '+c.table_name+' union all ' from information_schema.columns as c inner join information_schema.tables as t on c.table_name=t.table_name where t.table_type='base table' and t.table_name =@tablename set @sql=left(@sql,len(@sql)-10) --print @sql exec(@sql) end
You can find more details into this blog post https://exploresql.com/2019/12/14/how-to-find-fill-rate-in-a-table/
Check fillrate in a table - Transact-SQL, I am trying to calculate not filled rate in value column for eg: Total NULL (In value column)*100 DECLARE @PHARMA TABLE ( MRN VARCHAR (30), 0) AS decimal(5, 2)) AS neuro_fill_rate FROM ( SELECT SUM(CASE� I want to take the daily backup of a single table from LIVE MS SQL server to local ( keeping in mind both are on seperate network with no direct connection in between as of now ). The table will have approx 40 thousand new entry each day.
SQL INSERT INTO SELECT Statement, INSERT INTO SELECT Syntax. Copy all columns from one table to another table: INSERT INTO table2. SELECT * FROM� Saving the Results Set from a SELECT Statement within a SQL Server Stored Procedure to a Regular Table. By saving a select statement’s results set to a regular table from inside a stored procedure, the process for persisting a results set can be even simpler than with the preceding example.
Table fillrate and other info! – SQLServerCentral, Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci) Often the data I get is brocken and requires mass cleaning / juggling the client the difference of the structure of the table % filled before and after cleansing. Update table using values from another table in SQL Server. Ask Question Asked 4 years, 2 months ago. Active 1 year, 5 months ago. Viewed 164k times 15. 8. I have 2
To Find Fill Rate in a Table on both AZURE and On-Premise – Key , Recent Posts. Hyperlink a value in SQL Server March 22, 2020; Methods to Show Rupee Symbol in SQL Server March 18, 2020� Temporal tables have been a feature of SQL Server since version 2016. SQL Server professionals have historically had several options to track data change events. The evolution of the data tracking mechanism started with Change tracking (CT), Change Data Capture (CDC) and now Temporal Tables.
- You would need a dynamic pivot, to do this for all columns in the table, where you don't know the names of the columns in advance.
- Thank you. This worked, i was able to automate using this script to check after every load process in the ETL.
- Glad to hear it suited your needs!
- Thank you for the suggestion.I actually did look into this approach. But I have close to 400 columns in various tables, so i was hoping for a more automated way to do this.
- You can leverage dynamic sql but I would be gravely concerned if you have tables with over 400 columns. That is a pretty clear indication that the design is in some need of normalization.
- I would agree.. what i meant was, there were a total of 400 columns distributed over 15 tables. There are a average of 25 columns per table.
- So you need to spend a couple hours to write queries for those 15 tables. That isn't a big deal at all.
- Yes, its done. I will write a while loop to pick up table names from system objects and then this loop will pick up column names for each table name.