Ars Electronica 2013 – Quotidian Record

/*
Proof of concept for pivoting table with cross apply.
Autjor: Tim Bartel 2015.09.23

*/

-- Let's create simple table of employee sales records
create table sales (
recordID int IDENTITY(1,1) NOT NULL,
storeID int,
employeeID int,
promo1 decimal(16,2),
promo2 decimal(16,2),
promo3 decimal(16,2),
promo4 decimal(16,2),
promo5 decimal(16,2),
aprove1 varchar(3),
aprove2 varchar(3),
aprove3 varchar(3),
aprove4 varchar(3),
aprove5 varchar(3),
date1 date,
date2 date,
date3 date,
date4 date,
date5 date
)

Now let’s add some data:

insert into sales (storeID, employeeID, promo1, promo2, promo3, promo4, promo5, aprove1, aprove2, aprove3, aprove4, aprove5, date1, date2, date3, date4, date5)
values ('251', '12', '350.00', '275.00', '110.00', '50.50', '1111.11', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-07-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('251', '12', '750.00', '550.00', '330.00', '151.00', '1111.11', 'RN', 'RJ', 'AA', 'RN', 'TD', cast('2015-01-28' as date), cast('2015-03-15' as date), cast('2015-06-29' as date), cast('2015-08-03' as date), cast('2015-09-22' as date)),
('251', '12', '350.00', '275.00', '110.00', '50.50', '1000.00', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-07-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('251', '12', '350.00', '275.00', '110.00', '850.50', '11.11', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-06-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('190', '12', '350.00', '275.00', '110.00', '50.50', '100.00', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-05-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('250', '10', '350.00', '275.00', '110.00', '50.50', '1111.11', 'SA', 'RJ', 'SA', 'SA', 'RJ', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-07-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('250', '10', '750.00', '550.00', '330.00', '151.00', '1111.11', 'RN', 'RJ', 'AA', 'RN', 'RJ', cast('2015-01-28' as date), cast('2015-03-15' as date), cast('2015-06-29' as date), cast('2015-08-03' as date), cast('2015-09-22' as date)),
('250', '10', '350.00', '275.00', '110.00', '50.50', '1000.00', 'SA', 'RJ', 'SA', 'SA', 'RJ', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-07-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('250', '10', '350.00', '275.00', '110.00', '850.50', '11.11', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-06-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('251', '10', '350.00', '275.00', '110.00', '50.50', '100.00', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-05-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('33', '34', '350.00', '275.00', '110.00', '50.50', '1111.11', 'SA', 'RJ', 'SA', 'SA', 'RJ', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-07-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('33', '15', '750.00', '550.00', '330.00', '151.00', '1111.11', 'RN', 'RJ', 'AA', 'RN', 'RJ', cast('2015-01-28' as date), cast('2015-03-15' as date), cast('2015-06-29' as date), cast('2015-08-03' as date), cast('2015-09-22' as date)),
('33', '34', '350.00', '275.00', '110.00', '50.50', '1000.00', 'SA', 'RJ', 'SA', 'SA', 'RJ', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-07-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('91', '13', '350.00', '275.00', '110.00', '850.50', '11.11', 'AA', 'RJ', 'AA', 'AA', 'TD', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-06-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date)),
('91', '13', '350.00', '275.00', '110.00', '50.50', '100.00', 'YY', 'OT', 'PB', 'YY', 'VW', cast('2015-01-25' as date), cast('2015-05-05' as date), cast('2015-05-19' as date), cast('2015-09-22' as date), cast('2015-09-22' as date))

Let’s look at how this data ia arranged, how it is pivoted from vertical to horizontal

select * from sales

This is not very conducive to getting total sales by employee or by store. Not easy to order by promo, approval, or date.  We need result like this pivoted table below (total is 75 lines, image samples at 29 lines):

Compare the next two queries, each of which produce the same table as pictured above, and see which one is more elegant and concise. First, using UNPIVOT:

-- Let's unpivot using Unpivot
SELECT RecordID Record, StoreID Store, employeeID Employee,
Promos 'All Promos',
AllPromos Amount, AllApprovals ApprovedBy, AllDates SalesDate
--, SubID=ROW_NUMBER() OVER (PARTITION BY StoreID ORDER BY AllPromos)
FROM (
SELECT RecordID, StoreID, EmployeeID, promo1 , promo2, promo3, promo4, promo5, aprove1, aprove2, aprove3, aprove4, aprove5, date1, date2, date3, date4, date5
FROM sales) Promos
UNPIVOT ( AllPromos FOR Promos IN (promo1 , promo2, promo3, promo4, promo5)) SortPromos
UNPIVOT ( AllApprovals FOR Approvals IN (aprove1, aprove2, aprove3, aprove4, aprove5)) SortApprovals
UNPIVOT ( AllDates FOR Dates IN (date1, date2, date3, date4, date5)) SortDates
where RIGHT(Promos, 1) = RIGHT(Approvals, 1) and RIGHT(Approvals, 1) = RIGHT(Dates,1) order by employeeID, AllDates

And now using CROSS APPLY:

-- Let's unpivot using cross apply
select * from (
SELECT Record, Store, Employee, Promos as 'All Promos', Amount, ApprovedBy, SalesDate -- you can name these whatever you want but they have to match below.  You can use an alias here but not below
FROM sales
CROSS APPLY (VALUES
(RecordID, storeID, employeeID, 'Promo1', promo1, aprove1, date1),
(RecordID, storeID, employeeID, 'promo2', promo2, aprove2, date2),
(RecordID, storeID, employeeID, 'promo3', promo3, aprove3, date3),
(RecordID, storeID, employeeID, 'promo4', promo4, aprove4, date4),
(RecordID, storeID, employeeID, 'promo5', promo5, aprove5, date5)
) SalesByPromo -- you can add a where clause but not an order by clause
(Record, Store, Employee, Promos, Amount, ApprovedBy, SalesDate) -- these have to match above
) SalesByEmployee order by employee, SalesDate

It really doesn’t matter to me that CROSS APPLY has a few more lines, because it just makes more sense, has greater parallelism and symmetry. Plus you only need to write CROSS APPLY once where as UNPIVOT has to be written for each column set. And you only name the one CROSS APPLY derived table whereas you have to name each UNPIVOT. Considering that there is an understandably strong push for code that is as human readable as possible, my vote is for CROSS APPLY every time. As for performance, execution time differences are negligible but CROSS APPLY pulls ahead as you tack on secondary and tertiary UNPIVOT instances. CROSS APPLY is the clear winner for me. Let me know what you think.

Removing duplicate Records in TSQL is likely to be a unique situation every time but there are enough consistent characteristics to the task that we can standardize some of it.  I certainly would not want a one-button-click solution because that would have too narrow a view of what a duplicate is.  Defining a duplicate is something for administrators to do because they know their data better than anyone else.  Ask a director, a manager and a user what a duplicate record is and you will likely get three different answers.  Sure, each will tell you when records appear as duplicates in reports and such, but the combination of fields that make up a duplicate is something that should be examined closely in every situation.  Other than the standard of backing up a table, identifying the fields that comprise duplicate records is the most important part of the duplicate removal process.

That being said, I have developed a semi-universal TSQL process that I use to identify and remove duplicates.  I use row_number, dense_rank and partition.

You probably already know how row_number creates a number incremented by one for each row.  Adding partition to row_number groups the results such that each new group begins a new pecking number within the set.  Dense_Rank creates  the same incremented number for each item in a set.  Using both of these will enable us to:

1. Identify entire sets/groups of records where data in given fields are the same
2. Allow us to select/update/delete any subset within each set/group
   1. Any multiples of records greater than one (just the duplicates – 2nd, 3rd,4th etc.)
   2. Any specified item in a set/group (1st, 2nd, 3rd etc.)

/* Proof of concept for removing duplicates
 Intended use:
 1.) Updating multiples of identical records.
 2.) Removing second thru n count of duplicates.

Author: Tim Bartel 2015.09.22
*/

-- Create simple table of employee records
 create table employees (
 recordID int IDENTITY(1,1) NOT NULL,
 nameFirst varchar(50),
 nameLast varchar(100),
 salutation varchar(10),
 employeeNumber int,
 hireDate date,
 exitDate date
 )

-- Insert some records, including duplicates of employee number
 insert into employees (nameFirst, nameLast, salutation, employeeNumber, hireDate, exitDate)
 values ('April', 'Showers', 'Ms', 1001, '2010-01-31', null),
 ('May', 'Flowers', 'Ms', 1002, '2010-05-01', null),
 ('Jack', 'Jumping', 'Dr', 1003, '2011-06-11', null),
 ('Sal', 'Manilla', 'Mr', 1004, '2012-12-14', null),
 ('Al', 'Bondegas', 'Mr', 1005, '2013-09-15', null),
 ('Trip', 'Youup', 'Sr', 1006, '2013-09-15', null),
 ('Trip', 'Youup', 'Jr', 1007, '2014-07-10', '2014-08-10'),
 ('Trip', 'Youup', 'Jr', 1007, '2015-02-24', null),
 ('Trip', 'Youup', 'Jr', 1007, '2015-02-24', null),
 ('Trip', 'Youup', 'Jr', 1007, '2015-02-24', null),
 ('Mia', 'Culpa', 'Mrs', 1008, '2011-09-01', null),
 ('Ben', 'Stumped', 'Mr', 1009, '2014-08-10', '2014-09-10'),
 ('Ben', 'Stumped', '', 1009, '2015-03-24', null),
 ('Ben', 'Stumped', '', 1009, '2015-03-24', null),
 ('Ben', 'Stumped', 'Mr', 1009, '2015-03-24', null),
 ('Ben', 'Stumped', '', 1009, '2015-03-24', null)

Have a look at the records. Note that Trip Youup and Ben Stumped, are both previous employees who have returned and have been given the same employeeNumber as thier first time as employees. These are easy to spot and query by the exitDate in a small rescord set like this one (records 9,10 and 14,15,16 are true duplicates which need to be deleted, while records 8 and 13 need a new employeeNumber). Note that the first instance of Trip Youup with employeeNumber 1006 is a different Trip Youup than those with employee number 1007. 1006 is Trip Youup Senior, and 1007 is Trip Youup Junior. So employeeNumber is a determining factor.

select * from employees

Check for duplicates, create rownum for ordering and dense_rank for grouping.

ORDERING:
* rownum is a sequential count that resarts with 1 at the first new instance of records that meet the criteria in the partiton by clause.
** The end result is an ascending count of duplicate records which can be used for ordering of duplicate records, and selecting any multiple by count value

GROUPING:
* Desnse_Rank is a sequential count that repeats with each duplicate record which meets the criteria in the order by clause.
** The end result is a unique number that can be used to group identical records of the specified criteria.

 select * from (
 select row_number() over(partition by employeeNumber, nameFirst, nameLast order by employeeNumber) as rownum, DENSE_RANK() over(order by employeeNumber) as rank, recordID, nameFirst, nameLast, employeeNumber, hireDate, exitDate from employees
 ) e -- all 16 records
 where rownum >1 -- 7 records returned (One set of three [rank 7], and one set of four [rank 9])

We need to do the following:

1. Keep the first instance of the employeeNumber unchanged
2. Create a new employeeNumber for the second instance (the third + records can be changed too, it doesn’t matter because we are going to delete them)
3. Delete all remaining multiples
4. Make the script scalable for higher volume of records

-- Create a temporary table that contains only the multiples records 8,9,10 and 13,14,15,16), not the first instances (records 7 and 12).
 -- Use dense_rank in the outer select to get the value which we will use to increment the employee number (Addend)
 select DENSE_RANK() over(order by rank) as 'Addend', *
 into #employees_temp
 from (
 select row_number() over(partition by employeeNumber, nameFirst, nameLast order by employeeNumber) as rownum, DENSE_RANK() over(order by employeeNumber) as rank, recordID, nameFirst, nameLast, employeeNumber, hireDate, exitDate from employees
 ) e where rownum >1

How to select all instances of duplicates – including the first one

 select * from employees
 where employeeNumber in (select employeeNumber from #employees_temp where rownum >1) -- the full compliment of 9 rows

How to select only the non-primary instances of the duplicates (counts 2 thru n)

select * from employees
 where recordID in (select recordID from #employees_temp where rownum >1) -- just the 7 duplicates

Check calculation for changing the employeeNumber to the next highest number that is not in use

Note:
Augend – mathematical term for the first of the two numbers being added. Addend – mathematical term for the second of two numbers being added.

 declare @newEmpNum int
 select @newEmpNum = (select max(employeeNumber) from employees)
 select e.recordID, e.employeeNumber, rank, @newEmpNum ' Augend',Addend, @newEmpNum + Addend 'Sum' from #employees_temp et inner join employees e
 on et.recordID=e.recordID

Convert select to an Update: Records 8,9,10,13,14,15 and 16 will be updated

declare @newEmpNum int
 select @newEmpNum = (select max(employeeNumber) from employees)
 UPDATE e set e.employeeNumber=@newEmpNum + Addend from #employees_temp et inner join employees e
 on et.recordID=e.recordID -- (7 row(s) affected)

How to select duplicates from temp table

select * from #employees_temp where rownum > 2 -- 5 records

Delete duplicates from employee table based on recordID (unique key) of duplicates in temp table)

delete from employees where recordID in (select recordID from #employees_temp where rownum > 2) -- (5 row(s) affected)

Records 9,10,14,15 and 16 are now deleted.

 select * from employees

Check for duplicates

 select * from (
 select row_number() over(partition by employeeNumber, nameFirst, nameLast order by employeeNumber) as rownum, DENSE_RANK() over(order by employeeNumber) as rank, recordID, nameFirst, nameLast, employeeNumber, hireDate, exitDate from employees
 ) e where rownum >1 -- zero records returned

Let me know if you found this useful.

In TSQL IsNumeric evaluates data to see if it can be cast as any of ten numeric types of data: int, numeric, bigint, money, smallint, smallmoney, tinyint, float, decimal, real. The problem is that most of the time what we really and practically want to know is, is this number an integer? Or, is this number a decimal? Sadly, SSMS doesn’t come with a built in system function for IsInt or IsDecimal. But the good news is that you can build a user defined function (UDF) that will do the same thing.
I’m going to show you how do write the function (actually I’m going to give it to you already written), and show you how to make use of it once it’s up and running, but first I have to give credit where credit is due. The basic code for evaluating integer and decimal values was posted at StackOverflow. I added a IsNull and rtrim to clean it up even more:
IsNumeric(rtrim(ISNULL(myvarchar,1)) + ‘e0’)
Basically, all you need to do is add scientific notation (e0) to your value when parsing IsNumeric thus eliminating numbers that are already expressed exponentially, as often happens when copying/pasting from Excel. There’s a notation for integer and a notation for decimal.
Here’s my UDF for IsInt:

/*
 Name: IsInt
 Author Tim Bartel
 Created on 5/30/2014
 Purpose: create a function for evaluating if a varchar can be cast as an int.
 Returns boolean.
 0 = No, cannot cast to int
 1 = Yes, can cast to int
 */
 If object_id('dbo.IsInt') IS NOT NULL
 DROP FUNCTION dbo.IsInt
 GO
 CREATE FUNCTION dbo.IsInt (@myvarchar varchar(max))
 RETURNS int
 AS
 BEGIN
 DECLARE @intCheck int;
 SELECT @intCheck=IsNumeric(rtrim(ISNULL(@myvarchar,1)) + '.0e0')
 RETURN @intCheck;
 END
 GO

Here’s my UDF for IsDecimal:

 /* 
 Name: IsDecimal
 Author Tim Bartel
 Created on  5/30/2014
 Purpose: create a function for evaluating if a varchar can be cast as a decimal.
 Returns boolean.  
 0 = No, cannot cast to decimal 
 1 = Yes, can cast to decimal
 */
 If object_id('dbo.IsDecimal') IS NOT NULL
            DROP FUNCTION dbo.IsDecimal
GO
  CREATE FUNCTION dbo.IsDecimal (@myvarchar varchar(max))
  RETURNS int
  AS
  BEGIN
  DECLARE @decimalCheck int;
  SELECT @decimalCheck=IsNumeric(rtrim(ISNULL(@myvarchar,1)) + 'e0')
  RETURN @decimalCheck;
  END
GO  

Remember to preface your function by dbo so SQL knows where to look for the function. Yeah, that’s the bummer side of a user defined function; they are only available in the database where you installed them. If you want them in all your databases, you have to install them in all your databases. I have yet to discover a safe and easy method of installing a function as a system function. Let me know if you find such a way.
Now that you’ve executed the scripts above in your databases, you can do nifty things like find all the data that can’t be cast as an decimal… just by a lick of script like this:

 SELECT height FROM Demographics WHERE dbo.IsDecimal(height)=0

Or find data that can be cast as integer like this:

 SELECT age FROM Demographic WHERE dbo.IsInt(age)=1

How about putting that inline like so:
SELECT dbo.IsInt(age) , dbo.IsDecimal(height) FROM Demographic…
It’s a lot easier to remember and write than:
SELECT IsNumeric(rtrim(ISNULL(age,1)) + ‘e0’), IsNumeric(rtrim(ISNULL(height,1)) + ‘.0e0’) FROM Demographic…
Your welcome 😉

BTW, my UDFs above treat nulls as viable data to be cast as int or decimal. I did this because null cast as int or decimal is null. If you want your UDF to treat nulls as non-int or non-decimal – that is to say, you want nulls to fail the IsInt or IsDecimal test – then change 1 to 0 in the IsNull function like so:

 SELECT @intCheck=IsNumeric(rtrim(ISNULL(@myvarchar,0)) + '.0e0')

SELECT @decimalCheck=IsNumeric(rtrim(ISNULL(@myvarchar,0)) + 'e0')

If you like the function as is and just want to occasionally get a result set where nulls are invalid then wrap the field name inside function IsInt or IsDecimal UDF in an IsNull function and set null equal to a value that will fail… like so:

 SELECT dbo.IsDecimal(IsNull(height,'1.2.3')) FROM Demographics

SELECT dbo.IsInt(IsNull(age,'1.2.3')) FROM Demographic

Want a table with mixed data to test this in? No problem…

-- CREATE DATA FOR TESTING ISINT AND ISDECIMAL
CREATE TABLE Demographics (height varchar(7), age varchar(7))

-- Add mixed decimal/non-decimal, int/non-int data
 insert into Demographics (height, age) VALUES
 ('12.345', '0o1'),
 ('12.3456', 'o02'),
 ('`78', 'oo3'),
 ('''78.9', 'IV_4'),
 ('''78', 'V 5'),
 ('6''04', 'six'),
 ('64..5', '&'),
 ('64+', '*'),
 ('6+\4', 'Nine'),
 ('6+4', '010'),
 ('64..25', '1.10'),
 ('62`', '1..2'),
 ('64=5', '12.345'),
 ('68.5.', '`78'),
 ('68/', '''78.9'),
 ('68o', '''78'),
 ('7.0.5', '6''04'),
 ('70in', '64+'),
 ('5*', '6+\4'),
 (' 64', '6+4'),
 (' 64', '62`'),
 (' 64', '64=5'),
 (' 64', '68.5.'),
 (' 64', '68/'),
 ('64.5432', '7.0.5'),
 ('+64', '70in'),
 ('64,75', '5*'),
 ('64.753', ' 64'),
 ('+66.5', ' 64'),
 (' 64.25', ' 64'),
 (' 64.25', ' 64'),
 ('64.25 ', '64.543'),
 ('64.25 ', '+64'),
 ('`64.5', '64,75'),
 ('`64', '+66.5'),
 ('69.5.', ' 64.25'),
 ('11.11', ' 64.25'),
 ('$', '64.25 '),
 ('+', '64.25 '),
 (',', '`64.5'),
 ('-', '`64'),
 ('.', '69.5.'),
 ('€', '11.11'),
 ('£', '$'),
 ('¤', '+'),
 ('¥', ','),
 ('v', '-'),
 ('5.E4', '.'),
 ('5.0E4', '€'),
 ('5e4', '£'),
 ('5e4.e4', '¤'),
 ('5e4.0e0', '¥'),
 (NULL, 'v'),
 (NULL, '5.E4'),
 (NULL, '5.0E4'),
 (NULL, '5e4'),
 (NULL, '5e4.e4'),
 (NULL, '5e4.0e0'),
 (NULL, '+3'),
 (NULL, '-3'),
 (NULL, ''),
 (NULL, ' '),
 (NULL, NULL)
 -- (63 row(s) affected)

Sometimes we want to filter query results based on results from multiple fields from a sub-query or in a where clause but if you try this you’ll get and error message:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Let’s say that we are trying to join results of two queries in a union table but we want to omit duplicates. In this case we don’t really want to use exists or not exists because our goal is to eliminate specific results from a result set… a sort of combination of exists and not exists.

My solution is to concatenate the desired columns into one column. That way all your criteria is evaluated in a single expression.

For instance:
 ... WHERE t.name + c.name + cast(c.column_id as nvarchar(3)) NOT IN (
 select t.name + c.name + cast(c.column_id as nvarchar(3))
 FROM ... )

Here is a practical example of a situation where I used this concatenation tip to eliminate duplicate results in a union table of keys and constraints. It’s useful as a stored procedure because it allows you to pass a list of tables copy/pasted from Excel (or from a query grid result set) without having to add a delimiter. The line break that is natural to copying multiple records from Excel is the delimiter.

/*
 Name: sp_FindContraints
 Description: Get Primary keys and required fields
 Author: Tim Bartel
 Modification Log: Change
 Description Date Changed By
 Created procedure 07/28/2014 Tim Bartel

Intructions: Pass list of table names to this stored procedure.
 Requirements: List MUST contain line breaks.
 Inclusions: List may contain duplicates, list may contain blank or empty rows

Example:
 exec sp_FindContraints 'MyTable_001
 MyTable_002
 MyTable_001

MyTable_003
 MyTable_004
 MyTable_006
 MyTable_009
 MyTable_007
 MyTable_009

MyTable_010
 ';
*/

If object_id('dbo.sp_FindContraints') IS NOT NULL
 DROP PROC dbo.sp_FindContraints
 GO

CREATE PROCEDURE sp_FindContraints
@tablelist varchar(max) -- list of table names. May contain line breaks.
AS
 BEGIN
 SELECT [Table Name], [Column Name], [Constraint Type] FROM
 (-- Find keys by table name
 select t.name AS 'Table Name', c.name AS 'Column Name', k.type_desc AS 'Constraint Type'
 FROM sys.indexes i
 INNER JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
 INNER JOIN sys.columns c ON c.object_id=i.object_id AND c.column_id=ic.column_id
 INNER JOIN sys.tables t ON t.object_id = c.object_id
 INNER JOIN sys.key_constraints k ON k.parent_object_id=i.object_id AND k.name=i.name
 where t.name IN (SELECT distinct item FROM dbo.fnSplit(@tablelist,'
 '))
 UNION ALL
 -- Find non nullable fields
 select t.name AS 'Table Name', c.name AS 'Column', CASE is_nullable
 WHEN 0 THEN 'Not NULL' ELSE ''END AS 'Constraint'
 from sys.tables t
 INNER JOIN sys.columns c ON t.object_id = c.object_id
 where t.name IN (SELECT distinct item FROM dbo.fnSplit(@tablelist,'
 '))
 AND is_nullable=0
 AND t.name + c.name + cast(c.column_id as nvarchar(3)) NOT IN ( -- Remove fields with keys
 select t.name + c.name + cast(c.column_id as nvarchar(3))
 FROM sys.indexes i
 INNER JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id=i.index_id
 INNER JOIN sys.columns c ON c.object_id=i.object_id AND c.column_id=ic.column_id
 INNER JOIN sys.tables t ON t.object_id = c.object_id
 INNER JOIN sys.key_constraints k ON k.parent_object_id=i.object_id AND k.name=i.name
 where t.name IN (SELECT distinct item FROM dbo.fnSplit(@tablelist,'
 '))
 )
 ) Keys
 ORDER BY [Table Name], case [Constraint Type] WHEN 'Not NULL' THEN 99 ELSE 1 END
END

Union tables join tables vertically, which is fine if you want one long set of data returned, but what if you want to compare data side-by-side in one table horizontally? And what if your are trying to conserve space by shifting up null cells? This is the kind of thing that is usually done with reports, or in Word or Excel. Yet if you want to save valuable editing time, how would you do that directly in SQL? I’m going to show you an uncommon table join. Uncommon, not because of the type of table join but because of the columns by which we are going to join.

The problem:
Let’s say you have a list of 23 employees that you have put on two different task force teams, each team having the same duties delegated according to the employee’s status level. You want to see the two teams listed side by side without redundant duties. That is, you want the employees from each team to appear horizontally next to each other wherever their duties are the same with nulls/blanks only appearing when one team does not have an employee with matching duty.

For instance:
This is team 500 (11 employees)

SELECT  * FROM employee WHERE team_id='500'

This is team 501 (12 employees)

SELECT * FROM employee WHERE team_id='501'

This is the result that you want
(15 overlapping records, side-by-side – horizontal)

The Solution
The key is to create a rank for each duty in each of the two tables and then join the select statements on the matching ranks for each duty. Let’s take it step by step.

Create a rank:

SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank,
 * FROM employee WHERE team_id='500'

SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank,
 * FROM employee WHERE team_id='501'

Now you can join the two tables via sub-query on their duty and duty rank:

SELECT T500.emp_first_name +' '+ T500.emp_last_name AS 'Team 500',
 T501.emp_first_name +' '+ T501.emp_last_name AS 'Team 501'
 FROM
(SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='500') T500

FULL OUTER JOIN
 (SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='501') T501
 ON T500.duty_name=T501.duty_name AND T500.DutyRank=T501.DutyRank

The last thing we need to do is add a column of distinct duty. I also added ISNULL to handle nulls as blanks. Here’s the final code:

SELECT ISNULL(T500.emp_first_name, '') +' '+ ISNULL(T500.emp_last_name, '') AS 'Team 500',
 D.duty_name, ISNULL(T501.emp_first_name,'') +' '+ ISNULL(T501.emp_last_name,'') AS 'Team 501'
 FROM
(SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='500') T500

FULL OUTER JOIN
 (SELECT row_number() OVER ( PARTITION BY duty_name ORDER BY duty_name ) AS DutyRank, *
 FROM employee WHERE team_id='501') T501
 ON T500.duty_name=T501.duty_name AND T500.DutyRank=T501.DutyRank

INNER JOIN
 (SELECT distinct duty_name FROM employee) D
 ON D.duty_name=T500.duty_name OR D.duty_name=T501.duty_name
 ORDER BY D.duty_name

Background/Supporting Code:
If you want to test this code yourself, here’s the sql to create the table and data:
(disclaimer: These are completely fabricated names. Any similarity to real persons is unintentional.)

create table employee
 (emp_id char(3) not null,
 emp_first_name varchar(20) not null,
 emp_last_name varchar(20) not null,
 team_id char(3) not null,
 duty_name varchar(20))

--intitial records
 insert into employee
 values ('111','Al','Quinne', '500', 'Manager'),
 ('112','Betty','Green', '500', 'Specialist'),
 ('113','James','Saintjames', '500', 'Staff'),
 ('114','Jane','Hathaway', '500', 'Manager'),
 ('115','Ethan','Walker', '501', 'Specialist'),
 ('116','Wendy','Wright', '501', 'Consultant'),
 ('117','Perry','Lsughton', '501', 'Programmer'),
 ('118','Mary','Truce', '501', 'Staff'),
 ('119','Caitlin','Lancer', '501', 'Specialist'),
 ('120','Tobias','Indigo', '501', 'Specialist'),
 ('121','Oscar','Funkmier', '501', 'Specialist'),
 ('122','Quincy','Openhiem', '501', 'Specialist'),
 ('123','Judy','Trudy', '501', 'Specialist'),
 ('124','Pfeiffer','Jones', '501', 'Consultant'),
 ('125','Deanna','Kincade', '501', 'Consultant'),
 ('126','Peter','Sangabriel', '500', 'Specialist'),
 ('127','Gabriella','Wintruabe', '500', 'Specialist'),
 ('128','Bartholomew','Jacoby', '500', 'Specialist'),
 ('129','Micha','Elcondios', '500', 'Programmer'),
 ('130','Jack','Delta', '500', 'Programmer'),
 ('131','Ian','Shrewinicki', '500', 'Programmer'),
 ('132','Lena','Lawrence', '500', 'Consultant'),
 ('133','Bob','Urunkle', '501', 'Manager')

The SOUNDEX function in SQL, analyzes the phonetics of a given string of text and returns a value which can then be used to find similar values thus searching through text for similar sounding strings. The algorithm is actually a simple set of values assigned to similar sounding consonants.

Here’s the algorithm:
Letters B, F, P, V = 1
Letters C, G, J, K, Q, S, X, Z = 2
Letters D, T = 3
Letter L = 4
Letters M,N = 5
Letter R = 6

Letters A, E, I, O, U, H, W, Y are disregarded

Anything string resulting in a code with greater than four characters is truncated

Any string resulting in fewer than four characters is concatenated by zeros

Using the Soundex algorithm, I converted my name Tim Bartel to T500 B634.

Try it on your name using this JavaScript Soundex name converter:

There are three differences from the code that’s embedded on this page and the one below.
1. I’m not using a form to post back to the same page because of WordPress, so I’m using JavaScript in the button call to get the input values. View page source if you want to compare.
2.Removed carriage returns from the JavaScript – again to placate WordPress.
3. Layout of input fields using a table (It’s still vogue to layout forms and data with tables… just not entire pages – just gotta watch out for minimum widths on small mobile devices).

<!DOCTYPE html>
 <html>
 <body>

<script language="JavaScript" type="text/javascript">
 function soundex(first,last){
 var soundex="";
 var person={fname:first,lname:last};
 if(first, last){
 for (x in person){
 var a = person[x].toUpperCase().split(''),
 f = a.shift(),
 r = '',
 codes = {
 A: '', E: '', I: '', O: '', U: '', H: '', W: '', Y: '',
 B: 1, F: 1, P: 1, V: 1,
 C: 2, G: 2, J: 2, K: 2, Q: 2, S: 2, X: 2, Z: 2,
 D: 3, T: 3,
 L: 4,
 M: 5, N: 5,
 R: 6
 };
 r = f + a.map(function (v, i, a) {
 return codes[v] ;}).filter(function (v, i, a) {
 return ((i === 0) ? v !== codes[f] : v !== a[i - 1]);
 }).join('');
 soundex=soundex + (r + '000').slice(0, 4) + " ";
 }
 }
 else{
 soundex = 'you must enter a first and last name';
 }
 document.getElementById("sndxname").innerHTML=soundex;
 }
 </script>

<form name="soundexForm" id="soundexForm" >
 First Name: <input type="text" name="fname" value="" / > <br />
 Last Name: <input type="text" name="lname" value="" / > <br />
 <input type="button" value="Soundex Me"
 onclick="soundex(document.soundexForm.fname.value,document.soundexForm.lname.value);" />
 <br />
 The soundex value of your name is:
 <span style="font-weight:bold;" id="sndxname" name="sndxname"></ span>
 </ form>
 </body>
 </html>

I used a soundex script posted to GitHub by Shawn Dumas (https://gist.github.com/shawndumas/1262659). I made the following changes:
1. Added explicit blank values for characters H, W and Y.
2. Removed unnecessary case change.
3. Added check for undefined variable where form is left blank
4. loop through first and last names
5. Concatenate first and last names into one string
6. Return results to page by innerHTML
7. Corrected many syntax errors – mostly missing semi-colons

My favorite snippet of programming code is the ternary operator used to handle a Boolean evaluation (true/false). It is concise no matter which language Ruby, JavaScript or VB.net.

Anatomy of the Ternary operator:
A ternary is a two-step conditional method; evaluate and execute. The evaluation must be conditional Boolean, that is it must ask for true false results. Think of it like a turnstile or a fork in a road where it will either be open or closed. There is no third option as with If/Then/Else. But the beauty of the ternary is not in extensibility but rather brevity. The ternary is concise and keeps code tight and short. Consider the following JavaScript If/Else statement that sets a discount of 10% only if the age variable is greater than or equal to 65.

if(age>=65){
 discount = total * '.10';
 }
 else{
 discount = '0';
 }

Now look at the Ternary version:

discount=(age>=65)? total * '.10' : '0' ;

Here’s the syntax in JavaSctipt, Ruby and VB:

Ruby Ternary Operator:
 (condition) ? (result if the condition is true) : (result if the condition is false)

discount=(age>=65) ? total * '.10' : '0'

VB.NET 2008 Ternary Operator:
 If((Condition), (result if the condition is true), (result if the condition is false)) 

discount = If(age>=65, total; * '.10', '0')

VB 6.0 and up Ternary Operator:
 IIf((Condition), (result if the condition is true), (result if the condition is false))

discount = IIf(age>=65, total; * '.10', '0')

JavaScript Ternary Operator (AKA Conditional Operator):
 (condition) ? value1 : value2 ;

discount=(age>=65)? total * '.10' : '0' ;

Try a client side JavaScript version yourself:

You can also copy/paste the code below into a text editor and save as and html document. Browse the document with I.E, Firefox or Chrome and you’ll get a working example.

<!DOCTYPE html>
<html>
<body>
<p>Are you eligible for a senior discount?</p>
How old are you? <input id="age" value="65" />
<button onclick="seniorDiscount()">Check for discount</button>
<p>Total: 250</br>
Discount: <span id="discountme">250</span></br>
Balance: <span id="totalme"></span>
</p>
</div>
<script>function seniorDiscount(){var total,age,discount;total='250';age=document.getElementById("age").value;discount=(age>=65)? total * '.10' : '0';total -= discount;document.getElementById("discountme").innerHTML=discount;document.getElementById("totalme").innerHTML=total;}</script>
</body>
</html>