Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding the MIN and MAX date - How do I approach this?


Finding the MIN and MAX date - How do I approach this?

Author
Message
terry.ward
terry.ward
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
I have a table with the following data:


EMPID EffectiveDate PrimaryRater
12345 10/10/2001 A12345
12345 07/12/2013 A12345
12345 08/18/2002 A12345
12345 07/17/1966 A12345
12345 01/01/1966 B12345



What I want to do is create a table that looks like this:


EMPID StartDate PrimaryRater EndDate
12345 07/17/1966 A12345 07/12/2013
12345 01/01/1966 B12345 07/17/1966



Not knowing SQL. I am not sure what approach is best.

What approach do you recommend?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 38985
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
terry.ward
terry.ward
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
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?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 38985
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 question

Why 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:

12345 01/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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

terry.ward
terry.ward
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
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:


EMPID StartDate PrimaryRater EndDate
12345 Jul 17, 1966 A12345 Jul 12, 2013
12345 Jan 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:


EMPID StartDate PrimaryRater EndDate
12345 Jul 17, 1966 A12345 Jul 12, 2013
12345 Jan 01, 1966 B12345 Jul 17, 1966


This will provide a sequential history of all PrimaryRater, EffectiveDate combinations.

How would I achieve this?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8556 Visits: 18143
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




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
terry.ward
terry.ward
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
Thank you very much.

That was the solution I was looking for.

I appreciate the help.
terry.ward
terry.ward
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
One more question/example.

If I have the following table:


EMPID EffectiveDate PrimaryRater PrimaryReviewer
12345 10/10/2001 A12345 A67890
12345 07/12/2013 A12345 B67890
12345 08/18/2002 A12345 C67890
12345 07/17/1966 A12345 D67890
12345 01/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:


EMPID StartDate PrimaryRater PrimaryReviewer EndDate
12345 01/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.


EMPID EffectiveDate PrimaryRater PrimaryReviewer EndDate
12345 07/17/1966 A12345 B67890 Current (No EndDate)
12345 01/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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search