question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked AvinashMore-7502 answered

summarising counts on a table

Hi,

I have a table structure like below.

It illustrates match information based on assets

97574-image.png


Users table

97489-image.png


I am trying to write a query that shows all assets with a column for the following.

Where s1_match, s2_match or s3_match is populated with the following users, user2, user3,user4. The ordering doesnt matter, the column should only display true or false if all those columns are populated with any of those 3 users.

The query should also for for every asset where any of the following columns has any other user assigned to it apart from user1, user2, user 3, user4. In the example, asset7 and asset11 will be flagged up.

Thanks in advance.


sql-server-transact-sql
image.png (35.4 KiB)
image.png (4.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MrFlinstone-1451,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @MrFlinstone-1451,

Thank you so much for posting here in Microsoft Q&A.

Please refer below:

  select SRN
  ,case when s1_match in (SELECT [user] FROM Users where [type]='tertiary')
          and s2_match in (SELECT [user] FROM Users where [type]='tertiary')
          and s3_match in (SELECT [user] FROM Users where [type]='tertiary')
          and s1_match <> s2_match 
          and s2_match <> s3_match 
          and s3_match <> s1_match 
 then 1 else 0 end  matchresult
  from assets 

Output:

 SRN matchresult
 Asset1 1
 Asset2 0
 Asset3 1
 Asset4 1
 Asset5 0
 Asset6 0
 Asset7 0
 Asset8 0
 Asset9 1
 Asset10 1
 Asset11 0

In addition, in case you also would like to match the p1_match with user1 whose type is primary, you could refer below:

  select SRN
  ,case when s1_match in (SELECT [user] FROM Users where [type]='tertiary')
          and s2_match in (SELECT [user] FROM Users where [type]='tertiary')
          and s3_match in (SELECT [user] FROM Users where [type]='tertiary')
          and s1_match <> s2_match 
          and s2_match <> s3_match 
          and s3_match <> s1_match 
          and p1_match =(SELECT [user] FROM Users where [type]='Primary') 
          then 1 else 0 end  matchresult
  from assets 

Output:

 SRN matchresult
 Asset1 1
 Asset2 0
 Asset3 0
 Asset4 0
 Asset5 0
 Asset6 0
 Asset7 0
 Asset8 0
 Asset9 1
 Asset10 0
 Asset11 0

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

Try this:

 -- Query 1
 SELECT CASE WHEN t.SRN IS NULL THEN 0 ELSE 1 END AS True_Or_False
 FROM Assets AS a
 LEFT JOIN (
  SELECT *
  FROM Assets
  WHERE s1_match IN ('user2', 'user3', 'user4')
    AND s2_match IN ('user2', 'user3', 'user4')
    AND s3_match IN ('user2', 'user3', 'user4')
    AND s1_match <> s2_match 
    AND s2_match <> s3_match 
    AND s3_match <> s1_match
 ) AS t ON t.SRN = a.SRN;
    
 -- Query 2
 SELECT *
 FROM Assets
 WHERE s1_match = 'unmatched' OR s1_match IN (SELECT [user] FROM Users)
   AND s2_match = 'unmatched' OR s2_match IN (SELECT [user] FROM Users) 
   AND s3_match = 'unmatched' OR s3_match IN (SELECT [user] FROM Users)
   AND s4_match = 'unmatched' OR s4_match IN (SELECT [user] FROM Users)
 );
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AvinashMore-7502 avatar image
0 Votes"
AvinashMore-7502 answered

Try this solution:

 -- Will return 1 if any of the user in search criteria is assigned
 -- Will return 0 if no user from search criteria is assigned 
 -- will return -1 if any other user not is search criteria is assigned
    
 ;WITH SC as  -- Search Criteria
 (
     SELECT 1 as Result, 'user2' as [user]
     UNION ALL
     SELECT 1 as Result, 'user3' as [user]
     UNION ALL
     SELECT 1 as Result, 'user4' as [user]
     UNION ALL
     SELECT 0 as Result, 'unmatched' as [user]
 ) , MD AS  -- Match data
 (
     SELECT SRN
             ,ISNULL(M1.Result,-1) as Result1 
             ,ISNULL(M2.Result,-1) as Result2
             ,ISNULL(M3.Result,-1) as Result3
             ,ISNULL(M4.Result,-1) as Result4
     FROM Assets A
     LEFT OUTER JOIN SC AS M1
     ON A.S1_match = M1.[user]
     LEFT OUTER JOIN SC AS M2
     ON A.S2_match = M2.[user]
     LEFT OUTER JOIN SC AS M3
     ON A.S3_match = M3.[user]
     LEFT OUTER JOIN SC AS M4
     ON A.S4_match = M4.[user]
 )
 SELECT    SRN
         ,  Result1 |    Result2    | Result3 |    Result4 as FinalResult
 FROM MD


Regards,
Avinash

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.