Home How do I get values based on evaluation of only one column values
Reply: 0

How do I get values based on evaluation of only one column values

Avi
1#
Avi Published in 2018-01-12 02:22:45Z

Table 1

   Dept ID     Name    EmpID   Attended      Sub Dept   

     1           Rob     100     N           HR
     1           Max     101     N           Mech
     1           Mary    102     Y           IT
     2           Jay     103     N           Mech
     2           Jon     104     Y           IT 
     2           Jim     105     N           IT
     2           Jack    106     N           HR 
     2           Sam     107     Y           IT
     2           Sim     108     N           Eng  
     2           Abs     109     Y           IT    
     2           Name    110     N           IT 
     3           Phil    111     Y           IT
     3           Pam     112     N           IT 
     3           Pen     113     Y           IT

Expected Output

    Dept ID     Name    EmpID   Attended      Sub Dept   

     1           Rob     100     N           HR
     1           Max     101     N           Mech
     2           Jay     103     N           Mech
     2           Jim     105     N           IT
     2           Jack    106     N           HR 
     2           Sam     107     Y           IT
     2           Sim     108     N           Eng  
     3           Pam     112     N           IT 

IT sub dept within a Dept ID is used to compare and get the rows with attended flag. So the scenario is I need to look at attended column based on Dept ID, and take only those records where attended = Y. Note, Attended = Y will only exist for IT but not all IT will have Attended = Y. After that, I need to look Attended = Y or N for IT and then get the records. Emp ID is the order they receive per dept ID and the way record come in.

For ID 1 there was one record = Y so we should get 2 records before that. For ID = 2, Emp ID = 104 had first = Y , we need to get 103 emp ID, then we evaluate other rows , as we can see 104 and 105 both have IT (but only 104 had Y, so comparing to the next row 105 we don't need 104, and start our evaluation from 105 to another ID having IT. 107 has IT and attended = Y, we need to get 105 and 106. Then we evaluate 107 with another id with ID, which is 109, so we get 107 and 108. Then for 109 we need to look at another row with IT, as for the next row we don't have IT and attended = Y, we don't need to consider 109 and start at 110 if any rows are added for the Dept ID. Same logic applies for DeptID = 3,

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302624 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO