Analytical and reporting query

Question

I have a table structure illustrated below.

The owner information can be seen below.

Table structure below.

create table #asset_ownership  
(  
assetid int,  
primary_owner int,  
secondary1_owner int,  
secondary2_owner int,  
secondary3_owner int  
)  
  

create table #owners
(owner_id int,
owner_fullname varchar(100),
owner_status char(5),
[status] varchar(20),
ranking tinyint
)

insert into #asset_ownership  
select 1,2222,3333,4444,5555  
union all  
select 2,7777,1111,4444,2222  
union all  
select 3,8888,1111,3333,4444  
union all  
select 4,1111,2222,3333,4444  
union all  
select 5,9988,8855,5589,1134  
union all  
select 6,7722,2256,1124,1311  
  
  
  

insert into #owners
select 1111,'Tom','P1','primary',1
union all
select 2222,'Alice','S1','secondary',1
union all
select 3333,'John', 'S2','secondary',2
union all
select 4444,'Ian', 'S3','secondary',3

P1 = Means primary owner and is higher from an hierarchy perspective
S1 = Secondary 1, lower than primary 1 but higher than secondary 2
S2 = Secondary 2 lower than secondary 1 but higher than secondary 3
S3 = Secondary 3 lower than secondary 2 and secondary 1

I am trying to put together queries that will show the below.

For every asset, how many of the owners match the defined owners list, where there is a match, where is the match based on primary owner, secondary1_owner or secondary2_owner or secondary3_owner , and a count for every match ?

I only want to match primary owner with the primary defined owner but any secondary owner (S1, S2, S3) with any of the secondary owners.
A count and summary of every match per asset id.

A query where there is no match with any of the defined owners.
A query to highlight matches and what they are, I guess one from the above will show this.

Thanks in advance.

Answer 1

Here is a query for your latest requirement:

WITH secondowners AS (
   SELECT ao.assetid, V.secondowner, V.ranking
   FROM   #asset_ownership ao
   CROSS  APPLY (VALUES(1, ao.secondary1_owner), (2, ao.secondary2_owner), (3, ao.secondary3_owner)) AS V(ranking, secondowner)
), secondmatches AS (
   SELECT so.assetid, so.secondowner,
          matchno = row_number() OVER(PARTITION BY so.assetid ORDER BY so.ranking)
   FROM   secondowners so
   JOIN   #owners o ON so.secondowner = o.owner_id
   WHERE  o.status = 'secondary'
), pivotseconds AS (
   SELECT assetid, 
          MIN(CASE matchno WHEN 1 THEN secondowner END) AS sec1_match,
          MIN(CASE matchno WHEN 2 THEN secondowner END) AS sec2_match,
          MIN(CASE matchno WHEN 3 THEN secondowner END) AS sec3_match,
          COUNT(*) AS secs_count
   FROM   secondmatches
   GROUP  BY assetid
)
SELECT ao.assetid, IIF(o.status = 'primary', o.owner_id, NULL) AS primary_owner_match, 
       ps.sec1_match, ps.sec2_match, ps.sec3_match,
       IIF(o.status = 'secondary', o.owner_id, NULL) AS sec_match_prim,
       IIF(o.status = 'primary', 1, 0), isnull(ps.secs_count, 0)
FROM   #asset_ownership ao
LEFT   JOIN pivotseconds ps ON ao.assetid = ps.assetid
LEFT   JOIN #owners o ON ao.primary_owner = o.owner_id
ORDER BY ao.assetid

If you have larger amounts of data, it may be better to materialise one of the CTEs to a temp table.

Answer 2

Hi,

Thanks for the answer. I was wondering how I could get a count like below. It can be on a seperate select statement, when I tried it, its almost like I need to declare a variable and increment at each match,

Answer 3

Hi @MrFlinstone ，

Please refer below:

;WITH CTE AS(  
SELECT a.assetid,  
CASE WHEN EXISTS (SELECT 1 FROM   #owners o  
	WHERE  o.owner_id = a.primary_owner AND  o.owner_status = 'P1')   
	THEN cast(a.primary_owner as char)  
	ELSE 'non_matched' END AS [Primary_owner_match],  
CASE WHEN EXISTS (SELECT 1 FROM   #owners o  
	WHERE  o.owner_id IN (a.secondary1_owner,a.secondary2_owner,a.secondary3_owner)  
	AND  o.owner_status LIKE 'S[1-3]')  
	AND  a.secondary1_owner in (select owner_id from #owners)  
	AND NOT EXISTS (SELECT 1 FROM   #owners o  
	WHERE o.owner_id =a.secondary1_owner AND  o.owner_status = 'P1')  
	THEN cast(a.secondary1_owner as char)  
	ELSE 'non_matched'END AS [Secondary_owner_match],  
CASE WHEN EXISTS (SELECT 1 FROM   #owners o  
	WHERE  o.owner_id IN (a.secondary1_owner,a.secondary2_owner,a.secondary3_owner)  
	AND  o.owner_status LIKE 'S[1-3]')  
	AND  a.secondary2_owner in (select owner_id from #owners)  
	THEN cast(a.secondary2_owner as char)  
	ELSE 'non_matched' END AS [Secondary_owner2_match],  
CASE WHEN EXISTS (SELECT 1 FROM   #owners o  
	WHERE  o.owner_id IN (a.secondary1_owner,a.secondary2_owner,a.secondary3_owner)  
	AND  o.owner_status LIKE 'S[1-3]')  
	AND  a.secondary3_owner in (select owner_id from #owners)  
	THEN cast(a.secondary3_owner as char)  
	ELSE 'non_matched' END AS [Secondary_owner3_match],  
CASE WHEN EXISTS (SELECT 1 FROM   #owners o  
	WHERE o.owner_id =a.secondary1_owner AND  o.owner_status = 'P1')   
	THEN cast(a.secondary1_owner as char)  
	ELSE 'non_matched' END AS [Secondary_owner_matching_preferred_primary]  
FROM   #asset_ownership a)  
SELECT  Primary_owner_match,  
case when Secondary_owner_matching_preferred_primary<>'non_matched' then Secondary_owner2_match else Secondary_owner_match end  Secondary_owner_match,  
case when Secondary_owner_matching_preferred_primary<>'non_matched' then Secondary_owner3_match else Secondary_owner2_match end  Secondary_owner2_match,  
case when Secondary_owner_matching_preferred_primary<>'non_matched' then 'non_matched' else Secondary_owner3_match end  Secondary_owner3_match,  
Secondary_owner_matching_preferred_primary  
FROM CTE  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Apologies, I have now added some extra columns to the owner table above. As I reckon this might help. Please see expected results below.

Answer 5

Since you did not provide expected output, I am not fully certain of what you are looking for. It seems that you are looking for more than one query, but exactly how many are not clear to me.

There is a query which shows which have assets we have matched owners.

SELECT ao.*, CASE WHEN EXISTS (SELECT *
                               FROM   #owners o
                               WHERE  o.owner_id = ao.primary_owner
                                 AND  o.owner_status = 'P1')
                       THEN 'X'
                       ELSE ''
             END AS [Match Primary],
             CASE WHEN EXISTS (SELECT *
                               FROM   #owners o
                               WHERE  o.owner_id IN (ao.secondary1_owner, 
                                                     ao.secondary2_owner, 
                                                     ao.secondary3_owner)
                                 AND  o.owner_status LIKE 'S[1-3]')
                       THEN 'X'
                       ELSE ''
             END AS [Match Secondary]
FROM   #asset_ownership ao