December 10, 2013 at 1:24 pm
I have a table with the following data:
EMPIDEffectiveDate PrimaryRater
1234510/10/2001 A12345
1234507/12/2013 A12345
1234508/18/2002 A12345
1234507/17/1966 A12345
1234501/01/1966 B12345
What I want to do is create a table that looks like this:
EMPIDStartDate PrimaryRater EndDate
1234507/17/1966 A12345 07/12/2013
1234501/01/1966 B12345 07/17/1966
Not knowing SQL. I am not sure what approach is best.
What approach do you recommend?
December 10, 2013 at 1:31 pm
well, whenever you use any of the aggregate functions(MIN,MAX,COUNT)
you have to have one or more GROUP BY column(s).
IF you group by just EMPID, you can only end up getting the MIN/MAX PrimaryRater column.
if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question
;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])
AS
(
SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL
SELECT '12345','07/12/2013','A12345' UNION ALL
SELECT '12345','08/18/2002','A12345' UNION ALL
SELECT '12345','07/17/1966','A12345' UNION ALL
SELECT '12345','01/01/1966','B12345'
)
SELECT EMPID,
Min(EffectiveDate) AS StartDate,
PrimaryRater,
max(EffectiveDate)
FROM MyCTE
GROUP BY
EMPID,
PrimaryRater
Lowell
December 10, 2013 at 1:42 pm
Lowell (12/10/2013)
if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the question
Why not? It seems correct to me.
December 10, 2013 at 1:53 pm
Would this approach work in production?
The actual table that I want to use has 82K of rows.
One row for every EffectiveDate, PrimaryRater, PrimaryReviewer combination for every EMPID in the company.
My example was just one employee. Some employees have 20 rows of combinations and some just have one row.
Reconstructing your answer for 82K of rows of data. Doesn't seem optimal. Any suggestions?
December 10, 2013 at 1:58 pm
Just to be clear.
Lowell used the following code as sample data:
;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])
AS
(
SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL
SELECT '12345','07/12/2013','A12345' UNION ALL
SELECT '12345','08/18/2002','A12345' UNION ALL
SELECT '12345','07/17/1966','A12345' UNION ALL
SELECT '12345','01/01/1966','B12345'
)
And the following is the actual solution:
SELECT EMPID,
Min(EffectiveDate) AS StartDate,
PrimaryRater,
max(EffectiveDate)
FROM MyCTE
GROUP BY
EMPID,
PrimaryRater
This will work with millions of rows and any combinations. Be sure that PrimaryRater is always the same for each employee, otherwise, you'll get a row for evey EMPID and PrimaryRater combination.
December 10, 2013 at 2:12 pm
Luis Cazares (12/10/2013)
Lowell (12/10/2013)
if you group by both EMPID and PrimaryRater, you don't get the "end Date" you were looking for according to your sample data, so im not sure if your expected results is incorrect, or there's more to the questionWhy not? It seems correct to me.
Lius, it was his expected results that made me wonder: he said his expected results for one of the rows was this data:
1234501/01/1966 B12345 07/17/1966
, but since i grouped by PrimaryRater, and there's only one row, the min/max dates are teh same value (01/01/1966)
that's why i thought either his expected results might be incorrect, or there's some biz rule not yet described, like 'if there's no [?date], use a different row?
Lowell
December 10, 2013 at 2:18 pm
Thank you,
I tried the code on my table:
SELECT EMPID,
CONVERT(VARCHAR,Min(CONVERT(DATETIME,EffectiveDate)),107) AS StartDate,
PrimaryRater,
CONVERT(VARCHAR,MAX(CONVERT(DATETIME,EffectiveDate)),107) AS EndDate
FROM ReportingTable
GROUP BY
EMPID,
PrimaryRater
I received the following results:
EMPIDStartDate PrimaryRater EndDate
12345Jul 17, 1966 A12345 Jul 12, 2013
12345Jan 01, 1966 B12345 Jan 01, 1966
I think the only piece missing is this. I would like the StartDate to be the EndDate for the Previous Primary Rater.
Like this:
EMPIDStartDate PrimaryRater EndDate
12345Jul 17, 1966 A12345 Jul 12, 2013
12345Jan 01, 1966 B12345 Jul 17, 1966
This will provide a sequential history of all PrimaryRater, EffectiveDate combinations.
How would I achieve this?
December 10, 2013 at 2:46 pm
I had totally missed the point of the different EndDate for PrimaryRater B12345.
Here's an option:
;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])
AS
( --This is sample data
SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL
SELECT '12345','07/12/2013','A12345' UNION ALL
SELECT '12345','08/18/2002','A12345' UNION ALL
SELECT '12345','07/17/1966','A12345' UNION ALL
SELECT '12345','01/01/1966','B12345'
),
--Solution starts here
Aggregated AS(
SELECT EMPID,
Min(EffectiveDate) AS StartDate,
PrimaryRater,
max(EffectiveDate) EndDate,
DENSE_RANK() OVER( PARTITION BY EMPID ORDER BY Min(EffectiveDate)) RN
FROM MyCTE
GROUP BY
EMPID,
PrimaryRater
)
SELECT a.EMPID,
a.StartDate,
a.PrimaryRater,
CASE WHEN a.StartDate = a.EndDate THEN b.StartDate ELSE a.EndDate END,
ISNULL( NULLIF(a.StartDate, a.EndDate), b.StartDate)
FROM Aggregated a
LEFT
JOIN Aggregated b ON a.EMPID = b.EMPID AND a.RN = b.RN - 1
December 10, 2013 at 2:55 pm
Thank you very much.
That was the solution I was looking for.
I appreciate the help.
December 10, 2013 at 3:43 pm
One more question/example.
If I have the following table:
EMPIDEffectiveDate PrimaryRater PrimaryReviewer
1234510/10/2001 A12345 A67890
1234507/12/2013 A12345 B67890
1234508/18/2002 A12345 C67890
1234507/17/1966 A12345 D67890
1234501/01/1966 B12345 E67890
Business rule(s):
1. The EffectiveDate is the sysdate of the update. It does not distinguish between the EffectiveDate of change for either the Primary Rater/Reviewer.
2. The only way to determine who was actually changed is to compare the IDs of the PrimaryRater and PrimaryReviewer at the time of the change with the previous PrimaryRater or PrimaryReviewer.
So, in this example the EffectiveDate of 01/01/1966 is the effective date of both Rater and Reviewer because there was no record prior. EffectiveDate 07/17/1966 is the EffectiveDate of a new set of Rater/Reviewers. Thus the Start and End dates of the previous are:
EMPIDStartDate PrimaryRater PrimaryReviewer EndDate
1234501/01/1966 B12345 E67890 07/17/1966
PrimaryRater A12345 has an EffectiveDate of 07/17/1966 but no EndDate. All other EffectiveDates are for the Reviewer not the Rater. Thus he has no EndDate and is still the most current Rater.
How would I create this table.
EMPIDEffectiveDate PrimaryRater PrimaryReviewer EndDate
1234507/17/1966 A12345 B67890 Current (No EndDate)
1234501/01/1966 B12345 E67890 07/17/1966
B67890 is the most Current Reviewer based on MAX date 07/12/2013
A12345 is the most Current Rater based on Start Date of 07/17/1966
B12345 & E67890 are the most Previous set based on EffectiveDate 01/01/1966 with no Previous sets.
The example you provided is excellent for gaining the date sets for Raters but not for adding in the most current Reviewer.
What modification would add? Thanks in advance for your considerations and help on this matter.
December 10, 2013 at 9:16 pm
Would this help?
WITH SampleData([EMPID],[EffectiveDate],[PrimaryRater], [PrimaryReviewer])
AS
( --This is sample data
SELECT '12345',convert(datetime,'10/10/2001'),'A12345', 'A67890' UNION ALL
SELECT '12345','07/12/2013','A12345', 'B67890' UNION ALL
SELECT '12345','08/18/2002','A12345', 'C67890' UNION ALL
SELECT '12345','07/17/1966','A12345', 'D67890' UNION ALL
SELECT '12345','01/01/1966','B12345', 'E67890'
)
--Solution starts here
,CTE AS(
SELECT EMPID,
PrimaryRater,
MIN( EffectiveDate) StartDate,
MAX( EffectiveDate) EndDate,
DENSE_RANK() OVER( PARTITION BY EMPID ORDER BY Min(EffectiveDate)) RN
FROM SampleData
GROUP BY EMPID,
PrimaryRater
)
SELECT c.EMPID,
c.StartDate,
c.PrimaryRater,
s.PrimaryReviewer,
ISNULL( c2.StartDate, GETDATE()) EndDate
FROM CTE c
JOIN SampleData s ON c.EMPID = s.EMPID
AND c.PrimaryRater = s.PrimaryRater
AND c.EndDate = s.EffectiveDate
LEFT
JOIN CTE c2 ON c.EMPID = c2.EMPID
AND c.RN = c2.RN - 1
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply