none
Comparing Column Differences

    Question

  • I have a large table with 88 columns (not normalized, yet). I need to list any column differences within this table based upon the unique values (PK)  of DatabaseName, LastName, FirstName and FullSSNo.

    The baseline to match against are all rows with the DatabaseName='Main'. All other rows need to be compared to the “Main”  Row based upon a match with the LastName, FirstName and FullSSNo with any columns which differ from the “Main” Row.

    In addition, there are rows in this table that also do not have an equivalent row based upon the unique combination (PK) of LastName, FirstName and FullSSNo with a DatabaseName='Main' and these rows must be also be listed.

    I have converted all of the columns to varchar so they can easily be compared.

    I've made some good progress, but I’m stuck in the UnPivot Join Portion.

    Table Definition

    CREATE TABLE [dbo].[Main Database](
          [LAST NAME] [nvarchar](30) NOT NULL,
          [FIRST NAME] [nvarchar](30) NOT NULL,
          [MI] [nvarchar](30) NULL,
          [EDC] [date] NULL,
          [BRANCH OF SERVICE] [nvarchar](50) NULL,
          [RANK] [nvarchar](30) NULL,
          [FULL SSN] [nvarchar](255) NOT NULL,
          [EAS] [date] NULL,
          [CONTACT PHONE] [nvarchar](255) NULL,
          [NHCP CM] [nvarchar](30) NULL,
          [PRIMARY NHCP CM] [bit] NOT NULL,
          [PATIENT CO-MANAGED] [bit] NOT NULL,
          [DATE OF MEDEVAC] [date] NULL,
          [MEDEVAC CATAGORY] [nvarchar](50) NULL,
          [RESERVIST] [bit] NOT NULL,
          [CURRENT STATUS] [nvarchar](50) NULL,
          [DMIS] [nvarchar](30) NULL,
          [COMMAND] [nvarchar](30) NULL,
          [COMPANY] [nvarchar](30) NULL,
          [MSC] [nvarchar](50) NULL,
          [SOURCE] [nvarchar](50) NULL,
          [PCM] [nvarchar](30) NULL,
          [PCM CLINIC] [nvarchar](30) NULL,
          [REFFERED BY] [nvarchar](100) NULL,
          [DATE OPENED] [date] NULL,
          [DATE CLOSED] [date] NULL,
          [TYPE OF CASE] [nvarchar](30) NULL,
          [COMPLEXITY] [nvarchar](30) NULL,
          [ACUITY] [nvarchar](30) NULL,
          [MECHANISM OF INJURY] [nvarchar](30) NULL,
          [BLAST EXPOSURE] [bit] NOT NULL,
          [TBI] [bit] NOT NULL,
          [AMPUTATION] [bit] NOT NULL,
          [PTSD] [bit] NOT NULL,
          [DIAGNOSIS] [nvarchar](255) NULL,
          [TREATMENT PLAN] [nvarchar](255) NULL,
          [DISCHARGE DATE] [date] NULL,
          [TIME MIN/MONTH] [int] NULL,
          [BOARD STATUS] [nvarchar](50) NULL,
          [BOARD EXPIRATION DATE] [date] NULL,
          [DATE REFERRED TO VA REP] [date] NULL,
          [NEURO/PSYC EVAL DATE] [date] NULL,
          [FINAL DISPOSITION] [nvarchar](50) NULL,
          [HOSPITAL CORPSMAN] [bit] NOT NULL,
          [NHCP AUGMENT] [bit] NOT NULL,
          [SPECIAL CONSIDERATIONS] [nvarchar](255) NULL,
          [COMMENTS] [nvarchar](255) NULL,
          [COMMENTS CONTINUED] [nvarchar](255) NULL,
          [FMP] [nvarchar](4) NULL,
          [MARRIED] [bit] NOT NULL,
          [SPONSORS NAME] [nvarchar](50) NULL,
          [SPONSORS LAST FOUR] [nvarchar](4) NULL,
          [REFERRED TO] [nvarchar](50) NULL,
          [C5 PT] [bit] NOT NULL,
          [NHCP] [nvarchar](50) NULL,
          [NHCP YES/ NO] [bit] NOT NULL,
          [NHCP DEPT 1] [nvarchar](50) NULL,
          [NHCP DEPT 2] [nvarchar](50) NULL,
          [NHCP DEPT 3] [nvarchar](50) NULL,
          [NHCP DEPT 4] [nvarchar](50) NULL,
          [NMCSD] [nvarchar](50) NULL,
          [NMCSD YES/ NO] [bit] NOT NULL,
          [NMCSD DEPT 1] [nvarchar](50) NULL,
          [NMCSD DEPT 2] [nvarchar](50) NULL,
          [NMCSD DEPT 3] [nvarchar](50) NULL,
          [NMCSD DEPT 4] [nvarchar](50) NULL,
          [OTHER] [nvarchar](50) NULL,
          [OTHER YES/NO] [bit] NOT NULL,
          [OTHER TYPE] [nvarchar](15) NULL,
          [OTHER DEPT 1] [nvarchar](50) NULL,
          [OTHER DEPT 2] [nvarchar](50) NULL,
          [OTHER DEPT 3] [nvarchar](50) NULL,
          [OTHER DEPT 4] [nvarchar](50) NULL,
          [OTHER 2] [nvarchar](50) NULL,
          [OTHER 2 YES/NO] [bit] NOT NULL,
          [OTHER 2 TYPE] [nvarchar](50) NULL,
          [OTHER 2 DEPT 1] [nvarchar](50) NULL,
          [OTHER 2 DEPT 2] [nvarchar](50) NULL,
          [OTHER 2 DEPT 3] [nvarchar](50) NULL,
          [OTHER 2 DEPT 4] [nvarchar](50) NULL,
          [OTHER 3] [nvarchar](50) NULL,
          [WWBn] [bit] NOT NULL,
          [CATEGORY] [int] NULL,
          [BEN CAT] [nvarchar](255) NULL,
          [RowId] [int] IDENTITY(1,1) NOT NULL,
          [LastUpdatedDate] [datetime] NULL,
          [UserName] [nvarchar](75) NULL,
          [DatabaseName] [nvarchar](75) NULL,
     CONSTRAINT [aaaaaMain Database_PK] PRIMARY KEY NONCLUSTERED 
    (
          [RowId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    View Definition 

    ALTER VIEW [dbo].[vw_CaseMgmt]
    AS
    Select 
    RowId as RowNo
    , Cast (Isnull([LAST NAME],'') as varchar(100)) as [LAST NAME]
    , Cast (Isnull([FIRST NAME],'') as varchar(100)) as [FIRST NAME]
    , Cast (Isnull([MI],'') as varchar(100)) as [MI]
    , Cast (Isnull([EDC],'') as varchar(100)) as [EDC]
    , Cast (Isnull([BRANCH OF SERVICE],'') as varchar(100)) as [BRANCH OF SERVICE]
    , Cast (Isnull([RANK],'') as varchar(100)) as [RANK]
    , Cast (Isnull([FULL SSN],'') as varchar(100)) as [FULL SSN]
    , Cast (Isnull([EAS],'') as varchar(100)) as [EAS]
    , Cast (Isnull([CONTACT PHONE],'') as varchar(100)) as [CONTACT PHONE]
    , Cast (Isnull([NHCP CM],'') as varchar(100)) as [NHCP CM]
    , Cast (Isnull([PRIMARY NHCP CM],'') as varchar(100)) as [PRIMARY NHCP CM]
    , Cast (Isnull([PATIENT CO-MANAGED],'') as varchar(100)) as [PATIENT CO-MANAGED]
    , Cast (Isnull([DATE OF MEDEVAC],'') as varchar(100)) as [DATE OF MEDEVAC]
    , Cast (Isnull([MEDEVAC CATAGORY],'') as varchar(100)) as [MEDEVAC CATAGORY]
    , Cast (Isnull([RESERVIST],'') as varchar(100)) as [RESERVIST]
    , Cast (Isnull([CURRENT STATUS],'') as varchar(100)) as [CURRENT STATUS]
    , Cast (Isnull([DMIS],'') as varchar(100)) as [DMIS]
    , Cast (Isnull([COMMAND],'') as varchar(100)) as [COMMAND]
    , Cast (Isnull([COMPANY],'') as varchar(100)) as [COMPANY]
    , Cast (Isnull([MSC],'') as varchar(100)) as [MSC]
    , Cast (Isnull([SOURCE],'') as varchar(100)) as [SOURCE]
    , Cast (Isnull([PCM],'') as varchar(100)) as [PCM]
    , Cast (Isnull([PCM CLINIC],'') as varchar(100)) as [PCM CLINIC]
    , Cast (Isnull([REFFERED BY],'') as varchar(100)) as [REFFERED BY]
    , Cast (Isnull([DATE OPENED],'') as varchar(100)) as [DATE OPENED]
    , Cast (Isnull([DATE CLOSED],'') as varchar(100)) as [DATE CLOSED]
    , Cast (Isnull([TYPE OF CASE],'') as varchar(100)) as [TYPE OF CASE]
    , Cast (Isnull([COMPLEXITY],'') as varchar(100)) as [COMPLEXITY]
    , Cast (Isnull([ACUITY],'') as varchar(100)) as [ACUITY]
    , Cast (Isnull([MECHANISM OF INJURY],'') as varchar(100)) as [MECHANISM OF INJURY]
    , Cast (Isnull([BLAST EXPOSURE],'') as varchar(100)) as [BLAST EXPOSURE]
    , Cast (Isnull([TBI],'') as varchar(100)) as [TBI]
    , Cast (Isnull([AMPUTATION],'') as varchar(100)) as [AMPUTATION]
    , Cast (Isnull([PTSD],'') as varchar(100)) as [PTSD]
    , Cast (Isnull([DIAGNOSIS],'') as varchar(100)) as [DIAGNOSIS]
    , Cast (Isnull([TREATMENT PLAN],'') as varchar(100)) as [TREATMENT PLAN]
    , Cast (Isnull([DISCHARGE DATE],'') as varchar(100)) as [DISCHARGE DATE]
    , Cast (Isnull([TIME MIN/MONTH],'') as varchar(100)) as [TIME MIN/MONTH]
    , Cast (Isnull([BOARD STATUS],'') as varchar(100)) as [BOARD STATUS]
    , Cast (Isnull([BOARD EXPIRATION DATE],'') as varchar(100)) as [BOARD EXPIRATION DATE]
    , Cast (Isnull([DATE REFERRED TO VA REP],'') as varchar(100)) as [DATE REFERRED TO VA REP]
    , Cast (Isnull([NEURO/PSYC EVAL DATE],'') as varchar(100)) as [NEURO/PSYC EVAL DATE]
    , Cast (Isnull([FINAL DISPOSITION],'') as varchar(100)) as [FINAL DISPOSITION]
    , Cast (Isnull([HOSPITAL CORPSMAN],'') as varchar(100)) as [HOSPITAL CORPSMAN]
    , Cast (Isnull([NHCP AUGMENT],'') as varchar(100)) as [NHCP AUGMENT]
    , Cast (Isnull([SPECIAL CONSIDERATIONS],'') as varchar(100)) as [SPECIAL CONSIDERATIONS]
    , Cast (Isnull([COMMENTS],'') as varchar(100)) as [COMMENTS]
    , Cast (Isnull([COMMENTS CONTINUED],'') as varchar(100)) as [COMMENTS CONTINUED]
    , Cast (Isnull([FMP],'') as varchar(100)) as [FMP]
    , Cast (Isnull([MARRIED],'') as varchar(100)) as [MARRIED]
    , Cast (Isnull([SPONSORS NAME],'') as varchar(100)) as [SPONSORS NAME]
    , Cast (Isnull([SPONSORS LAST FOUR],'') as varchar(100)) as [SPONSORS LAST FOUR]
    , Cast (Isnull([REFERRED TO],'') as varchar(100)) as [REFERRED TO]
    , Cast (Isnull([C5 PT],'') as varchar(100)) as [C5 PT]
    , Cast (Isnull([NHCP],'') as varchar(100)) as [NHCP]
    , Cast (Isnull([NHCP YES/ NO],'') as varchar(100)) as [NHCP YES/ NO]
    , Cast (Isnull([NHCP DEPT 1],'') as varchar(100)) as [NHCP DEPT 1]
    , Cast (Isnull([NHCP DEPT 2],'') as varchar(100)) as [NHCP DEPT 2]
    , Cast (Isnull([NHCP DEPT 3],'') as varchar(100)) as [NHCP DEPT 3]
    , Cast (Isnull([NHCP DEPT 4],'') as varchar(100)) as [NHCP DEPT 4]
    , Cast (Isnull([NMCSD],'') as varchar(100)) as [NMCSD]
    , Cast (Isnull([NMCSD YES/ NO],'') as varchar(100)) as [NMCSD YES/ NO]
    , Cast (Isnull([NMCSD DEPT 1],'') as varchar(100)) as [NMCSD DEPT 1]
    , Cast (Isnull([NMCSD DEPT 2],'') as varchar(100)) as [NMCSD DEPT 2]
    , Cast (Isnull([NMCSD DEPT 3],'') as varchar(100)) as [NMCSD DEPT 3]
    , Cast (Isnull([NMCSD DEPT 4],'') as varchar(100)) as [NMCSD DEPT 4]
    , Cast (Isnull([OTHER],'') as varchar(100)) as [OTHER]
    , Cast (Isnull([OTHER YES/NO],'') as varchar(100)) as [OTHER YES/NO]
    , Cast (Isnull([OTHER TYPE],'') as varchar(100)) as [OTHER TYPE]
    , Cast (Isnull([OTHER DEPT 1],'') as varchar(100)) as [OTHER DEPT 1]
    , Cast (Isnull([OTHER DEPT 2],'') as varchar(100)) as [OTHER DEPT 2]
    , Cast (Isnull([OTHER DEPT 3],'') as varchar(100)) as [OTHER DEPT 3]
    , Cast (Isnull([OTHER DEPT 4],'') as varchar(100)) as [OTHER DEPT 4]
    , Cast (Isnull([OTHER 2],'') as varchar(100)) as [OTHER 2]
    , Cast (Isnull([OTHER 2 YES/NO],'') as varchar(100)) as [OTHER 2 YES/NO]
    , Cast (Isnull([OTHER 2 TYPE],'') as varchar(100)) as [OTHER 2 TYPE]
    , Cast (Isnull([OTHER 2 DEPT 1],'') as varchar(100)) as [OTHER 2 DEPT 1]
    , Cast (Isnull([OTHER 2 DEPT 2],'') as varchar(100)) as [OTHER 2 DEPT 2]
    , Cast (Isnull([OTHER 2 DEPT 3],'') as varchar(100)) as [OTHER 2 DEPT 3]
    , Cast (Isnull([OTHER 2 DEPT 4],'') as varchar(100)) as [OTHER 2 DEPT 4]
    , Cast (Isnull([OTHER 3],'') as varchar(100)) as [OTHER 3]
    , Cast (Isnull([WWBn],'') as varchar(100)) as [WWBn]
    , Cast (Isnull([CATEGORY],'') as varchar(100)) as [CATEGORY]
    , Cast (Isnull([BEN CAT],'') as varchar(100)) as [BEN CAT]
    , Cast (Isnull([RowId],'') as varchar(100)) as [RowId]
    , Cast (Isnull([LastUpdatedDate],'') as varchar(100)) as [LastUpdatedDate]
    , Cast (Isnull([UserName],'') as varchar(100)) as [UserName]
    , Cast (Isnull([DatabaseName],'') as varchar(100)) as [DatabaseName]
    From dbo.[Main Database]

    Unpivot Portion

    ;With Unpivotdata as(
    select RowNo,  colname=cast(colname as varchar(20))
    ,colvalue =convert(varchar(20), colvalue )
    from dbo.vw_CaseMgmt
    unpivot (colvalue for colname in (
    [LAST NAME],[FIRST NAME],[MI],[EDC],[BRANCH OF SERVICE],[RANK],[FULL SSN],[EAS],[CONTACT PHONE],[NHCP CM],[PRIMARY NHCP CM],[PATIENT CO-MANAGED],[DATE OF MEDEVAC],[MEDEVAC CATAGORY],[RESERVIST],[CURRENT STATUS],[DMIS],[COMMAND],[COMPANY],[MSC],[SOURCE],[PCM],[PCM CLINIC],[REFFERED BY],[DATE OPENED],[DATE CLOSED],[TYPE OF CASE],[COMPLEXITY],[ACUITY],[MECHANISM OF INJURY],[BLAST EXPOSURE],[TBI],[AMPUTATION],[PTSD],[DIAGNOSIS],[TREATMENT PLAN],[DISCHARGE DATE],[TIME MIN/MONTH],[BOARD STATUS],[BOARD EXPIRATION DATE],[DATE REFERRED TO VA REP],[NEURO/PSYC EVAL DATE],[FINAL DISPOSITION],[HOSPITAL CORPSMAN],[NHCP AUGMENT],[SPECIAL CONSIDERATIONS],[COMMENTS],[COMMENTS CONTINUED],[FMP],[MARRIED],[SPONSORS NAME],[SPONSORS LAST FOUR],[REFERRED TO],[C5 PT],[NHCP],[NHCP YES/ NO],[NHCP DEPT 1],[NHCP DEPT 2],[NHCP DEPT 3],[NHCP DEPT 4],[NMCSD],[NMCSD YES/ NO],[NMCSD DEPT 1],[NMCSD DEPT 2],[NMCSD DEPT 3],[NMCSD DEPT 4],[OTHER],[OTHER YES/NO],[OTHER TYPE],[OTHER DEPT 1],[OTHER DEPT 2],[OTHER DEPT 3],[OTHER DEPT 4],[OTHER 2],[OTHER 2 YES/NO],[OTHER 2 TYPE],[OTHER 2 DEPT 1],[OTHER 2 DEPT 2],[OTHER 2 DEPT 3],[OTHER 2 DEPT 4],[OTHER 3],[WWBn],[CATEGORY],[BEN CAT],[RowId],[LastUpdatedDate],[UserName],[DatabaseName]
    ))
    as unpvt)
    ,u as (select  v.databasename, v.[Last Name], v.[First Name], v.[NHCP CM], v.[Full SSN], a.RowNo, a.colname, a.colvalue 
    from dbo.vw_CaseMgmt v
    Left Join  Unpivotdata a on
    v.RowNo=A.RowNo )
    Select distinct  U.* from u 
    Order by u.[Last Name], u.[First Name], u.[Full SSN],  u.colname, u.[DatabaseName]

     

    Sample Data:
    DatabaseName Full SSNo Last Name First Name FMP MSC Category etc….
    Main 111-11-1111 Jones Betty A WA Misc abc
    DB1 111-11-1111 Jones Betty B WA

    Misc

    Desired Final Output ( to list only those columns with mismatches based against the "Main" DatbaseName:

    DatabaseName Full SSNo Last Name First Name ColumnName Column Value (Note)
    Main 111-11-1111 Jones Betty FMP A This is the Baseline
    DB1 111-11-1111 Jones Betty FMP B
    DB2 111-11-2222 Randolph Richard Rank Mr. This is a row which exists in with DB2 as Database Name but not have a corresponding value with Database Name= Main


    John




    • Edited by vsla Wednesday, July 24, 2013 8:41 PM
    Wednesday, July 24, 2013 4:45 PM

Answers

  • The following works - although maybe not the best way, but it does what I need it to do:

    ;With Unpivotdata as(
    select RowNo,  colname=cast(colname as varchar(20))
    ,colvalue =convert(varchar(20), colvalue )
    from dbo.vw_CaseMgmt
    unpivot (colvalue for colname in (
    [LAST NAME],[FIRST NAME],[MI],[EDC],[BRANCH OF SERVICE],[RANK],[FULL SSN],[EAS],[CONTACT PHONE],[NHCP CM],[PRIMARY NHCP CM],[PATIENT CO-MANAGED],[DATE OF MEDEVAC],[MEDEVAC CATAGORY],[RESERVIST],[CURRENT STATUS],[DMIS],[COMMAND],[COMPANY],[MSC],[SOURCE],[PCM],[PCM CLINIC],[REFFERED BY],[DATE OPENED],[DATE CLOSED],[TYPE OF CASE],[COMPLEXITY],[ACUITY],[MECHANISM OF INJURY],[BLAST EXPOSURE],[TBI],[AMPUTATION],[PTSD],[DIAGNOSIS],[TREATMENT PLAN],[DISCHARGE DATE],[TIME MIN/MONTH],[BOARD STATUS],[BOARD EXPIRATION DATE],[DATE REFERRED TO VA REP],[NEURO/PSYC EVAL DATE],[FINAL DISPOSITION],[HOSPITAL CORPSMAN],[NHCP AUGMENT],[SPECIAL CONSIDERATIONS],[COMMENTS],[COMMENTS CONTINUED],[FMP],[MARRIED],[SPONSORS NAME],[SPONSORS LAST FOUR],[REFERRED TO],[C5 PT],[NHCP],[NHCP YES/ NO],[NHCP DEPT 1],[NHCP DEPT 2],[NHCP DEPT 3],[NHCP DEPT 4],[NMCSD],[NMCSD YES/ NO],[NMCSD DEPT 1],[NMCSD DEPT 2],[NMCSD DEPT 3],[NMCSD DEPT 4],[OTHER],[OTHER YES/NO],[OTHER TYPE],[OTHER DEPT 1],[OTHER DEPT 2],[OTHER DEPT 3],[OTHER DEPT 4],[OTHER 2],[OTHER 2 YES/NO],[OTHER 2 TYPE],[OTHER 2 DEPT 1],[OTHER 2 DEPT 2],[OTHER 2 DEPT 3],[OTHER 2 DEPT 4],[OTHER 3],[WWBn],[CATEGORY],[BEN CAT],[RowId],[LastUpdatedDate],[UserName],[DatabaseName]
    ))
    as unpvt)
    ,u as (select  v.databasename, v.[Last Name], v.[First Name], v.[NHCP CM], v.[Full SSN], a.RowNo, a.colname, a.colvalue 
    from dbo.vw_CaseMgmt v
    Left Join  Unpivotdata a on
    v.RowNo=A.RowNo )
    , NM as (
    Select distinct  U.* from u 
    where databasename<>'Main'),
    m as (
    Select distinct  U.* from u 
    where databasename='Main')
    Select * from M 
    Inner Join NM on 
    NM.[Last Name]=M.[Last Name] and 
    NM.[First Name]=M.[First Name] and 
    NM.[FULL SSN]=M.[FULL SSN] and 
    NM.ColName=M.ColName
    Where 
    NM.ColValue<>M.ColValue and 
    M.colname<>'RowId' and
    M.colname<>'LastUpdatedDate' and 
    M.colname<>'DatabaseName'
    Order by  NM.[DatabaseName], M.[Last Name], M.[First Name], M.[Full SSN],  M.colname


    John

    Thursday, July 25, 2013 5:35 PM

All replies

  • to check column name diff, you need to join sys.columns and sys.objects with object_ID

    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 24, 2013 11:26 PM
  • Hello, Prajesh.

    Not looking for Column Name differences - looking for Column Differences (values).


    John

    Thursday, July 25, 2013 4:10 PM
  • to compare column value difference

    the best way is use UNION 

    if the column values in resultset 1 is same in resultset 2 UNION will result the no of unique no of records with values in either part.

    Also you can use EXCEPT 

    that will show you is there any difference in both resultset value

    for both the above trick you need to make the CTE same for both resultset to compare


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 4:17 PM
  • I had an article a while back on a similar problem - may be you can use some of its ideas:

    http://www.kodyaz.com/articles/sql-string-manipulation-to-find-difference.aspx

    (Took me some time to find it -in my gmail)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 25, 2013 4:23 PM
    Moderator
  • The following works - although maybe not the best way, but it does what I need it to do:

    ;With Unpivotdata as(
    select RowNo,  colname=cast(colname as varchar(20))
    ,colvalue =convert(varchar(20), colvalue )
    from dbo.vw_CaseMgmt
    unpivot (colvalue for colname in (
    [LAST NAME],[FIRST NAME],[MI],[EDC],[BRANCH OF SERVICE],[RANK],[FULL SSN],[EAS],[CONTACT PHONE],[NHCP CM],[PRIMARY NHCP CM],[PATIENT CO-MANAGED],[DATE OF MEDEVAC],[MEDEVAC CATAGORY],[RESERVIST],[CURRENT STATUS],[DMIS],[COMMAND],[COMPANY],[MSC],[SOURCE],[PCM],[PCM CLINIC],[REFFERED BY],[DATE OPENED],[DATE CLOSED],[TYPE OF CASE],[COMPLEXITY],[ACUITY],[MECHANISM OF INJURY],[BLAST EXPOSURE],[TBI],[AMPUTATION],[PTSD],[DIAGNOSIS],[TREATMENT PLAN],[DISCHARGE DATE],[TIME MIN/MONTH],[BOARD STATUS],[BOARD EXPIRATION DATE],[DATE REFERRED TO VA REP],[NEURO/PSYC EVAL DATE],[FINAL DISPOSITION],[HOSPITAL CORPSMAN],[NHCP AUGMENT],[SPECIAL CONSIDERATIONS],[COMMENTS],[COMMENTS CONTINUED],[FMP],[MARRIED],[SPONSORS NAME],[SPONSORS LAST FOUR],[REFERRED TO],[C5 PT],[NHCP],[NHCP YES/ NO],[NHCP DEPT 1],[NHCP DEPT 2],[NHCP DEPT 3],[NHCP DEPT 4],[NMCSD],[NMCSD YES/ NO],[NMCSD DEPT 1],[NMCSD DEPT 2],[NMCSD DEPT 3],[NMCSD DEPT 4],[OTHER],[OTHER YES/NO],[OTHER TYPE],[OTHER DEPT 1],[OTHER DEPT 2],[OTHER DEPT 3],[OTHER DEPT 4],[OTHER 2],[OTHER 2 YES/NO],[OTHER 2 TYPE],[OTHER 2 DEPT 1],[OTHER 2 DEPT 2],[OTHER 2 DEPT 3],[OTHER 2 DEPT 4],[OTHER 3],[WWBn],[CATEGORY],[BEN CAT],[RowId],[LastUpdatedDate],[UserName],[DatabaseName]
    ))
    as unpvt)
    ,u as (select  v.databasename, v.[Last Name], v.[First Name], v.[NHCP CM], v.[Full SSN], a.RowNo, a.colname, a.colvalue 
    from dbo.vw_CaseMgmt v
    Left Join  Unpivotdata a on
    v.RowNo=A.RowNo )
    , NM as (
    Select distinct  U.* from u 
    where databasename<>'Main'),
    m as (
    Select distinct  U.* from u 
    where databasename='Main')
    Select * from M 
    Inner Join NM on 
    NM.[Last Name]=M.[Last Name] and 
    NM.[First Name]=M.[First Name] and 
    NM.[FULL SSN]=M.[FULL SSN] and 
    NM.ColName=M.ColName
    Where 
    NM.ColValue<>M.ColValue and 
    M.colname<>'RowId' and
    M.colname<>'LastUpdatedDate' and 
    M.colname<>'DatabaseName'
    Order by  NM.[DatabaseName], M.[Last Name], M.[First Name], M.[Full SSN],  M.colname


    John

    Thursday, July 25, 2013 5:35 PM