July 23, 2015 at 12:51 pm
I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP?
For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year
FROM
EMP_DATE DTE join
EMP_INFO EMP on EMP.ID = DTE.ID join
YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
DATA SAMPLE:
EMP
ID NAME
1 JOHN
EMP_INFO
ID STARTDATE ENDDATE
12013-10-24 2014-03-11
12014-06-13 2014-03-11
12014-06-15 2014-03-11
12014-09-08 2014-03-11
12014-09-12 2014-03-11
12014-09-14 2014-03-11
12014-01-13 2014-05-17
12014-05-14 2014-06-09
12014-06-10 2014-06-16
12014-08-31 2014-09-04
12014-09-05 2014-09-06
12014-09-07 2014-10-23
YEAR
Year
2012
2013
2014
2015
July 23, 2015 at 1:00 pm
You have been here long enough to know that you should post your data in a consumable format.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2015 at 1:22 pm
Phil - I completely agree, I remember that and I appreciate it...
Here is the script:
Create table EMP
(ID INT,
NAME NVARCHAR(10)
)
insert into EMP values (1, 'JOHN')
create table EMP_INFO
(ID INT,
STARTDATE DATE,
ENDDATE DATE)
insert into EMP_INFO values
(1, '2013-10-24', '2014-03-11'),
(1, '2014-06-13', '2014-03-11'),
(1, '2014-06-15', '2014-03-11'),
(1, '2014-09-08', '2014-03-11'),
(1, '2014-09-12', '2014-03-11'),
(1, '2014-09-14', '2014-03-11'),
(1, '2014-01-13', '2014-05-17'),
(1, '2014-05-14', '2014-06-09'),
(1, '2014-06-10', '2014-06-16'),
(1, '2014-08-31', '2014-09-04'),
(1, '2014-09-05', '2014-09-06'),
(1, '2014-09-07', '2014-10-23')
create table [Year]
([year] numeric)
insert into year values
(2012),
(2013),
(2014),
(2015)
July 23, 2015 at 1:31 pm
lsalih (7/23/2015)
Phil - I completely agree, I remember that and I appreciate it...Here is the script:
Create table EMP
(ID INT,
NAME NVARCHAR(10)
)
insert into EMP values (1, 'JOHN')
create table EMP_INFO
(ID INT,
STARTDATE DATE,
ENDDATE DATE)
insert into EMP_INFO values
(1, '2013-10-24', '2014-03-11'),
(1, '2014-06-13', '2014-03-11'),
(1, '2014-06-15', '2014-03-11'),
(1, '2014-09-08', '2014-03-11'),
(1, '2014-09-12', '2014-03-11'),
(1, '2014-09-14', '2014-03-11'),
(1, '2014-01-13', '2014-05-17'),
(1, '2014-05-14', '2014-06-09'),
(1, '2014-06-10', '2014-06-16'),
(1, '2014-08-31', '2014-09-04'),
(1, '2014-09-05', '2014-09-06'),
(1, '2014-09-07', '2014-10-23')
create table [Year]
([year] numeric)
insert into year values
(2012),
(2013),
(2014),
(2015)
Can the ENDDATE be less than STARTDATE for any given row?
July 23, 2015 at 1:35 pm
I fixed up your create table/insert scripts. No Semi-colons?
use tempdb;
go
Create table EMP
(ID INT,
NAME NVARCHAR(10)
);
GO
insert into EMP values (1, 'JOHN')
create table EMP_INFO
(ID INT,
STARTDATE DATE,
ENDDATE DATE);
GO
insert into EMP_INFO values
(1, '2013-10-24', '2014-03-11'),
(1, '2014-06-13', '2014-03-11'),
(1, '2014-06-15', '2014-03-11'),
(1, '2014-09-08', '2014-03-11'),
(1, '2014-09-12', '2014-03-11'),
(1, '2014-09-14', '2014-03-11'),
(1, '2014-01-13', '2014-05-17'),
(1, '2014-05-14', '2014-06-09'),
(1, '2014-06-10', '2014-06-16'),
(1, '2014-08-31', '2014-09-04'),
(1, '2014-09-05', '2014-09-06'),
(1, '2014-09-07', '2014-10-23');
create table [Year]
([year] numeric)
insert into year values
(2012),
(2013),
(2014),
(2015);
GO
What's the answer to this one? (Please post your expected answer.)
Are you looking for the latest date between the StartDate and EndDate that's within a given year? (2012...2015?)
July 23, 2015 at 1:37 pm
This seems to be what you're asking for. However, it seems awkward that you consider the enddate as an enrollment date.
SELECT EMP.ID,
EMP.NAME,
MAX(x.EnrollmentDate) AS EnrollmentDate,
YR.[year]
FROM EMP_INFO DTE
join EMP EMP on EMP.ID = DTE.ID
CROSS APPLY (VALUES(DTE.StartDate),(DTE.EndDate)) x(EnrollmentDate)
join [YEAR] YR on YR.[YEAR] = YEAR(x.EnrollmentDate)
GROUP BY EMP.ID,
EMP.NAME,
YR.[year]
July 23, 2015 at 1:38 pm
Lynn - the answer is yes.
July 23, 2015 at 1:42 pm
Luis - Can you please tell me how you go about the max between start and end date?
July 23, 2015 at 1:44 pm
lsalih (7/23/2015)
Lynn - the answer is yes.
Please explain how an ENDDATE can be less than the corresponding STARTDATE.
July 23, 2015 at 1:47 pm
lsalih (7/23/2015)
Luis - Can you please tell me how you go about the max between start and end date?
I unpivoted the dates, so I wouldn't care which column has the date. That's why there's a CROSS APPLY between the JOINs.
Read more about unpivoting using CROSS APPLY in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
This won't give you the max between start and end date, but it will give you the results you requested.
July 23, 2015 at 1:48 pm
Here's another approach:
WITH cteEmpDates AS
(
SELECT ID, STARTDATE AS AnyDate FROM #EMP_INFO UNION ALL
SELECT ID, ENDDATE FROM #EMP_INFO
), cteDates AS
(
SELECT DATEFROMPARTS([year],1,1) AS YearStartDate,
DATEFROMPARTS([year],12,31) AS YearEndDate,
[year]
FROM #Year
)
SELECT t1.ID, t3.Name, t2.[year], MAX(t1.AnyDate) AS MaxDate
FROM cteEmpDates t1
JOIN cteDates t2 ON t1.AnyDate BETWEEN t2.YearStartDate AND t2.YearEndDate
JOIN #Emp t3 ON t1.ID = t3.ID
GROUP BY t1.ID, t3.Name, t2.[year];
This returns:
ID Name year MaxDate
----------- ---------- --------------------------------------- ----------
1 JOHN 2013 2013-10-24
1 JOHN 2014 2014-10-23
DATEFROMPARTS was added in SQL2012. You can read more about it in the link for the book in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 23, 2015 at 1:51 pm
I will thank you, where EnrollmentDate is coming from?
July 23, 2015 at 1:52 pm
Here's an alternate method: with only two columns, I'd probably skip using CROSS APPLY:
DECLARE @starting_year int
SET @starting_year = 2012
SELECT subquery.ID, e.NAME, subquery.Year, subquery.Max_Date
FROM (
SELECT ID, Year, MAX(Date) AS Max_Date
FROM EMP_INFO
CROSS JOIN (
SELECT 'Start' AS WhichDate UNION ALL
SELECT 'End'
) AS WhichDate
CROSS APPLY (
SELECT CASE WHEN WhichDate = 'Start' THEN STARTDATE ELSE ENDDATE END AS Date
) AS aan1
CROSS APPLY (
SELECT YEAR(Date) AS Year
) AS aan2
WHERE
CASE WHEN WhichDate = 'Start' THEN STARTDATE ELSE ENDDATE END >= CAST(@starting_year AS char(4)) + '0101'
GROUP BY ID, Year
) AS subquery
INNER JOIN EMP e ON e.ID = subquery.ID
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2015 at 1:53 pm
lsalih (7/23/2015)
I will thank you, where EnrollmentDate is coming from?
From this in the FROM clause:
CROSS APPLY (VALUES(DTE.StartDate),(DTE.EndDate)) x(EnrollmentDate)
July 23, 2015 at 1:55 pm
ScottPletcher (7/23/2015)
Here's an alternate method: with only two columns, I'd probably skip using CROSS APPLY:
DECLARE @starting_year int
SET @starting_year = 2012
SELECT subquery.ID, e.NAME, subquery.Year, subquery.Max_Date
FROM (
SELECT ID, Year, MAX(Date) AS Max_Date
FROM EMP_INFO
CROSS JOIN (
SELECT 'Start' AS WhichDate UNION ALL
SELECT 'End'
) AS WhichDate
CROSS APPLY (
SELECT CASE WHEN WhichDate = 'Start' THEN STARTDATE ELSE ENDDATE END AS Date
) AS aan1
CROSS APPLY (
SELECT YEAR(Date) AS Year
) AS aan2
WHERE
CASE WHEN WhichDate = 'Start' THEN STARTDATE ELSE ENDDATE END >= CAST(@starting_year AS char(4)) + '0101'
GROUP BY ID, Year
) AS subquery
INNER JOIN EMP e ON e.ID = subquery.ID
So much work to do what Luis did in a very succinct piece of code.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply