Saturday, January 05, 2013 10:31 AM
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.
Saturday, January 05, 2013 12:35 PM
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.
Saturday, January 05, 2013 12:41 PM
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
Saturday, January 05, 2013 3:32 PM
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
SELECT EmployeeNo, ProjectCode ...,
WHERE rowno = 1
Erland Sommarskog, SQL Server MVP, email@example.com
Saturday, January 05, 2013 4:47 PMPlease 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
Tuesday, March 26, 2013 6:20 AM
will try io improve my terminalogy in case of SQL terms.