Author: way0utwest

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was reading Iris Classon’s blog recently and ran across a post on her day at work. I think it’s a fantastic post that does help younger people understand what a job is like. I’m looking forward to seeing more.

One quick thing struck me in the post, which was that she has clients that are missing key value settings in a table. I’ve dealt with this, and want to write more, but a quick post on just finding out that there are missing settings.

Finding the Data Inconsistencies

Each client should have a set of key value pairs in a settings table. However, because of application problems, not every client does. In the sample data (below), there are 8 clients, each of which should have 5 values in the GlobalSettings table. However, there are only 20 rows in this table when there should be 40.

This is the type of issue I’ve had occur in an application, especially one that evolves over time. We add a key-value item to the application, which new clients get, but older ones are never populated. This is the same type of issue I saw in a post by Iris Classon.

How can I find the items that don’t match? One simple way is to count the values, but include a HAVING clause to limit the results. If I want to see who has all the values, I can do this:

SELECT gs.ClientID, COUNT(*)

FROM dbo.GlobalSetting AS gs 

GROUP BY gs.ClientID
  HAVING COUNT(*) = 5

In my sample data, this returns one row, for Client 1. If I change the HAVING clause to < 5, I get the other seven rows.

There are other considerations here, and this isn’t the best way that you might ensure you have the values. I might have a table, or a derived table that ensures I’m checking the right 5 values.

I’ve written more about this in an article at SQLServerCentral.

The Setup

I built a couple quick tables and added data with this script. Note that there are 8 clients, and that each has a series of settings in a table. There are 5 possible settings (Position, Height, Weight, Number, College)

CREATE TABLE Client
(ClientKey INT IDENTITY (1,1) NOT NULL CONSTRAINT ClientPK PRIMARY KEY 
, ClientName VARCHAR(200)
, ClientStatus TINYINT)
go
CREATE TABLE GlobalSetting
( GlobalSettingKey INT IDENTITY (1,1) NOT NULL CONSTRAINT GlobalSettingPK PRIMARY KEY 
, ClientID INT NOT NULL CONSTRAINT GlobalSettingFK_Client_ClientID FOREIGN KEY REFERENCES Client
, GlobalSettingName VARCHAR(100)
, GlobalSettingValue VARCHAR(500)
)
GO
INSERT client VALUES ('Shaquil', 1), ('Von', 1), ('Bradley', 1), ('Shane', 2), ('Todd', 2), ('Jerrol', 3), ('Jeff', 3), ('Josey', 3)

 Position, College, Height, weight, number
INSERT dbo.GlobalSetting
(
    ClientID ,
    GlobalSettingName ,
    GlobalSettingValue
)
VALUES
  (1, 'Position', 'OLB')
, (1, 'Weight', '250'),
(1, 'College', 'CSU')
, (1, 'Height', '74')
, (1, 'Number', '48')
, (2, 'Weight', '250')
, (2, 'Number', '58')
, (2, 'College', 'Texas A&M')
, (3, 'Height', '76')
, (3, 'Weight', '269')
, (4, 'Position', 'OLB')
, (4, 'College', 'Missouri')
, (4, 'Height', '75')
, (5, 'Weight', '230')
, (5, 'Number', '51')
, (5, 'College', 'Sacramento St')
, (6, 'Weight', '235')
, (6, 'Position', 'LB')
, (7, 'Weight', '249')
, (8, 'Number', '47')

SQLNewBlogger

This only took about 10 minutes to write, though I had to build the tables and data. Of these, the data took the longest, because I had to look up the values .

This is a basic example of checking data in a business situation. I might write about this in my job, perhaps showing a daily integrity check or a custom metric that I use to ensure the system is working. You can do the same thing and ensure that your data is correct.