February 5, 2013 at 3:31 pm
momba (2/5/2013)
...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.
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! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 12, 2013 at 7:37 pm
momba (2/5/2013)
...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.
It's always better to learn new things.
But in this case good old derived table will work pretty well:
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_CDE
from 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_DTE
WHERE [BranchID]='950' AND (DISC_DTE IS NULL OR (DISC_DTE > 20120630 AND DISC_DTE < 20130201))
order by emp_id , adm_date
_____________
Code for TallyGenerator
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply