SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Compare data within a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldba20
Posting Yak Master

183 Posts

Posted - 12/10/2012 :  09:34:12  Show Profile  Reply with Quote
Folks:

I need help with SQL Query:

CREATE TABLE #tblDetails
( Account varchar(50),
  SubFolder varchar(100),
  GrpAccess varchar(50),
  Access varchar(20))


insert into #tblDetails values ('BOA','Location','MktAccess','Read')
insert into #tblDetails values ('BOA','Location','LegalAccess','Read')
insert into #tblDetails values ('BOA','Correspondence','AdminAccess','Read')
insert into #tblDetails values ('BOA','Correspondence','MktAccess','Modify')

insert into #tblDetails values ('CAPT','Location','MktAccess','Read')
insert into #tblDetails values ('CAPT','Location','LegalAccess','Read')
insert into #tblDetails values ('CAPT','Correspondence','AdminAccess','Read')
insert into #tblDetails values ('CAPT','Correspondence','MktAccess','Modify')

insert into #tblDetails values ('ALPH','Location','MktAccess','Read')
insert into #tblDetails values ('ALPH','Location','LegalAccess','Read')
insert into #tblDetails values ('ALPH','Correspondence','AdminAccess','Read')
insert into #tblDetails values ('ALPH','Correspondence','MktAccess','Modify')


This table contains our Folder security data for each Account. The sub folder under the Account should be same and the security for Sub folders should be same. I would like to find if there is a difference.

IF somebody adds a record in this table or modifies the table only for one Account then it should display the account records where the new inserted / record is not matching.

Example:
insert into #tblDetails values ('ALPH','Auditor','MktAccess','Modify')


OR

UPDATE #tblDetails 
SET Access = 'Modify'
WHERE Account = 'CAPT' 
and SubFolder = 'Correspondence' 
and GrpAccess = 'AdminAccess'


MisMatch Report should display this for which account the insert / update was NOT executed (Missing entries)

OUTPUT:

'BOA','Auditor','MktAccess','Modify'
'CAPT','Auditor','MktAccess','Modify'
'BOA','Correspondence','AdminAccess','Modify'
'ALPH','Correspondence','AdminAccess','Modify'


Thanks !
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000