need to check multiple condition and retrieve records in sql server

See more:

SQL-Server

SQL-Server-2012

I have two tables. emp:

CREATE TABLE dbo.emp (
    empid INT NULL,
    deptid INT NULL,
    doj DATE NULL,
    loc VARCHAR(50) NULL,
    [status] INT NULL,
    [guid] INT NULL,
    locid INT
)
INSERT INTO emp (empid, deptid, doj, loc, [status], [guid], locid)
VALUES
    (1, 10, '2013-09-25', 'hyd', 5, 10, 4),
    (1, 10, '2014-03-25', 'che', 5, 11, 1),
    (1, 10, '2014-04-09', 'pune', 5, 12, 2),
    (1, 10, '2015-01-22', 'pune', 5, 13, 2),
    (2, 20, '2015-12-13', 'beng', 5, 14, 3),
    (2, 20, '2014-12-12', 'chen', 5, 15, 2),
    (2, 20, '2010-10-15', 'beng', 5, 16, 3),
    (2, 20, '2010-10-15', 'beng', 4, 17, 3),
    (3, 30, '2011-11-15', 'beng', 5, 18, 3),
    (4, 40, '2010-11-15', 'chen', 5, 19, 1),
    (4, 40, '2010-11-15', 'beng', 5, 20, 3),
    (4, 40, '2009-11-15', 'beng', 5, 21, 3),
    (5, 50, '2010-12-14', 'beng', 5, 22, 3)

empref:

CREATE TABLE dbo.empref (
    empid INT NULL,
    deptid INT NULL,
    startdate DATE NULL,
    [status] INT NULL,
    [guid] INT NULL,
    checkid INT NULL
)
INSERT INTO empref (empid, deptid, startdate, [status], [guid], checkid)
VALUES
    (1, 10, '2013-10-02', 2, 1, 1),
    (1, 10, '2014-04-09', 2, 2, 2),
    (1, 10, '2015-12-09', 1, 3, 3),
    (1, 10, '2015-01-30', 2, 4, 4),
    (2, 20, '2015-12-14', 2, 2, 5),
    (2, 20, '2015-12-15', 2, 3, 6),
    (3, 30, '2011-11-15', 2, 3, 7),
    (3, 30, '2011-11-16', 2, 5, 8),
    (4, 40, '2010-11-17', 2, 6, 9),
    (5, 50, '2010-11-15', 2, 7, 9),
    (5, 50, '2010-11-15', 2, 8, 10)

Both tables have common columns Empid + deptid We need to consider emp table status=5 related records compare with empref table status=2 related records and emp table doj <= startdate --empref table and days difference between less than or equal to 30 days.Remain status values 4 or 1 ignore in the process time.

if we found multiple records in emp table then we consider min(doj) and min(locid) priority and corresponding guid from empref table and record consider as update in the filter. if we found multiple records in empref table then we consider min(startdate)and min(checkid_) priority and corresponding guid from empref table and record consider as update in the filter.

if above conditions not satisfied with few records in emp table then records need to consider insert in the filter.

based on above tables I want output like below

Empid| Deptid | loc | Status | Filter | Doj |guid
1 | 10 | hyd | 5 | Update | 2013-09-25|1
1 | 10 | che | 5 | insert | 2014-03-25|2 ------min(startdate) corresponding record
1 | 10 | pune| 5 | update | 2014-04-09|11 --------mul
1 | 10 | Pune| 5 | update | 2015-01-22|4 
2 | 20 | beng| 5 | update | 2015-12-13|2 --------------min(doj) record
2 | 20 | chen| 5 | insert | 2014-12-12|15 
2 | 20 | beng| 5 | insert | 2010-10-15|16 -----this record not fall the above conditions
3 | 30 | beng| 5 | update | 2011-11-15 |3
4 | 40 |chen |5 |update |2010-11-15 |6
4 | 40 |beng |5 |insert |2010-11-15 |20
4 | 40 |beng |5 |insert |2010-11-15 |21
5 | 50 |beng |5 |update |2010-12-14 |9

I tried like below

SELECT e.Empid,
       ER.deptid,
       e.loc,
       e.[status],
       ER.[guid],
       e.[guid],
       CASE
           WHEN DATEDIFF(DAY, e.doj, ER.startdate) <= 0
               THEN 'INSERT'
           ELSE 'UPDATE'
       END filter,
       e.doj
FROM emp e
INNER JOIN empref ER ON e.Empid = ER.empid AND e.deptid = ER.deptid
WHERE e.[status] = 5
    AND ER.[status] = 2
    AND e.doj <= ER.startdate
    AND DATEDIFF(DAY, e.doj, ER.startdate) <= 30

and I tried another way

SELECT *
FROM (
    SELECT e.empid,
           e.deptid,
           e.loc,
           e.status,
           CASE
               WHEN DATEDIFF(DAY, e.doj, b.startdate) < 30
                   THEN 'Update'
               ELSE 'Insert'
           END AS filter,
           e.doj,
           CASE
               WHEN DATEDIFF(DAY, e.doj, b.startdate) < 30
                   THEN b.guid
               ELSE e.guid
           END AS guid,
           CASE
               WHEN ROW_NUMBER() OVER (PARTITION BY e.guid ORDER BY e.doj DESC) = 1
                   THEN 'current'
               ELSE 'initial'
           END AS Fil,
           CASE
               WHEN ROW_NUMBER() OVER (PARTITION BY b.empid, b.deptid ORDER BY b.startdate DESC) = 1
                   THEN 'current'
               ELSE 'initial'
           END AS Fil1,
           b.startdate
    FROM emp e
    LEFT JOIN empref b ON b.empid = e.empid
        AND b.deptid = e.deptid
        AND b.startdate >= e.doj
    WHERE e.status = 5
        AND b.status = 2
        AND e.empid = 1
) a
WHERE a.fil = 'current'

In the above query not given expected result. please tell me how to write query to achieve this task in sql server