CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Avi
Avi

Reputation: 1845

How to find the values which are between column values (value should be looked from only one column)

Table 1 

Dept ID     Name    EmpID   Attended    StartDate       Enddate 
1           Rob     100     Y           2017/01/01      2017/01/08
1           Max     101     N           2017/01/10      2017/01/16
1           Mary    102     N           2017/01/10      2017/01/20
2           Jay     103     N           2017/02/05      2017/02/08
2           Jon     104     Y           2017/02/10      2017/02/20 
2           Jim     105     N           2017/03/01      2017/03/20
2           Jack    106     N           2017/03/25      2017/03/29 
2           Sam     107     Y           2017/04/01      2017/04/20
2           Sim     108     N           2017/05/01      2017/05/15  
2           Abs     109     Y           2017/06/15      2017/06/20    
2           Name    110     N           2017/06/23      2017/06/26 
3           Phil    111     Y           2017/12/01      2017/12/03
3           Pam     112     N           2017/12/05      2017/12/06 
3           Pen     113     Y           2017/12/07      2017/12/10

Expected Output 

Dept ID     Name    EmpID   Attended    StartDate       Enddate 

2           Jon     104     Y           2017/02/10      2017/02/20 
2           Jim     105     N           2017/03/01      2017/03/20
2           Jack    106     N           2017/03/25      2017/03/29 
2           Sam     107     Y           2017/04/01      2017/04/20
2           Sim     108     N           2017/05/01      2017/05/15  
3           Phil    111     Y           2017/12/01      2017/12/03
3           Pam     112     N           2017/12/05      2017/12/06

So scenario is I need to look at attended column based on Dept ID, and take only those records where attended = Y exists more than once. After that, I need to look at the Most recent Attended = Y and the initial Attended = Y and get all the values between that except Most recent Attended = Y but includes Initial Attended. Emp ID is the order they receive per dept ID and the way record come in.

For ID 1 there was not two Y so we don't need it, for 2 Abs had most recent Attended = 'Y' and Jon with the Initial Attended = 'Y', so I need to include Jon's records along with other records before Abs records.

Upvotes: 1

Views: 41

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Something like this should help

;WITH cte
     AS (SELECT DeptID,
                MaxStartDate = Max(StartDate),
                MinStartDate = Min(StartDate),
                YCount = Count(1)
         FROM   Yourtable
         WHERE  Attended = 'Y'
         GROUP  BY DeptID)
SELECT t.*
FROM   Yourtable t
       INNER JOIN cte c
               ON t.DeptID = c.DeptID
                  AND t.StartDate >= MinStartDate
                  AND t.StartDate < c.MaxStartDate
WHERE  YCount > 1

Upvotes: 2

Related Questions