compare Max with 2nd Max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

compare Max with 2nd Max date

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 17:18:42

Table A
101 12/31/2010
101 12/31/2012
101 6/30/2009
102 12/31/2010
102 12/31/2012
102 6/30/2009
102 4/30/2009

To return the max I use the following query:
SELECT ID, Max(A.DATE) AS MaxDate
FROM A
WHERE A.TYPE = 'SUB'
GROUP BY A.ID

I need to compare the Max Date BY ID with the second max date.

I tried using the top two and a not exists in the above sub query. I tries usinf the exact same query with a not exists, but cannot get the correct syntax.

Thanks,
dz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-23 : 17:23:38

[code]DECLARE @Sample TABLE
(
ID TINYINT,
[DATE] DATETIME
)

INSERT @Sample
SELECT 101, '12/31/2010' UNION ALL
SELECT 101, '12/31/2012' UNION ALL
SELECT 101, '6/30/2009' UNION ALL
SELECT 102, '12/31/2010' UNION ALL
SELECT 102, '12/31/2012' UNION ALL
SELECT 102, '6/30/2009' UNION ALL
SELECT 102, '4/30/2009'

SELECT ID,
MAX(CASE WHEN recID = 1 THEN [DATE] ELSE NULL END) AS MaxDate,
MAX(CASE WHEN recID = 2 THEN [DATE] ELSE NULL END) AS SecondMaxDate
FROM (
SELECT ID,
[DATE],
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE] DESC) AS recID
FROM @Sample
--WHERE [TYPE] = 'SUB'
) AS d
WHERE recID <= 2
GROUP BY ID[/code]

N 56°04'39.26"
E 12°55'05.63"

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 17:29:24

I forgot to mention - I tried ROW_NUMBER and it is not a recognized function. The data is stored on a 2000 box. I am using 2005 app to run the scripts.

Thanks,
Debi

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2010-08-23 : 17:34:43

Try replacing ROW_NUMBER with RANK for 2000.

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 17:36:23

I will try that, but can you tell me if there is a way to get it by using the script below:

select ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate2 from activity
where not exists (
SELECT ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate1
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID
)
GROUP BY ACTIVITY.ID

This does not error out, but is not returning anyhting. I need it to return the ID, date of teh max not in the sub query.

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 17:38:10

'RANK' is not a recognized function name.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2010-08-23 : 17:52:09

quote:
Originally posted by dzabor

I will try that, but can you tell me if there is a way to get it by using the script below:

select ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate2 from activity
where not exists (
SELECT ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate1
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID
)
GROUP BY ACTIVITY.ID

This does not error out, but is not returning anyhting. I need it to return the ID, date of teh max not in the sub query.

You are only testing that the subquery in your where clause returns a value. You are not testing those values returned by the subquery.

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 17:57:57

Yay! I got it a bit further.

-- returns max date by id
SELECT ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID

Returns:
101 12/31/2012
102 12/31/2012

-- returns 2nd max date by id
select ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate2
from activity
where ACTIVITY.THRU_DATE not in (
SELECT Max(ACTIVITY.THRU_DATE) AS MaxDate1
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID
)
and activity_type = 'sub'
GROUP BY ACTIVITY.ID

returns:

101 12/31/2010
102 12/31/2010

Now I need to figure out how to compare dates by ID.

dz

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2010-08-23 : 17:58:57

Replace

				ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE] DESC) AS recID
			FROM	@Sample

with

				(SELECT COUNT(*) FROM @sample WHERE ID = s1.ID AND [DATE] >= s1.[DATE]) AS recID
			FROM	@Sample as s1

in Peso's solution.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2010-08-23 : 18:13:33

Continuing on the path that you were on you would need to remove your subquery from the where clause and left join against it as a derived table. You could then pull the second maximum date from your result set by grabbing the max date where there was no match in your derived table (where the date returned in the derived table is null).

Something like this:

