T-SQL Grouping & aggregation Issue

Question

Hi all ,

I have below reult set,

I want distinct Employee from it .

Base on condition as follows.

If their are Single record for Employee Then it will take As it is its record.

If their are Duplicate EmployeeNo record(i.e. same EmployeeNo and same Projectcode ,Oracle ProjectNumber) and Project code =OracleProjectNumber then  take Max assignmentStart date .

If their are duplicate EmployeeNo  record (i.e. (1st) is  same EmployeeNo and different PRojectCode,OracleProjectNumber AND (2nd) is same EmployeeNo and same  Projectcode ,Oracle ProjectNumber) then it will take 2nd condition record along with date)

IF it is Single EmployeeNO record and Both projectCode and OracleProjectNumber are same/different then it will take record as it is along with date . 

(in case of 11805 EmployeeNO I want second record becoz Both projectCode are same ;so ignore 1st.

If 11805 EmployeeNo having both record with same  projetCode,OracleProjectNumber then I want Max Date from it's Two record)

from below result set I need total 11 record with above condition for date.

Answer 1

Please rephrase your question to make it simpler. If possible give scenarios with Data Values from the above data shown. You have only said what you want if some values are equal and are duplicate. What should be returned in a anti scenario.

Please try to be as clear and simple when posting questions.

Answer 2

Is this the expected result?

--CREATE TABLE EmployeeAssignment
--(
--EmployeeNo int
--,ProjectCode int
--,OracleProjectNumber int
--,AssignmentStart datetime
--,AssignmentEnd datetime
--)

--INSERT EmployeeAssignment
--(
--EmployeeNo 
--,ProjectCode 
--,OracleProjectNumber 
--,AssignmentStart 
--,AssignmentEnd 
--)
--VALUES 
--(1,1,1,'2009-1-20','2015-3-31')
--,(1,1,2,'2007-1-20','2015-3-31')
--,(2,1,1,'2006-1-20','2015-3-31')
--,(3,1,1,'2005-1-20','2015-3-31')
--,(3,1,2,'2005-1-20','2015-3-31')
--,(4,1,2,'2010-1-20','2015-3-31')
--,(4,1,2,'2011-1-20','2015-3-31');

WITH CTE (EmployeeNo ,ProjectCode ,OracleProjectNumber ,AssignmentStart ,AssignmentEnd )
AS 
	(SELECT EmployeeNo 
	,ProjectCode 
	,OracleProjectNumber 
	,AssignmentStart 
	,AssignmentEnd 
	FROM EmployeeAssignment)
SELECT B.EmployeeNo 
	,B.ProjectCode 
	,B.OracleProjectNumber
	,B.AssignmentStart
	,B.AssignmentEnd 
from CTE B
JOIN (
	SELECT C.EmployeeNo 
		,C.ProjectCode 
		,C.OracleProjectNumber 
		,C.AssignmentStart
		FROM CTE C
		JOIN 
			(select EmployeeNo 
				,ProjectCode 
				,MIN(AssignmentStart) AS AssignmentStart
			FROM CTE
			GROUP BY EmployeeNo 
				,ProjectCode 
			) A
		ON C.EmployeeNo = A.EmployeeNo 
			AND C.ProjectCode = A.ProjectCode 
			AND C.AssignmentStart = A.AssignmentStart 
		)D
ON B.EmployeeNo = D.EmployeeNo 
	AND B.ProjectCode = D.ProjectCode 
	AND B.OracleProjectNumber = D.OracleProjectNumber
	AND B.AssignmentStart = D.AssignmentStart
ORDER BY B.EmployeeNo,B.OracleProjectNumber 

Answer 3

It is not entirely simple to follow your conditions, but this is the pattern for this type of request:

WITH CTE AS (
    SELECT EmployeeNo, ProjectCode ...,
           row_number() OVER (PARTITION BY EmployeeNo
                                ORDER BY <add your choice here>) AS rowno
   FROM tbl
)
SELECT EmployeeNo, ProjectCode ...,
FROM   CTE
WHERE  rowno = 1

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. Now we have to guess and write your code for you! What is the name of the table??  Also, please learn why rows are not records. They have no ordering, so “first record” and “second record” have no meaning in SQL. 

English is an obvious problem for you. Try writing in your native language and then use Google Translate to post English. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

Thanks sir.

will try io improve my terminalogy in case of SQL terms.