SQLTeam.com | Weblogs | Forums

Need to get records based on multiple conditions in sql server

sql2008

#1

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


#2

CREATE TABLE Job_Assignments
(Job_Assignments_id CHAR(5) NOT NULL
REFERENCES Personnel(Job_Assignments_id),
dept_id CHAR(5) NOT NULL
REFERENCES Departments (dept_id) NOT NULL,
initial_assignment_date DATE NOT NULL,
assignment_city_name CHAR(5) NOT NULL
CHECK (assignment_city IN ('che', 'pune', 'beng')),
assignment_status INTEGER NOT NULL
CHECK (assignment_status > 0),
PRIMARY KEY (Job_Assignments_id, dept_id, initial_assignment_date, assignment_city_name, assignment_status);
);

INSERT INTO Job_Assignments
VALUES
('00001', 10, '2013-09-25', 'hyd', 5),
('00001', 10, '2014-03-25', 'che', 5),
('00001', 10, '2014-04-09', 'pune', 5),
('00001', 10, '2015-01-22', 'pune', 5),
('00002', 20, '2015-12-13', 'beng', 5),
('00002', 20, '2014-12-12', 'chen', 5),
('00002', 20, '2010-10-15', 'beng', 5),
('00002', 20, '2010-10-15', 'beng', 4),
('00003', 30, '2011-11-15', 'beng', 5);

This is still crappy design, but at least it has a key, constraints and follows ISO rules.

I will guess that you need to look at

But first, learn the basics of a simple data model. After putting 40 years of my life into SQL. It is painful to see it misused this way.