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