November 16, 2012 at 11:43 am
Folks:
I need help with a query to compare records from Master table (#tblSMaster) to records in Detais table (#tblSDetails). The #tblSMaster stores information about each subfoldername and which group has what kind of access. The #tblSDetails stores information about Main Folder, Client Folder and the Subfolder name along with which group has what kind of access. Both the output for these table are coming from different sources so I would like to compare each Subfolder from #tblSMaster in the #tblSDetails for mismatch. It should also find if any Subfolder is missing in the #tblSDetails table.
CREATE TABLE #tblSDetails
(FolderName varchar(200),ClientFolder varchar(200),SubFolderName varchar(200),SGroup varchar(200),Access varchar(50))
insert into #tblSDetails values ('Invoice','ABC','Territory','Admin','Read')
insert into #tblSDetails values ('Invoice','ABC','Territory','IT','Modify')
insert into #tblSDetails values ('Invoice_Archive','BOA','Territory','Admin','Read')
insert into #tblSDetails values ('Invoice_Archive','BOA','Territory','IT','Modify')
insert into #tblSDetails values ('Invoice','PQR','Territory','Admin','Read')
insert into #tblSDetails values ('Invoice','PQR','Territory','IT','Modify')
insert into #tblSDetails values ('Invoice_Archive','CSS','Territory','Admin','Read')
insert into #tblSDetails values ('Invoice_Archive','CSS','Territory','IT','Modify')
insert into #tblSDetails values ('Invoice','ABC','Documents','Legal','Modify')
insert into #tblSDetails values ('Invoice','ABC','Documents','IT','Modify')
insert into #tblSDetails values ('Invoice_Archive','BOA','Documents','Legal','Modify')
insert into #tblSDetails values ('Invoice_Archive','BOA','Documents','IT','Modify')
insert into #tblSDetails values ('Invoice','PQR','Documents','Legal','Modify')
insert into #tblSDetails values ('Invoice','PQR','Documents','IT','Modify')
insert into #tblSDetails values ('Invoice_Archive','CSS','Documents','Legal','Modify')
insert into #tblSDetails values ('Invoice_Archive','CSS','Documents','IT','Modify')
CREATE TABLE #tblSMaster
(SubFolderName varchar(200),SGroup varchar(200), Access varchar(50))
insert into #tblSMaster values('Territory','Admin','Read')
insert into #tblSMaster values('Territory','IT','Modify')
insert into #tblSMaster values('Documents','Legal','Modify')
insert into #tblSMaster values('Documents','IT','Modify')
Thanks !
November 16, 2012 at 12:00 pm
Great job posting ddl and sample data!!! Unfortunately your question is very unclear. What does a mismatch mean? Is it possible to normalize this stuff and add something that can be a key?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2012 at 12:12 pm
Mismatch meaning sometimes it is possible that in the Details table (#tblSDetails) the Subfolder might not exists or it maybe also possible that the data under Access column for a Subfolder might be changed from Read to Modify and vice versa. So that data has be checked against the Master table (#tblSMaster) which has the accurate data.
November 16, 2012 at 1:20 pm
sqldba20 (11/16/2012)
Mismatch meaning sometimes it is possible that in the Details table (#tblSDetails) the Subfolder might not exists or it maybe also possible that the data under Access column for a Subfolder might be changed from Read to Modify and vice versa. So that data has be checked against the Master table (#tblSMaster) which has the accurate data.
Can you add some data to your sample data to illustrate what you mean and then either toss together a temp table or explain what you want as output? I think I know what you are looking for but I am not totally sure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 16, 2012 at 2:01 pm
Let's say somebody adds a new record in the Details table (#tblSDetails) with new Sub-Folder
insert into #tblSDetails values ('Invoice_Archive','CSS','Location','Action','Read')
OR
Updates the Access column from Read to Modify where FolderName = 'Invoice' and SubFolderName = 'Territory'
UPDATE #tblSDetails
SET Access = 'Modify'
where FolderName ='Invoice'
and SubFolderName = 'Territory'
and SGroup ='Admin'
Then in that case When I run a SQL / SP to match the records I should get the output in the attachment. It is also possible that somebody might update/insert record into the Master table (#tblSMaster) and it should be able to show me that through SQL / SP.
Thanks !
November 16, 2012 at 2:15 pm
sqldba20 (11/16/2012)
Let's say somebody adds a new record in the Details table (#tblSDetails) with new Sub-Folder
insert into #tblSDetails values ('Invoice_Archive','CSS','Location','Action','Read')OR
Updates the Access column from Read to Modify where FolderName = 'Invoice' and SubFolderName = 'Territory'
UPDATE #tblSDetailsSET Access = 'Modify'
where FolderName ='Invoice'
and SubFolderName = 'Territory'
and SGroup ='Admin'
Then in that case When I run a SQL / SP to match the records I should get the output in the attachment. It is also possible that somebody might update/insert record into the Master table (#tblSMaster) and it should be able to show me that through SQL / SP.
Thanks !
This works at least for the examples given above.
select *
from #tblSDetails d
left join #tblSMaster m on d.SubFolderName = m.SubFolderName and d.SGroup = m.SGroup and d.Access = m.Access
where m.Access is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply