InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 09:25:45 GMT20http://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxA simple reply is enough, there are no extra points for correct answers.When you spend more time in this forum, you'll find out that answers that might seem correct, could be corrected to perform better or fix a bug.Welcome to SQL Server Central.Thu, 08 Nov 2012 13:32:29 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxThanks for the tips, Luis.Since I am learning here, does Sam get points for answering my question ?Thu, 08 Nov 2012 12:26:14 GMTRonWhttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxRon,Even if the Excel file is better than an image, it's not the best option to post sample data.There are people that won't download any files (due to company policies or personal preferences) and it's not in the best format to use it as it is.You should post your sample data the way Sam did. Take a look at the article linked in my signature for more information for your future posts.Thu, 08 Nov 2012 10:19:24 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxSorry for not giving sample data.Sam, your answer works. Thank you very much. :-DI used the first script since the last row is always the highest date.I did have to add a RowID.If anyone is interested, I attached a spreadsheet containing Sheet1 with raw data and Sheet2 with results.Thanks to all who replied!Thu, 08 Nov 2012 09:40:05 GMTRonWhttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxThis should work if I am understanding your problem correctly. Please test it with different test cases.I have assumed that there is a identity row id without gaps for each row, and in the order your data is in. Also, when you said "find the maximum date for the EMPLOYEE", I assumed you meant the maximum EndDate; if it is EffectDate, replace it accordingly.There are 2 solutions; Solution 1 is useful if your most recent date is always in the last row (row with the highest RowId); otherwise check out Solution 2.There are certain aspects where you can improve the performance; but this can be a good starting point. Again, please test the code with various test cases.[code="other"]CREATE TABLE #a(Employee INT, Effect_Date DATETIME, End_Date DATETIME, Job_Code VARCHAR(10), RowId INT IDENTITY(1, 1))INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '11/12/1992', '6/17/1993', 'T065'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '6/18/1993', '8/31/1993', 'T065'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '9/1/1993', '7/31/2001', 'T065'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/1/2001', '9/19/2004', 'T083'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '9/20/2004', '7/31/2007', 'T083'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/1/2007', '8/15/2007', 'T079'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/16/2007', '8/12/2009', 'T079'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/16/2007', '6/12/2008', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '6/13/2008', '8/13/2008', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/14/2008', '6/11/2009', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '6/12/2009', '8/16/2009', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/13/2009', '8/14/2012', 'T079'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/17/2009', '12/31/2009', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '1/11/2010', '8/12/2010', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/16/2010', '12/12/2010', 'T009'--INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '8/16/2012', '12/12/2013', 'T009'INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '12/13/2010', '6/9/2011', 'T009'--INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '6/10/2011', '6/30/2011', 'T011'--INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '7/1/2011', '9/30/2011', 'T011'--INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '10/1/2011', '10/28/2011', 'T009'--INSERT INTO #a(Employee, Effect_Date, End_Date, Job_Code) SELECT 1, '10/29/2011', '12/30/2011', 'T009'---- ---- Solution 1. (If your Max Date is always in the last row; use this)/*For a given employee, join on rows where the Job Code is different, but the rowid in the left table is lower than the row-id on the right table.This means that the last continuous sets of Employee-Job_Codes will have null values on the right hand side.And since your Max date is in the last row, then a min and max on the left hand row-ids where right hand row-ids are null should give you the rows that contain your solution data.*/SELECT A1.Employee, A1.Job_Code, MIN(A1.Effect_Date) AS Effect_Date, MAX(A1.End_Date) AS End_Date FROM #a A1 LEFT JOIN #a A2 ON (A1.Employee = A2.Employee AND A1.Job_Code <> A2.Job_Code AND A1.RowId < A2.RowId) WHERE A2.RowId IS NULL GROUP BY A1.Employee, A1.Job_Code---- ---- Solution 2. (If your Max Date is not always in the last row; use this. This should work even if your max date is in the last row)/*In this solution, first get the MinRow and Maxrow for each continuous sets of Employee-Job_Codes.To do this, join on rows where the Job_Code on Right table is different but left RowId is less than right RowId and then get a minimum of Right RowId (lets call this MinRowId); Then each Employee-Job_Code-MinRowId represents a continuous set Then get that row from this intermediate result table which contains the row-id where max date is present and use it to get the related dates*/; WITH D1 (Employee, Job_Code, Effect_Date, End_Date, RowId, MinRowId) AS -- Step 1 of [Gets MinRow and Maxrow for each Continuous Sets of Employee-Job_Codes](SELECT A1.Employee, A1.Job_Code, A1.Effect_Date AS Effect_Date, A1.End_Date AS End_Date, A1.RowId, MIN(A2.RowId) AS MinRowId FROM #a A1 LEFT JOIN #a A2 ON (A1.Employee = A2.Employee AND A1.Job_Code <> A2.Job_Code AND A1.RowId < A2.RowId) GROUP BY A1.Employee, A1.Job_Code, A1.Effect_Date, A1.End_Date, A1.RowId),D2 (Employee, Job_Code, MinRowId, MaxRowId) AS -- Step 2 of [Gets MinRow and Maxrow for each Continuous Sets of Employee-Job_Codes](SELECT A.Employee, A.Job_Code, MIN(A.RowId) AS MinRowId, MAX(A.RowId) AS MaxRowId FROM D1 AS A GROUP BY A.Employee, A.Job_Code, A.MinRowId),MRD(Employee, RowId, DtRnk) AS -- Row for [Most Recent Effect_Date](SELECT A1.Employee, A1.RowId, ROW_NUMBER() OVER (PARTITION BY A1.Employee ORDER BY A1.Employee, A1.Effect_Date DESC) AS Rnk -- Change Effect_Date to End_Date in the Rnk if your max date has to be End_Date FROM #a A1)SELECT D2.Employee, D2.Job_Code, AMIN.Effect_Date, AMAX.End_Date FROM MRD M INNER JOIN D2 ON (M.Employee = D2.Employee AND M.RowId BETWEEN D2.MinRowId AND D2.MaxRowId) -- Max Date Row is Between MinRow and MaxRow of a Employee-Job_Code cOntinuous set. INNER JOIN #a AMIN ON (D2.Employee = AMIN.Employee AND D2.Job_Code = AMIN.Job_Code AND D2.MinRowId = AMIN.RowId) INNER JOIN #a AMAX ON (D2.Employee = AMAX.Employee AND D2.Job_Code = AMAX.Job_Code AND M.RowId = AMAX.RowId) WHERE M.DtRnk = 1DROP TABLE #a[/code]Tue, 06 Nov 2012 15:10:06 GMTSam S Kollihttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspx[quote][b]CELKO (11/6/2012)[/b][hr] Does your boss draw pictures when he wants to give you programming specs? [/quote]:( Unfortunately, yes. However, he pays me to use the pictures and we're all volunteers here.We need some help, to help, we need DDL and consumable sample data.Tue, 06 Nov 2012 07:49:07 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxPlease 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. Does your boss draw pictures when he wants to give you programming specs? Do often open files from people who do not know?Tue, 06 Nov 2012 06:14:50 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxFrom what I understood from reading your requirement and looking at the image you uploaded....i have come up with the following....I think it should work for you:[code="sql"]Select Employee, MIN(Effect_Date) As MinEffectDate, MIN(End_Date) From Table Where JobCode = (Select JobCode From Table Where Effect_Date = (Select MAX(Effect_Date) From Table))Group By Employee [/code]If this does not work for your requirement the please visit the link in my signature and post DDL and sample data as shown in the link.....it is very tedious to create a sample data set by looking at the image you uploaded and the people here would not have the time to do so.If you post the DDL and Sample data as shown in the link in my signature, then you might have a better chance of getting your problem solved.[b]EDIT(07/11/2012)[/b]: There is a slight edit in this code......I forgot to add Group By in the code....you'll have to group by Employee.Tue, 06 Nov 2012 01:53:59 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxCan you post some DDL and sample data?Tue, 06 Nov 2012 01:51:36 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxTry thisSELECT EMPLOYEE,MIN(EFECT_DATE) AS EFECT_DATE,MAX(END_DATE) AS END_DATE,JOB_CODEFROM TABLEGROUP BY EMPLOYEE,JOB_CODEHAVING JOB_CODE ='T009'Mon, 05 Nov 2012 16:59:43 GMTMaiquehttp://www.sqlservercentral.com/Forums/Topic1381314-392-1.aspxMy task is to find the minimum and maximum date for the Code contained in the latest row. A picture will help...As you will see in the attached example, I need t-sql to 1) find the maximum date for the EMPLOYEE 2) capture the JOB_CODE related to the max date 3) find the minimum date (going backward in EFFECTIVE_DATE descending) related to the specific JOB_CODE.You will notice the JOB_CODE can come and go in date order. I only want the min date for the[u] latest [/u]set of continuous JOB_CODE rows.In the attached example, I want to end up with EFFECTIVE_DATE = '2009-08-17' and END_DATE = '2011-06-09' as based on JOB_CODE = 'T009'.Thanks for the help.Mon, 05 Nov 2012 16:48:25 GMTRonW