July 23, 2015 at 2:00 pm
Lynn - Any need for Start year Parameter to pass?
I will modify my actual query hoping it will work.. Thank you all for your input.
July 23, 2015 at 2:01 pm
Great, thank you. That is exactly what I was looking for!!! THANKS...
July 23, 2015 at 2:02 pm
Lynn Pettis (7/23/2015)
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.
Perhaps, but it's inherently understandable. I also removed the name from the group by for performance reasons, which lengthens the code but on a large table could prevent multiple lookups of the same id-->name and having to group by a character column.
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 2:02 pm
Luis Cazares (7/23/2015)
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]
Luis - I completely forgot about that method to do an UNPIVOT. Very slick.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 23, 2015 at 2:07 pm
Luis, Lynn and Wayne, I am very thankful.. With your help, I was able to get the query to working, I appreciate it! 😀
July 24, 2015 at 7:34 am
Question: Now that I changed the actual query, I am running into getting more than 1 record per year for the rows which do not match because of grouping. How to go about getting distinct value per year?
Example of data:
ID Class_enrolledType Val1Type2 Val2 Year StartDate
10203- IT related01 NULL NULL NULL 2015 2015-09-06
10203- IT related 01 01 01 01 2014 2015-01-12
10203- IT related01 01 01 01 2015 2015-10-23
As soon I included the type and value fields, I got above OP from the query.
July 24, 2015 at 7:40 am
lsalih (7/24/2015)
Question: Now that I changed the actual query, I am running into getting more than 1 record per year for the rows which do not match because of grouping. How to go about getting distinct value per year?Example of data:
ID Class_enrolledType Val1Type2 Val2 Year StartDate
10203- IT related01 NULL NULL NULL 2015 2015-09-06
10203- IT related 01 01 01 01 2014 2015-01-12
10203- IT related01 01 01 01 2015 2015-10-23
As soon I included the type and value fields, I got above OP from the query.
I would help you, but I have no idea of what you're talking about because those columns weren't part of your sample data.
July 24, 2015 at 8:16 am
Luis - I will send an updated schema and sample data shortly. Thank you very much.
July 29, 2015 at 11:47 am
Luis - Very sorry to update you, but your query worked great. The null issue was due to bad data which we fixed and had nothing to do with the query itself, once we fixed the bad data we were able to get the correct results. Once again, many thanks!
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply