question

sha avatar image
sha asked

SQL query assistance

Hi,

I need assistance writing a query for the following scenario:

userid (not unique, duplicate ids)

trans_lvl - values could be 5 or 6 or (other)

The request is to find data for these three scenarios

1. distinct userids with 100% of its assigned trans_lvls = 5 (all of these userids are assigned only 5)

2. distinct userids with 100% of its assigned trans_lvls = 6 (all of these userids are assigned only 6)

3. distinct userids with 100% of its assigned trans_lvls having combination of both 5 and 6 (and only 5 and 6)

4. distinct userids with 100% of its assigned trans_lvls = anything else/any other combo

Data example:

userid trans_lvl

1 5

1 6
2 2
2 5
3 5
3 5
4 6

result would be:

userid1 under scenario 3

userid 2 under scenario 4

userid 3 under scenario 1

userid 4 under scenario 2

I've attached a script to recreate the data example (not tested - sorry, I don't have SS installed at home).

Thank you in advance.

querysql query
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
erlokeshsharma08 avatar image
erlokeshsharma08 answered

Try this

-- SAMPLE DATA
Declare  @tb table  (userid INT, trans_lvl INT)
INSERT INTO @tb VALUES (1,5),(1,6),(2,2),(2,5),(3,5),(3,5),(4,6)
-- COMBINE THE DISTINCT USEIDS INTO SINGLE ROW USING FOR XML
SELECT userid, trans_lvl =  (SELECT STUFF((select distinct ',' + CAST (trans_lvl AS VARCHAR(100)) from @tb
          where userid = t.userid
           FOR XML PATH('')),1,1,'')) 
INTO #tb_final
FROM @tb t GROUP BY userid
-- FINAL RESULT
SELECT userid, CASE WHEN trans_lvl = '5' THEN 1 
                 WHEN trans_lvl = '6' THEN 2
        WHEN trans_lvl = '5,6' THEN 3
     ELSE 4 END AS Scenario
     FROM #tb_final
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sha avatar image
sha answered

Sorry for the.delay. Thank you very much - this did the trick!

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI avatar image WRBI commented ·

Morning @sha,

If the answer resolved your issue please consider marking it as answered.

Regards,

WRBI

0 Likes 0 ·
sha avatar image
sha answered

Hi @WRBI,

I don't see where to mark as answered. Can you please advise?

TIA...

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.