SELECT  a1.ID,
        MAX(a1.THRU_DATE) AS MaxDate1,
        MAX(CASE WHEN a2.THRU_DATE IS NULL THEN a1.THRU_DATE ELSE NULL END) AS MaxDate2
FROM    ACTIVITY AS a1
LEFT OUTER JOIN (
                  SELECT  ACTIVITY.ID,
                          MAX(ACTIVITY.THRU_DATE) AS MaxDate1
                  FROM    ACTIVITY
                  GROUP BY ACTIVITY.ID
                ) AS a2
        ON a1.ID = a2.ID
           AND a1.THRU_DATE = a2.THRU_DATE
GROUP BY a1.ID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-08-23 : 18:22:25

[code]DECLARE @Sample TABLE
(
ID TINYINT,
[DATE] DATETIME
)

INSERT @Sample
SELECT 101, '12/31/2010' UNION ALL
SELECT 101, '12/31/2010' UNION ALL
SELECT 101, '6/30/2009' UNION ALL
SELECT 102, '12/31/2010' UNION ALL
SELECT 102, '12/31/2012' UNION ALL
SELECT 102, '6/30/2009' UNION ALL
SELECT 102, '4/30/2009'

Select S.ID,MaxDate,MAX(S.[DATE])as SecondMaxDate
from @Sample S
inner join
(
Select ID,MAX([DATE]) as MaxDate
from @Sample
Group by ID
)Z on Z.ID = S.ID and Z.MaxDate > S.[DATE]
Group by S.ID,MaxDate[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-08-23 : 18:24:44

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		ID TINYINT,
		[DATE] DATETIME
	)

INSERT	@Sample
SELECT	101, '12/31/2010' UNION ALL
SELECT	101, '12/31/2012' UNION ALL
SELECT	101, '6/30/2009' UNION ALL
SELECT	102, '12/31/2010' UNION ALL
SELECT	102, '12/31/2012' UNION ALL
SELECT	102, '6/30/2009' UNION ALL
SELECT	102, '4/30/2009'

SELECT		ID,
		MAX(CASE WHEN recID = 1 THEN [DATE] ELSE NULL END) AS MaxDate,
		MAX(CASE WHEN recID = 2 THEN [DATE] ELSE NULL END) AS SecondMaxDate
FROM		(
			SELECT	ID,
				[DATE],
				ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DATE] DESC) AS recID
			FROM	@Sample
			--WHERE	[TYPE] = 'SUB'
		) AS d
WHERE		recID <= 2
GROUP BY	ID

N 56°04'39.26"
E 12°55'05.63"

Shouldn't we be using DENSE_RANK() for 2 records with same Max Date? Although OP hasn't specified over here.

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 20:29:12

Ok - I put this together before looking at teh last post. It returns everything I need )and I know there is some over kill), but now I need to add an update statement to the beginning as follows:

update DEMO
set EXPIRE = Z1.MaxDate
from DEMO
inner join DEMO.id = @compareDates.id

--I am not sure how to connect to my query below:

DECLARE @compareDates TABLE
(
ID int,
[MaxDate1] DateTime,
[MaxDate2] DateTime
)

INSERT @compareDates
select ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate1, Null AS MaxDate2
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID
union
select ACTIVITY.ID, null, Max(ACTIVITY.THRU_DATE) AS MaxDate2
from activity
where ACTIVITY.THRU_DATE not in (
SELECT Max(ACTIVITY.THRU_DATE) AS MaxDate1
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID
)
and activity_type = 'sub'
GROUP BY ACTIVITY.ID

Select Z2.ID,MaxDate,MAX(Z2.MaxDate2)as MaxDate
from @compareDates Z2
inner join
(
Select ID,MAX(MaxDate1) as MaxDate
from @compareDates
Group by ID
)Z1 on Z1.ID = Z2.ID and DateDiff(day,Z1.MaxDate, Z2.MaxDate2) < 365
Group by Z2.ID,MaxDate

