Distinct MaxDates - 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

Distinct MaxDates

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 11:24:15

I wrote this statement hoping it would return the following:

The max activity date, the max transaction date and the max log date.

It works, but now I need a distinct ID from all three tables along with the max maxdate. (I need it to return a distinct ID with the MaxDate from all three tables for that ID.)

select max (a.transaction_Date) maxDate, a.ID id from activity a
group by a.ID

union

select max(transaction_date)maxDate, t.st_ID id from trans t
group by t.st_ID

union

select max(Date_time)maxDate, nl.ID id from name_log nl
group by nl.ID

Thanks, DZ

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-19 : 11:27:54

[code]select distinct maxDate
from
(
select max (a.transaction_Date) maxDate, a.ID id from activity a
group by a.ID

union

select max(transaction_date)maxDate, t.st_ID id from trans t
group by t.st_ID

union

select max(Date_time)maxDate, nl.ID id from name_log nl
group by nl.ID
) m[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 11:41:53

[code]SELECT p.ID,
MAX(p.ActivityDate) AS MaxActivityDate,
MAX(p.TransDate) AS TransDate,
MAX(p.LogDate) AS LogDate
FROM (
SELECT ID,
Transaction_Date AS ActivityDate,
CAST(NULL AS DATETIME) AS TransDate,
CAST(NULL AS DATETIME) AS LogDate
FROM Activity

UNION ALL

SELECT st_ID,
NULL,
Transaction_Date,
NULL
FROM Trans

UNION ALL

SELECT ID,
NULL,
NULL,
Date_Time
FROM Trans
) AS p
GROUP BY p.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 11:49:58

What is CAST(NULL AS DATETIME) ? Is this formating all dates to be the same? I noticed the log date shows 2007-08-21 12:32:40.447000000, but the other dates are formated like this 2007-06-04 00:00:00.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 11:58:17

The CAST is to ensure that proper datatype is used throughout the query.
The important question is, did my suggestion help?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-19 : 12:10:35

[code]select t1.ID,
t1.maxDate as MaxActivityDate,
t2.maxDate AS TransDate,
t3.maxDate AS LogDate
FROM
(select max (a.transaction_Date) as 'maxDate', a.ID id from activity a
group by a.ID)t1
CROSS APPLY (select max(transaction_date)as 'maxDate' from trans t
WHERE t.st_ID=t1.ID) t2
CROSS APPLY (select max(Date_time) as 'maxDate' from name_log nl
WHERE t.st_ID=t1.ID)t3[/code]

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 12:11:30

It is returning two errors

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 3
Invalid column name 'Date_Time'.

DZ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:22:58

quote:
Originally posted by dzabor

It is returning two errors

Which one?
Mine or Visakh's?

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 12:23:24

Peso - It worked I just changed the last table to Name_log. It is returning ID, MaxActivityDate,TransDate,LogDate for each distinct record. That is very cool. We are almost there!

I now need it to give return the max of the three columns for each ID. It should show only ID, Max of all dates.

Thanks,
Dz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:25:32

Even simpler!

SELECT		p.ID,
		MAX(p.theDate) AS MaxDate
FROM		(
			SELECT	ID,
				Transaction_Date AS theDate
			FROM	Activity

			UNION ALL

			SELECT	st_ID,
				Transaction_Date
			FROM	Trans

			UNION ALL

			SELECT	ID,
				Date_Time
			FROM	Name_Log
		) AS p
GROUP BY	p.ID

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 12:31:55

That worked!!! Both of these scripts are going to be useful. :)

Thanks! You rock!

DZ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:33:34

If you want to combine the two queries, do like this

SELECT		p.ID,
		MAX(p.ActivityDate) AS ActivityDate,
		MAX(p.TransDate) AS TransDate,
		MAX(p.LogDate) AS LogDate,
		MAX(p.theDate) AS MaxDate
FROM		(
			SELECT	ID,
				Transaction_Date AS ActivityDate,
				CAST(NULL AS DATETIME) AS TransDate,
				CAST(NULL AS DATETIME) AS LogDate,
				Transaction_Date AS theDate
			FROM	Activity

			UNION ALL

			SELECT	st_ID,
				NULL,
				Transaction_Date,
				NULL,
				Transaction_Date
			FROM	Trans

			UNION ALL

			SELECT	ID,
				NULL,
				NULL,
				Date_Time,
				Date_Time
			FROM	Name_Log
		) AS p
GROUP BY	p.ID

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 12:39:34

That makes sense - I like that one. How would I add the following criteria:

where maxDate > '12/31/2005'

DZ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:43:13

After each and one SELECT statement in the derived table, like this

SELECT		p.ID,
		MAX(p.ActivityDate) AS ActivityDate,
		MAX(p.TransDate) AS TransDate,
		MAX(p.LogDate) AS LogDate,
		MAX(p.theDate) AS MaxDate
FROM		(
			SELECT	ID,
				Transaction_Date AS ActivityDate,
				CAST(NULL AS DATETIME) AS TransDate,
				CAST(NULL AS DATETIME) AS LogDate,
				Transaction_Date AS theDate
			FROM	Activity
			WHERE	Transaction_Date >= '20060101'

			UNION ALL

			SELECT	st_ID,
				NULL,
				Transaction_Date,
				NULL,
				Transaction_Date
			FROM	Trans
			WHERE	Transaction_Date >= '20060101'

			UNION ALL

			SELECT	ID,
				NULL,
				NULL,
				Date_Time,
				Date_Time
			FROM	Name_Log
			WHERE	Date_Time >= '20060101'
		) AS p
GROUP BY	p.ID

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 13:30:08

khtan - Because of the different date formats it was return up to two rows per records. One for each format. But thanks for your help.

Peso - that worked like a charm! Thanks!!!

Thanks to both of you for helping. I really learned a lot.

dzabor
Posting Yak Master

138 Posts

Posted - 2007-12-19 : 13:56:00

Question. I am trying to learn from what you are helping me with, so I have been playing with the syntax and adding another date field, but it keeps returning an error:

Server: Msg 8157, Level 16, State 1, Line 1
All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists.

SELECT p.ID,
MAX(p.ActivityDate) AS ActivityDate,
MAX(p.TransDate) AS TransDate,
MAX(p.LogDate) AS LogDate,
MAX(p.PaidDate) AS PaidDate,
MAX(p.theDate) AS MaxDate
FROM (
SELECT ID,
Transaction_Date AS ActivityDate,
CAST(NULL AS DATETIME) AS TransDate,
CAST(NULL AS DATETIME) AS LogDate,
CAST(NULL AS DATETIME) AS PaidDate,
Transaction_Date AS theDate
FROM Activity
WHERE Transaction_Date <= '20050101'

UNION ALL

SELECT st_ID,
NULL,
Transaction_Date,
NULL,
Transaction_Date,
NULL,
Transaction_Date
FROM Trans
WHERE Transaction_Date <= '20050101'

UNION ALL

SELECT ID,
NULL,
NULL,
NULL,
Date_Time,
Date_Time,
Date_Time
FROM Name_Log
WHERE Date_Time <= '20050101'

UNION ALL

SELECT ID,
NULL,
Paid_Thru,
NULL,
Paid_Thru,
NULL,
Paid_Thru
FROM Name
WHERE Paid_Thru <= '20050101'

) AS p
GROUP BY p.ID

Can you tell me what I am doing wrong?

DZ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 14:46:07

The first and second UNION ALL SELECT statement have more columns than the first SELECT.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources