InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 15:26:26 GMT20http://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]momba (2/5/2013)[/b][hr]...I need to study this row number() thing. Will let you know how it goes when I straigten it out. Thanks for being so patient with my newbieness.[/quote]It's always better to learn new things.But in this case good old derived table will work pretty well:[code="sql"]select CONVERT (varchar(9),T.EMPLOYEE_ID) AS emp_id, CONVERT(datetime,(CONVERT(varchar(8), T.ADMISSION_DTE))) AS Adm_date, T.ADMISSION_CDE, T.ADMIT_TYPE_CDE, CONVERT(datetime,(CONVERT(varchar(8), T.DISC_DTE))) AS Dis_date, T.DISC_CDE, T.DISC_TYPE_CDEfrom CurrentTable T INNER JOIN (SELECT EMPLOYEE_ID, MAX(DISC_DTE) latest_dte FROM CurrentTable GROUP BY EMPLOYEE_ID ) LT ON LT.EMPLOYEE_ID = T.EMPLOYEE_ID AND LT.latest_dte = T.DISC_DTEWHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))order by emp_id , adm_date[/code]Tue, 12 Feb 2013 19:37:31 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]momba (2/5/2013)[/b][hr]...I need to study this row number() thing. Will let you know how it goes when I straigten it out. Thanks for being so patient with my newbieness.[/quote]Yes, you DO need to study ROW_NUMBER(), and likely the numerous other things that come along with OVER() and what are known as "windowing functions", ESPECIALLY that you are working on SQL Server 2012, where windowing functions finally got some lovin' by the dev team! :cool:Tue, 05 Feb 2013 15:31:08 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx...I need to study this row number() thing. Will let you know how it goes when I straigten it out. Thanks for being so patient with my newbieness.Tue, 05 Feb 2013 14:23:54 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]momba (2/5/2013)[/b][hr]:0( Now it says I'm missing the "GROUP BY" clause... (still working on it)...[/quote]No group by needed., forgot to take off the MAX.[code="sql"]WITH BaseData AS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY ADMISSION_DTE DESC), CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, ADMISSION_DTEFROM CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) ))SELECT *FROM BaseDataWHERE rn = 1;[/code]Tue, 05 Feb 2013 14:20:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx:0( Now it says I'm missing the "GROUP BY" clause... (still working on it)...Tue, 05 Feb 2013 14:11:30 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxMore like a picnic problem. That's what I get for writing code on a smartphone.Thanks for catching the missing parens.Tue, 05 Feb 2013 14:06:48 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxlooks like a copy/paste error:ROW_NUMBER needs parenthesis:change that query to ROW_NUMBER() and it should work fine.Tue, 05 Feb 2013 13:54:21 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxCause Lynn left out some brackets[code="sql"]WITH BaseData AS (SELECT rn = ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY ADMISSION_DTE DESC), CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_dateFROM CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) ))SELECT *FROM BaseDataWHERE rn = 1;[/code]Tue, 05 Feb 2013 13:54:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]Lynn Pettis (2/5/2013)[/b][hr][quote][b]momba (2/5/2013)[/b][hr]RE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...SELECT CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_datefrom CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) )GROUP BY EMPLOYEE_ID.... but as soon as I add in additional fields the numbers get higher and higher. ???I'm going to try the next example.[/quote]This:[code="sql"]WITH BaseData AS (SELECT rn = ROW_NUMBER OVER (PARTITION BY EMPLOYEE_ID ORDER BY ADMISSION_DTE DESC), CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_dateFROM CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) ))SELECT *FROM BaseDataWHERE rn = 1;[/code]Any columns you need added, ad to the SELECT in the CTE. You can also define which columns to select from the CTE instead of using the *, this way you can eliminate the column rn.[/quote]... for some reason SSMS 2012 doesn't like "OVER" in the "ROW NUMBER OVER" syntax. I'm working through it now...Tue, 05 Feb 2013 13:51:05 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]momba (2/5/2013)[/b][hr]RE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...SELECT CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_datefrom CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) )GROUP BY EMPLOYEE_ID.... but as soon as I add in additional fields the numbers get higher and higher. ???I'm going to try the next example.[/quote]This:[code="sql"]WITH BaseData AS (SELECT rn = ROW_NUMBER OVER (PARTITION BY EMPLOYEE_ID ORDER BY ADMISSION_DTE DESC), CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_dateFROM CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) ))SELECT *FROM BaseDataWHERE rn = 1;[/code]Any columns you need added, ad to the SELECT in the CTE. You can also define which columns to select from the CTE instead of using the *, this way you can eliminate the column rn.Tue, 05 Feb 2013 12:49:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxRE: Lowell's post....results are still too high... when I try to follow your logic in the code I get lost in the syntax around "GROUP BY" so I started back at the basics. If I just do emp_id and adm_date the numbers are right...SELECT CONVERT (VARCHAR(9), EMPLOYEE_ID) AS EMP_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_datefrom CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) )GROUP BY EMPLOYEE_ID.... but as soon as I add in additional fields the numbers get higher and higher. ???I'm going to try the next example.Tue, 05 Feb 2013 12:43:27 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxThis could be more efficient (esp if "pkstuff" tight and/or the table is indexed properly for the correlated SELECT):[code="sql"]select CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date, ADMISSION_CDE, ADMIT_TYPE_CDE, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date, DISC_CDE, DISC_TYPE_CDEfrom CurrentTable ct1WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))and ct1.admission_dte = (SELECT MAX(ct2.admission_dte) from curenttable ct1 WHERE ct1.pkstuff = ct2.pkstuff)order by emp_id , adm_date[/code]You can also solve this with this construct: , ROW_NUMBER() OVER(partition by ... order by admission_dte desc) as rownumberWHERE rownumber = 1Tue, 05 Feb 2013 12:24:37 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]momba (2/5/2013)[/b][hr]I'm trying to pull out the last record for each employee, i.e. if they appear in the table two or more times, I just want the record attached to the later adm_date (which should result in just 6474 records).[/quote]if you want the latest date, you want to use GROUP BY., and actually use the MAX*() function on the date so you get that last(latest/max date[code]SELECT CONVERT (VARCHAR(9), EMPLOYEE_ID) AS emp_id, MAX(CONVERT(DATETIME, (CONVERT(VARCHAR(8), ADMISSION_DTE)))) AS Adm_date, ADMISSION_CDE, ADMIT_TYPE_CDE, CONVERT(DATETIME, (CONVERT(VARCHAR(8), DISC_DTE))) AS Dis_date, DISC_CDE, DISC_TYPE_CDEFROM CurrentTableWHERE [BranchID] = '950' AND ( DISC_DTE IS NULL OR ( DISC_DTE > 20120630 AND DISC_DTE < 20130201 ) )GROUP BY CONVERT (VARCHAR(9), EMPLOYEE_ID), ADMISSION_CDE, ADMIT_TYPE_CDE, CONVERT(DATETIME, (CONVERT(VARCHAR(8), DISC_DTE))), DISC_CDE, DISC_TYPE_CDEORDER BY emp_id, adm_date [/code]Tue, 05 Feb 2013 12:04:48 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxI'm trying to pull out the last record for each employee, i.e. if they appear in the table two or more times, I just want the record attached to the later adm_date (which should result in just 6474 records).Tue, 05 Feb 2013 11:55:54 GMTmombahttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxDistinct just removes complete duplicate rows (rows where all the columns you select are exactly the same)What is that query supposed to return?Tue, 05 Feb 2013 11:39:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspx[quote][b]momba (2/5/2013)[/b][hr]I've got another noob question...When I run this query to just grab the emp_id I get 6474 but when I request more fields I get 6605?[code="sql"]select distinct CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date, ADMISSION_CDE, ADMIT_TYPE_CDE, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date, DISC_CDE, DISC_TYPE_CDEfrom CurrentTableWHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))order by emp_id , adm_date[/code]...I just want the record for the most recent adm_date what's up with my distinct?[/quote]The problem is with your understanding of the DISTINCT operator. It returns all distinct records based on all the columns in the select list. Each row will be distinct.Tue, 05 Feb 2013 11:38:45 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1416018-3077-1.aspxI've got another noob question...When I run this query to just grab the emp_id I get 6474 but when I request more fields I get 6605?[code="sql"]select distinct CONVERT (varchar(9),EMPLOYEE_ID) AS emp_id, CONVERT(datetime,(CONVERT(varchar(8),ADMISSION_DTE))) AS Adm_date, ADMISSION_CDE, ADMIT_TYPE_CDE, CONVERT(datetime,(CONVERT(varchar(8),DISC_DTE))) AS Dis_date, DISC_CDE, DISC_TYPE_CDEfrom CurrentTableWHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))order by emp_id , adm_date[/code]...I just want the record for the most recent adm_date what's up with my distinct?Tue, 05 Feb 2013 11:31:20 GMTmomba