Thanks in advance for all of your help!!!
dz

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-23 : 20:34:38

I also tried this one first since it seemed the simplest (and easiest to understand), but it kept coming up with the error "Msg 207, Level 16, State 3, Line 1
Invalid column name 'THRU_DATE'." It is a valid column name from the activity table. If I can get this one going it would be the easiest to add an update statement too!

**************************
SELECT a1.ID,
MAX(a1.THRU_DATE) AS MaxDate1,
MAX(CASE WHEN a2.THRU_DATE IS NULL THEN a1.THRU_DATE ELSE NULL END) AS MaxDate2
FROM ACTIVITY AS a1
LEFT OUTER JOIN (
SELECT ACTIVITY.ID,
MAX(ACTIVITY.THRU_DATE) AS MaxDate1
FROM ACTIVITY
GROUP BY ACTIVITY.ID
) AS a2
ON a1.ID = a2.ID
AND a1.THRU_DATE = a2.THRU_DATE
GROUP BY a1.ID

dz

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-24 : 11:01:51

jdaman and Peso - this worked!!!

DECLARE @compareDates TABLE
(
ID TINYINT,
[DATE] DATETIME
)

INSERT @compareDates
select ID, THRU_DATE
FROM A
--WHERE TYPE = 'SUB'
GROUP BY A.ID,DATE

SELECT ID,
MAX(CASE WHEN recID = 1 THEN [DATE] ELSE NULL END) AS MaxDate,
MAX(CASE WHEN recID = 2 THEN [DATE] ELSE NULL END) AS SecondMaxDate
FROM (
SELECT ID,
[DATE],
(SELECT COUNT(*) FROM @compareDates WHERE ID = s1.ID AND [DATE] >= s1.[DATE]) AS recID
FROM @compareDates as s1
--WHERE [TYPE] = 'SUB'
) AS d
WHERE recID <= 2
GROUP BY ID

Thanks for all of your help - what lifesavers you are!!!

dz

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-24 : 11:59:55

Now do you have any ideas on how to add an update?

I need to update a field from another table with one of the dates depending on the difference in dates.

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-24 : 12:02:28

Here is the whole script. I still need to be able to use these dates for updates to another table.

DECLARE @DemoExpire TABLE
(
ID TINYINT,
EXPIRE DATETIME
)
INSERT @DemoExpire
select ID, EXPIRE
FROM DEMO
GROUP BY ID,EXPIRE

DECLARE @compareDates TABLE
(
ID TINYINT,
Thru_DATE DATETIME
)

INSERT @compareDates
select ID, THRU_DATE
FROM ACTIVITY
WHERE ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID,THRU_DATE

SELECT ID,
MAX(CASE WHEN recID = 1 THEN [Thru_DATE] ELSE NULL END) AS MaxDate,
MAX(CASE WHEN recID = 2 THEN [Thru_DATE] ELSE NULL END) AS SecondMaxDate
FROM (
SELECT ID,
[Thru_DATE],
(SELECT COUNT(*) FROM @compareDates WHERE ID = s1.ID AND [Thru_DATE] >= s1.[Thru_DATE]) AS recID
FROM @compareDates as s1
) AS d
WHERE recID <= 2
GROUP BY ID

--I need to compare the two dates and then update a field in another table

UPDATE DEMO
SET EXPIRE = SecondMaxDate
where - DateDiff(Day, DATEADD(mm, +12, DATEDIFF(DAY, 0, MaxDate)),SecondMaxDate) > 365

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-24 : 13:55:16

i got it. I added the demo table to the @compareDates and made a temp table:
SELECT ID,
MAX(CASE WHEN recID = 1 THEN [Thru_DATE] ELSE NULL END) AS MaxDate,
MAX(CASE WHEN recID = 2 THEN [Thru_DATE] ELSE NULL END) AS SecondMaxDate,
EXPIRE

Into #Temp

add the update to the end of the script!

Thx!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources