CODEWITHSUNDEEP
sqlsql-servert-sql
user2040021
user2040021

Reputation: 309

Get the Date since active status for record in sql in different services

I am trying to find out date from which that particular client is active regardless of different service provided by that client. I am able to get the latest state but that doesn't help me with what I am looking for!!

Can you guys please share your thought or give guidance on on how to do this?

Here is my table.enter image description here

I am expecting following result

enter image description here

Here is what I am getting with following query

enter image description here

here is the query, it's not meant for above tables but idea is the same to get the last row of each name for each different services. Here is the query.

select FIRST,LAST,GENDER
from    Employees e
join    (
        select  row_number() over (
                    partition by emp_id
                    order by startdate desc) as rn
        ,       EMP_ID
        ,       STATUS
        from    EMPSTATES
        where   STARTDATE <= '2016-08-19'
        ) h
on      e.EMP_ID = h.EMP_ID
        and h.rn = 1 -- Only last row
where   h.STATUS = 'A'
order by FIRST,LAST

Upvotes: 2

Views: 213

Answers (3)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below query..

        SELECT  e.FIRSTNAME, 
                        e.LASTNAME, 
                        s.[STATUS], 
                        MIN(s.STARTDATE) Since
        FROM Employees e 
            JOIN EMPSTATS s 
                on e.EMP_ID=s.EMP_ID 
        WHERE s.STARTDATE <= '2016-09-06' 
                       AND [Status] ='A'
        GROUP BY e.FIRSTNAME, 
                            e.LASTNAME, 
                            s.[STATUS]

Upvotes: 1

gofr1
gofr1

Reputation: 15977

You can use ROW_NUMBER() and CTE:

;with cte as(
    SELECT Name, [status], [since], ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [since] asc) as rn
    FROM Employees
    WHERE --add your where statement here
)

Select Name, [status], [since]
From cte
Where Rn = 1

Edit, based on comments:

with cte as( 
SELECT e.FIRSTNAME, 
       e.LASTNAME, 
       s.[STATUS], 
       s.STARTDATE, 
       ROW_NUMBER() OVER (PARTITION BY e.emp_ID ORDER BY s.startdate asc) as rn
FROM Employees e 
join EMPSTATES s 
on e.EMP_ID=s.EMP_ID 
WHERE s.STARTDATE <= '2016-09-06' 
AND [Status] ='A'
 ) 

Select FIRSTNAME, 
       LASTNAME, 
       [STATUS], 
       STARTDATE
From cte 
Where Rn = 1

Or with top 1 with ties:

SELECT TOP 1 WITH TIES 
       e.FIRSTNAME, 
       e.LASTNAME, 
       s.[STATUS], 
       s.STARTDATE
FROM Employees e 
join EMPSTATES s 
on e.EMP_ID=s.EMP_ID 
WHERE s.STARTDATE <= '2016-09-06' 
AND [Status] ='A'
ORDER BY ROW_NUMBER() OVER (PARTITION BY e.emp_ID ORDER BY s.startdate asc)

Upvotes: 1

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Using ROW_NUMBER() function in CTE, you can achieve it

;WITH cteEmployeeStatus
(
    SELECT  ROW_NUMBER() OVER (
                PARTITION BY EmpName
                ORDER BY startdate) as rn
        ,EMP_ID
        ,StartDate
        ,EmpName
        ,STATUS
        FROM 
            EMPSTATES
        WHERE  
            STARTDATE <= '2016-08-19'
            AND  Status = 'Active'
)
SELECT EMP_ID, EmpName, StartDate AS Since FROM cteEmployeeStatus
WHERE rn = 1

Upvotes: 1

Related Questions