Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Finding the MIN and MAX date - How do I approach this? Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 3:39 PM
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?
Post #1521656
Posted Tuesday, December 10, 2013 1:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1521657
Posted Tuesday, December 10, 2013 1:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 3,540, Visits: 7,633
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521662
Posted Tuesday, December 10, 2013 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 3:39 PM
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?
Post #1521664
Posted Tuesday, December 10, 2013 1:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 3,540, Visits: 7,633
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521666
Posted Tuesday, December 10, 2013 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 12,910, Visits: 32,013
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1521669
Posted Tuesday, December 10, 2013 2:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 3:39 PM
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?

Post #1521672
Posted Tuesday, December 10, 2013 2:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 3,540, Visits: 7,633
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521681
Posted Tuesday, December 10, 2013 2:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 3:39 PM
Points: 6, Visits: 8
Thank you very much.

That was the solution I was looking for.

I appreciate the help.
Post #1521685
Posted Tuesday, December 10, 2013 3:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 3:39 PM
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.
Post #1521702
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse