Return column names where... - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Return column names where...

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2008-02-12 : 05:10:12

Hi All,

I have a query where I want to return the name of column that has the most recent date stored, for each reference. Below is an example.

REF-----------Process 1---------Process 2--------- Process 3-----

1234------------NULL--------------NULL-------------02/12/2008---
2314-----------02/10/2008--------02/12/2008----------NULL--------

What I want to achieve in my query result is like this (except I have about 12 Process columns):

Ref--------Stage

1234-----Process 3
2314-----Process 2

Any help much appreciated.

Thanks
Humate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 05:57:09

[code]

declare @t table
(
REF int,
Process1 datetime,
Process2 datetime,
Process3 datetime
)
INSERT INTO @t VALUES (1234,NULL,NULL,'02/12/2008')
INSERT INTO @t VALUES (2314,'02/10/2008','02/12/2008',NULL)
INSERT INTO @t VALUES (2114,'02/10/2008','05/12/2008',NULL)
INSERT INTO @t VALUES (2315,'11/15/2008','02/12/2008',NULL)
--
SELECT t1.REF,MAX(Value) AS MaxDate
FROM @t t1
CROSS APPLY
(

SELECT 'Process1' AS [Column],Process1 AS [Value]
FROM @t
WHERE REF=t1.REF
UNION ALL
SELECT 'Process2' AS [Column],Process2 AS [Value]
FROM @t
WHERE REF=t1.REF
UNION ALL
SELECT 'Process3' AS [Column],Process3 AS [Value]
FROM @t
WHERE REF=t1.REF
)t
GROUP BY t1.REF

output
-----------

REF MaxDate
----------- -----------------------
1234 2008-02-12 00:00:00.000
2114 2008-05-12 00:00:00.000
2314 2008-02-12 00:00:00.000
2315 2008-11-15 00:00:00.000[/code]

Humate
Posting Yak Master

101 Posts

Posted - 2008-02-12 : 06:03:06

Thanks for the help - this will be useful. I was wondering though if I could return the column name of "maxdate", rather than the date value i.e.

Ref--------Stage

1234-----Process 3
2314-----Process 2

instead of:

Ref--------Stage

1234-----2008/02/12
2314-----2008/02/12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 06:25:12

[code]

declare @t table
(
REF int,
Process1 datetime,
Process2 datetime,
Process3 datetime
)
INSERT INTO @t VALUES (1234,NULL,NULL,'02/12/2008')
INSERT INTO @t VALUES (2314,'02/10/2008','02/12/2008',NULL)
INSERT INTO @t VALUES (2114,'02/10/2008','05/12/2008',NULL)
INSERT INTO @t VALUES (2315,'11/15/2008','02/12/2008',NULL)
;
WIth CTE (REF,Col,Val) AS
(
SELECT t1.REF,[Column],[Value]
FROM @t t1
CROSS APPLY
(

SELECT 'Process1' AS [Column],Process1 AS [Value]
FROM @t
WHERE REF=t1.REF
UNION ALL
SELECT 'Process2' AS [Column],Process2 AS [Value]
FROM @t
WHERE REF=t1.REF
UNION ALL
SELECT 'Process3' AS [Column],Process3 AS [Value]
FROM @t
WHERE REF=t1.REF
)t
)
SELECT REF,Col FROM(
SELECT ROW_NUMBER() OVER (PARTITION BY REF Order BY Val DESC) RowNo,
REF,
Col
FROM CTE)t
WHERE t.RowNo=1

output
----------
REF Col
----------- --------
1234 Process3
2114 Process2
2314 Process2
2315 Process1[/code]

Humate
Posting Yak Master

101 Posts

Posted - 2008-02-12 : 08:14:22

Thanks! That does the trick nicely on a small set of test data. I have tried running on my full data (~300,000 rows), and the query takes so long it times out - is that something associated with using CTE or Cross Apply i.e. they are resource intensive?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-12 : 09:22:31

Try this

DECLARE @t TABLE
(
	REF 		int,
	Process1 	datetime,
	Process2 	datetime,
	Process3 	datetime
)
INSERT INTO @t VALUES (1234,NULL,NULL,'02/12/2008')
INSERT INTO @t VALUES (2314,'02/10/2008','02/12/2008',NULL)
INSERT INTO @t VALUES (2114,'02/10/2008','05/12/2008',NULL)
INSERT INTO @t VALUES (2315,'11/15/2008','02/12/2008',NULL)

SELECT	REF, process
FROM
(
	SELECT	REF, process, val, row_no = row_number() OVER (PARTITION BY REF ORDER BY REF, val DESC)
	FROM
	(
		SELECT	REF, process = 'Process1', val = Process1 FROM @t WHERE Process1 IS NOT NULL
		UNION ALL
		SELECT	REF, process = 'Process2', val = Process2 FROM @t WHERE Process2 IS NOT NULL
		UNION ALL
		SELECT	REF, process = 'Process3', val = Process3 FROM @t WHERE Process3 IS NOT NULL
	) d
) d
WHERE	row_no	= 1
/*
REF         process  
----------- -------- 
       1234 Process3
       2114 Process2
       2314 Process2
       2315 Process1

(4 row(s) affected)
*/

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

Humate
Posting Yak Master

101 Posts

Posted - 2008-02-12 : 10:54:23

Great!

That query worked faster (~2 mins). Thanks both for your help on this :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:07:35

Try this too!

DECLARE @Sample TABLE (Ref INT, Process1 DATETIME, Process2 DATETIME, Process3 DATETIME)

INSERT	@Sample
SELECT	1234, NULL, NULL, '02/12/2008' UNION ALL
SELECT	2314, '02/10/2008', '02/12/2008', NULL UNION ALL
SELECT	2114, '02/10/2008', '05/12/2008', NULL UNION ALL
SELECT	2315, '11/15/2008', '02/12/2008', NULL

SELECT		p.Ref,
		MAX(p.theDate) AS maxDate
FROM		@Sample AS s
UNPIVOT		(
			theDate
			FOR	theCol IN (s.Process1, s.Process2, s.Process3)
		) AS p
GROUP BY	p.Ref
ORDER BY	p.Ref

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:10:32

Oops. New requirements...

DECLARE @Sample TABLE (Ref INT, Process1 DATETIME, Process2 DATETIME, Process3 DATETIME)

INSERT	@Sample
SELECT	1234, NULL, NULL, '02/12/2008' UNION ALL
SELECT	2314, '02/10/2008', '02/12/2008', NULL UNION ALL
SELECT	2114, '02/10/2008', '05/12/2008', NULL UNION ALL
SELECT	2315, '11/15/2008', '02/12/2008', NULL

SELECT		u.Ref,
		u.theCol,
		u.theDate
FROM		(
			SELECT		p.Ref,
					ROW_NUMBER() OVER (PARTITION BY p.Ref ORDER BY p.theDate DESC) AS RecID,
					p.theCol,
					p.theDate
			FROM		@Sample AS s
			UNPIVOT		(
						theDate
						FOR	theCol IN (s.Process1, s.Process2, s.Process3)
					) AS p
		) AS u
WHERE		u.RecID = 1
ORDER BY	u.Ref

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:11:54

When you say 2 minutes is faster, how long time did the query originally take?
And how long time do my second suggestion take?

Peso		 3 reads
Visakh16	39 reads
Khtan		 9 reads

If Khtans suggestion takes about 2 minutes, my suggestion should take about 40-45 seconds.

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

Humate
Posting Yak Master

101 Posts

Posted - 2008-02-12 : 15:58:51

Interesting... 53 Seconds now! Very impressive, and thankyou.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-12 : 16:21:22

Here is another approach:


declare @t table
(
REF int,
Process1 datetime,
Process2 datetime,
Process3 datetime
)

insert into @t values (1234,null,null,'02/12/2008')
insert into @t values (1235,null,'02/12/2008','02/10/2008')
insert into @t values (1236,null,'02/11/2008','02/13/2008')
insert into @t values (2114,'02/10/2008','05/12/2008',null)
insert into @t values (2314,'02/12/2008','02/10/2008',null)
insert into @t values (2315,'11/15/2008','02/12/2008',null)
insert into @t values (2316,null,null,null)

select
	a.REF,
	Min_Col =
	(
  	select top 1
   		bb.xx
  	from
   		(
   		select xx = 'Process1', val = a.Process1	union all
   		select xx = 'Process2', val = a.Process2	union all
   		select xx = 'Process3', val = a.Process3
   		) bb
	where
		bb.val is not null
	order by
		bb.val desc
  	)
from
 	@t a
order by
	a.REF

Results:

REF         Min_Col  
----------- -------- 
1234        Process3
1235        Process2
1236        Process3
2114        Process2
2314        Process1
2315        Process1
2316        NULL

(7 row(s) affected)

Edit: I tested my method with 300,000 rows of random datetimes, and had an elapsed time of under 9 seconds. I didn't test other methods.



create table #t
(
REF int,
Process1 datetime,
Process2 datetime,
Process3 datetime
)

insert into #t
select
	number,
	dbo.F_RANDOM_DATETIME(1,1000,newid()),
	dbo.F_RANDOM_DATETIME(1,1000,newid()),
	dbo.F_RANDOM_DATETIME(1,1000,newid())
from
	F_TABLE_NUMBER_RANGE(1,300000)



declare @t table
(
REF int,
Min_Col varchar(8)
)

declare @st datetime
set @st = getdate()

insert into @t
select
	a.REF,
	Min_Col =
	(
  	select top 1
   		bb.xx
  	from
   		(
   		select xx = 'Process1', val = a.Process1	union all
   		select xx = 'Process2', val = a.Process2	union all
   		select xx = 'Process3', val = a.Process3
   		) bb
	where
		bb.val is not null
	order by
		bb.val desc
  	)
from
 	#t a
order by
	a.REF

select ET = getdate()-@st

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 16:41:10

Excellent! This also has the advantage of retrieving records with all NULL process dates.

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

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-12 : 16:46:15

Ok, I came up with a solution too. It is not very pretty, but it does 400k in 5s.


--Create working table
Drop Table #ProcessDates
Create Table #ProcessDates
(
	Ref INT identity(1, 1), 
	Process1 DATETIME, Process2 DATETIME, Process3 DATETIME,
	Process4 DATETIME, Process5 DATETIME, Process6 DATETIME,
	Process7 DATETIME, Process8 DATETIME, Process9 DATETIME,
	Process10 DATETIME, Process11 DATETIME, Process12 DATETIME
)

--Simulate LOTS of data
Declare @cnt int
Set @cnt = 0
While @cnt < 100000 Begin
	INSERT Into #ProcessDates
	SELECT NULL, NULL, '02/12/2008', 
		'01/12/2008','05/12/2008','04/12/2008',
		'02/14/2008','05/02/2008','07/12/2008',
		'02/19/2008','02/22/2008','09/12/2008'
	INSERT Into #ProcessDates
	SELECT NULL, NULL, '02/12/2008', 
		'01/12/2008',NULL,'04/12/2008',
		'02/14/2008','05/12/2008',NULL,
		'02/19/2008',NULL,'09/12/2008'
	INSERT Into #ProcessDates
	SELECT NULL, '8/22/2008', '02/12/2008', 
		'01/12/2008','05/12/2008','04/12/2008',
		'02/14/2008',NULL,'07/12/2008',
		'02/19/2008','02/22/2008',NULL
	INSERT Into #ProcessDates
	SELECT '9/25/2008', NULL, '02/12/2008', 
		'01/12/2008',NULL,NULL,
		NULL,'05/12/2008',NULL,
		'02/19/2008','02/22/2008',NULL

	Set @cnt = @cnt + 1
End


--Solution; Not pretty, not easily added to, but FAST
Select ref,
Case
	When Process1 Is Not Null 
	And	Process1 > IsNull(Process2, 0) 	And	Process1 > IsNull(Process3, 0) 
	And	Process1 > IsNull(Process4, 0) 	And	Process1 > IsNull(Process5, 0) 
	And	Process1 > IsNull(Process6, 0) 	And	Process1 > IsNull(Process7, 0) 
	And	Process1 > IsNull(Process8, 0) 	And	Process1 > IsNull(Process9, 0) 
	And	Process1 > IsNull(Process10, 0)	And	Process1 > IsNull(Process11, 0) 
	And	Process1 > IsNull(Process12, 0)	Then 'Process1'
	When Process2 Is Not Null 
	And	Process2 > IsNull(Process1, 0) 	And	Process2 > IsNull(Process3, 0) 
	And	Process2 > IsNull(Process4, 0) 	And	Process2 > IsNull(Process5, 0) 
	And	Process2 > IsNull(Process6, 0) 	And	Process2 > IsNull(Process7, 0) 
	And	Process2 > IsNull(Process8, 0) 	And	Process2 > IsNull(Process9, 0) 
	And	Process2 > IsNull(Process10, 0)	And	Process2 > IsNull(Process11, 0) 
	And	Process2 > IsNull(Process12, 0)	Then 'Process2'
	When Process3 Is Not Null 
	And	Process3 > IsNull(Process1, 0) 	And	Process3 > IsNull(Process2, 0) 
	And	Process3 > IsNull(Process4, 0) 	And	Process3 > IsNull(Process5, 0) 
	And	Process3 > IsNull(Process6, 0) 	And	Process3 > IsNull(Process7, 0) 
	And	Process3 > IsNull(Process8, 0) 	And	Process3 > IsNull(Process9, 0) 
	And	Process3 > IsNull(Process10, 0)	And	Process3 > IsNull(Process11, 0) 
	And	Process3 > IsNull(Process12, 0)	Then 'Process3'
	When Process4 Is Not Null 
	And	Process4 > IsNull(Process1, 0) 	And	Process4 > IsNull(Process2, 0) 
	And	Process4 > IsNull(Process3, 0) 	And	Process4 > IsNull(Process5, 0) 
	And	Process4 > IsNull(Process6, 0) 	And	Process4 > IsNull(Process7, 0) 
	And	Process4 > IsNull(Process8, 0) 	And	Process4 > IsNull(Process9, 0) 
	And	Process4 > IsNull(Process10, 0)	And	Process4 > IsNull(Process11, 0) 
	And	Process4 > IsNull(Process12, 0)	Then 'Process4'
	When Process5 Is Not Null 
	And	Process5 > IsNull(Process1, 0) 	And	Process5 > IsNull(Process2, 0) 
	And	Process5 > IsNull(Process3, 0) 	And	Process5 > IsNull(Process4, 0) 
	And	Process5 > IsNull(Process6, 0) 	And	Process5 > IsNull(Process7, 0) 
	And	Process5 > IsNull(Process8, 0) 	And	Process5 > IsNull(Process9, 0) 
	And	Process5 > IsNull(Process10, 0)	And	Process5 > IsNull(Process11, 0) 
	And	Process5 > IsNull(Process12, 0)	Then 'Process5'
	When Process6 Is Not Null 
	And	Process6 > IsNull(Process1, 0) 	And	Process6 > IsNull(Process2, 0) 
	And	Process6 > IsNull(Process3, 0) 	And	Process6 > IsNull(Process4, 0) 
	And	Process6 > IsNull(Process5, 0) 	And	Process6 > IsNull(Process7, 0) 
	And	Process6 > IsNull(Process8, 0) 	And	Process6 > IsNull(Process9, 0) 
	And	Process6 > IsNull(Process10, 0)	And	Process6 > IsNull(Process11, 0) 
	And	Process6 > IsNull(Process12, 0)	Then 'Process6'
	When Process7 Is Not Null 
	And	Process7 > IsNull(Process1, 0) 	And	Process7 > IsNull(Process2, 0) 
	And	Process7 > IsNull(Process3, 0) 	And	Process7 > IsNull(Process4, 0) 
	And	Process7 > IsNull(Process5, 0) 	And	Process7 > IsNull(Process6, 0) 
	And	Process7 > IsNull(Process8, 0) 	And	Process7 > IsNull(Process9, 0) 
	And	Process7 > IsNull(Process10, 0)	And	Process7 > IsNull(Process11, 0) 
	And	Process7 > IsNull(Process12, 0)	Then 'Process7'
	When Process8 Is Not Null 
	And	Process8 > IsNull(Process1, 0) 	And	Process8 > IsNull(Process2, 0) 
	And	Process8 > IsNull(Process3, 0) 	And	Process8 > IsNull(Process4, 0) 
	And	Process8 > IsNull(Process5, 0) 	And	Process8 > IsNull(Process6, 0) 
	And	Process8 > IsNull(Process7, 0) 	And	Process8 > IsNull(Process9, 0) 
	And	Process8 > IsNull(Process10, 0)	And	Process8 > IsNull(Process11, 0) 
	And	Process8 > IsNull(Process12, 0)	Then 'Process8'
	When Process9 Is Not Null 
	And	Process9 > IsNull(Process1, 0) 	And	Process9 > IsNull(Process2, 0) 
	And	Process9 > IsNull(Process3, 0) 	And	Process9 > IsNull(Process4, 0) 
	And	Process9 > IsNull(Process5, 0) 	And	Process9 > IsNull(Process6, 0) 
	And	Process9 > IsNull(Process7, 0) 	And	Process9 > IsNull(Process8, 0) 
	And	Process9 > IsNull(Process10, 0)	And	Process9 > IsNull(Process11, 0) 
	And	Process9 > IsNull(Process12, 0)	Then 'Process9'
	When Process10 Is Not Null 
	And	Process10 > IsNull(Process1, 0) And Process10 > IsNull(Process2, 0) 
	And	Process10 > IsNull(Process3, 0) And Process10 > IsNull(Process4, 0) 
	And	Process10 > IsNull(Process5, 0) And Process10 > IsNull(Process6, 0) 
	And	Process10 > IsNull(Process7, 0) And Process10 > IsNull(Process8, 0) 
	And	Process10 > IsNull(Process9, 0) And Process10 > IsNull(Process11, 0) 
	And	Process10 > IsNull(Process12, 0) Then 'Process10'
	When Process11 Is Not Null 
	And	Process11 > IsNull(Process1, 0) And Process11 > IsNull(Process2, 0) 
	And	Process11 > IsNull(Process3, 0) And Process11 > IsNull(Process4, 0) 
	And	Process11 > IsNull(Process5, 0) And Process11 > IsNull(Process6, 0) 
	And	Process11 > IsNull(Process7, 0) And Process11 > IsNull(Process8, 0) 
	And	Process11 > IsNull(Process9, 0) And Process11 > IsNull(Process10, 0) 
	And	Process11 > IsNull(Process12, 0) Then 'Process11'
	When Process12 Is Not Null 
	And	Process12 > IsNull(Process1, 0) And Process12 > IsNull(Process2, 0) 
	And	Process12 > IsNull(Process3, 0) And Process12 > IsNull(Process4, 0) 
	And	Process12 > IsNull(Process5, 0) And Process12 > IsNull(Process6, 0) 
	And	Process12 > IsNull(Process7, 0) And Process12 > IsNull(Process8, 0) 
	And	Process12 > IsNull(Process9, 0) And Process12 > IsNull(Process10, 0) 
	And	Process12 > IsNull(Process11, 0) Then 'Process12'
End
From #ProcessDates

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 16:50:30

Until some date actually is older than "0" which means January 1, 1900.

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-12 : 16:55:04

This whole topic is a variation of this:
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

Qualis:
I didn't test your solution, but I don't think you allowed for cases where the dates are equal or the dates are before 1900-01-01. Your solution is similar to the Method 2 that I posted in the link above. It does have a speed advantage over Method 1, but is a bit more work to code and test.

If you need the speed of Method 2, you can still use the simpler Method 1 code to test the output from Method 2.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 17:05:28

I did some tests with the sample data provided by Qualis.

-- Qualis
Select ref,
Case
	When Process1 Is Not Null 
	And	Process1 > IsNull(Process2, 0) 	And	Process1 > IsNull(Process3, 0) 
	And	Process1 > IsNull(Process4, 0) 	And	Process1 > IsNull(Process5, 0) 
	And	Process1 > IsNull(Process6, 0) 	And	Process1 > IsNull(Process7, 0) 
	And	Process1 > IsNull(Process8, 0) 	And	Process1 > IsNull(Process9, 0) 
	And	Process1 > IsNull(Process10, 0)	And	Process1 > IsNull(Process11, 0) 
	And	Process1 > IsNull(Process12, 0)	Then 'Process1'
	When Process2 Is Not Null 
	And	Process2 > IsNull(Process1, 0) 	And	Process2 > IsNull(Process3, 0) 
	And	Process2 > IsNull(Process4, 0) 	And	Process2 > IsNull(Process5, 0) 
	And	Process2 > IsNull(Process6, 0) 	And	Process2 > IsNull(Process7, 0) 
	And	Process2 > IsNull(Process8, 0) 	And	Process2 > IsNull(Process9, 0) 
	And	Process2 > IsNull(Process10, 0)	And	Process2 > IsNull(Process11, 0) 
	And	Process2 > IsNull(Process12, 0)	Then 'Process2'
	When Process3 Is Not Null 
	And	Process3 > IsNull(Process1, 0) 	And	Process3 > IsNull(Process2, 0) 
	And	Process3 > IsNull(Process4, 0) 	And	Process3 > IsNull(Process5, 0) 
	And	Process3 > IsNull(Process6, 0) 	And	Process3 > IsNull(Process7, 0) 
	And	Process3 > IsNull(Process8, 0) 	And	Process3 > IsNull(Process9, 0) 
	And	Process3 > IsNull(Process10, 0)	And	Process3 > IsNull(Process11, 0) 
	And	Process3 > IsNull(Process12, 0)	Then 'Process3'
	When Process4 Is Not Null 
	And	Process4 > IsNull(Process1, 0) 	And	Process4 > IsNull(Process2, 0) 
	And	Process4 > IsNull(Process3, 0) 	And	Process4 > IsNull(Process5, 0) 
	And	Process4 > IsNull(Process6, 0) 	And	Process4 > IsNull(Process7, 0) 
	And	Process4 > IsNull(Process8, 0) 	And	Process4 > IsNull(Process9, 0) 
	And	Process4 > IsNull(Process10, 0)	And	Process4 > IsNull(Process11, 0) 
	And	Process4 > IsNull(Process12, 0)	Then 'Process4'
	When Process5 Is Not Null 
	And	Process5 > IsNull(Process1, 0) 	And	Process5 > IsNull(Process2, 0) 
	And	Process5 > IsNull(Process3, 0) 	And	Process5 > IsNull(Process4, 0) 
	And	Process5 > IsNull(Process6, 0) 	And	Process5 > IsNull(Process7, 0) 
	And	Process5 > IsNull(Process8, 0) 	And	Process5 > IsNull(Process9, 0) 
	And	Process5 > IsNull(Process10, 0)	And	Process5 > IsNull(Process11, 0) 
	And	Process5 > IsNull(Process12, 0)	Then 'Process5'
	When Process6 Is Not Null 
	And	Process6 > IsNull(Process1, 0) 	And	Process6 > IsNull(Process2, 0) 
	And	Process6 > IsNull(Process3, 0) 	And	Process6 > IsNull(Process4, 0) 
	And	Process6 > IsNull(Process5, 0) 	And	Process6 > IsNull(Process7, 0) 
	And	Process6 > IsNull(Process8, 0) 	And	Process6 > IsNull(Process9, 0) 
	And	Process6 > IsNull(Process10, 0)	And	Process6 > IsNull(Process11, 0) 
	And	Process6 > IsNull(Process12, 0)	Then 'Process6'
	When Process7 Is Not Null 
	And	Process7 > IsNull(Process1, 0) 	And	Process7 > IsNull(Process2, 0) 
	And	Process7 > IsNull(Process3, 0) 	And	Process7 > IsNull(Process4, 0) 
	And	Process7 > IsNull(Process5, 0) 	And	Process7 > IsNull(Process6, 0) 
	And	Process7 > IsNull(Process8, 0) 	And	Process7 > IsNull(Process9, 0) 
	And	Process7 > IsNull(Process10, 0)	And	Process7 > IsNull(Process11, 0) 
	And	Process7 > IsNull(Process12, 0)	Then 'Process7'
	When Process8 Is Not Null 
	And	Process8 > IsNull(Process1, 0) 	And	Process8 > IsNull(Process2, 0) 
	And	Process8 > IsNull(Process3, 0) 	And	Process8 > IsNull(Process4, 0) 
	And	Process8 > IsNull(Process5, 0) 	And	Process8 > IsNull(Process6, 0) 
	And	Process8 > IsNull(Process7, 0) 	And	Process8 > IsNull(Process9, 0) 
	And	Process8 > IsNull(Process10, 0)	And	Process8 > IsNull(Process11, 0) 
	And	Process8 > IsNull(Process12, 0)	Then 'Process8'
	When Process9 Is Not Null 
	And	Process9 > IsNull(Process1, 0) 	And	Process9 > IsNull(Process2, 0) 
	And	Process9 > IsNull(Process3, 0) 	And	Process9 > IsNull(Process4, 0) 
	And	Process9 > IsNull(Process5, 0) 	And	Process9 > IsNull(Process6, 0) 
	And	Process9 > IsNull(Process7, 0) 	And	Process9 > IsNull(Process8, 0) 
	And	Process9 > IsNull(Process10, 0)	And	Process9 > IsNull(Process11, 0) 
	And	Process9 > IsNull(Process12, 0)	Then 'Process9'
	When Process10 Is Not Null 
	And	Process10 > IsNull(Process1, 0) And Process10 > IsNull(Process2, 0) 
	And	Process10 > IsNull(Process3, 0) And Process10 > IsNull(Process4, 0) 
	And	Process10 > IsNull(Process5, 0) And Process10 > IsNull(Process6, 0) 
	And	Process10 > IsNull(Process7, 0) And Process10 > IsNull(Process8, 0) 
	And	Process10 > IsNull(Process9, 0) And Process10 > IsNull(Process11, 0) 
	And	Process10 > IsNull(Process12, 0) Then 'Process10'
	When Process11 Is Not Null 
	And	Process11 > IsNull(Process1, 0) And Process11 > IsNull(Process2, 0) 
	And	Process11 > IsNull(Process3, 0) And Process11 > IsNull(Process4, 0) 
	And	Process11 > IsNull(Process5, 0) And Process11 > IsNull(Process6, 0) 
	And	Process11 > IsNull(Process7, 0) And Process11 > IsNull(Process8, 0) 
	And	Process11 > IsNull(Process9, 0) And Process11 > IsNull(Process10, 0) 
	And	Process11 > IsNull(Process12, 0) Then 'Process11'
	When Process12 Is Not Null 
	And	Process12 > IsNull(Process1, 0) And Process12 > IsNull(Process2, 0) 
	And	Process12 > IsNull(Process3, 0) And Process12 > IsNull(Process4, 0) 
	And	Process12 > IsNull(Process5, 0) And Process12 > IsNull(Process6, 0) 
	And	Process12 > IsNull(Process7, 0) And Process12 > IsNull(Process8, 0) 
	And	Process12 > IsNull(Process9, 0) And Process12 > IsNull(Process10, 0) 
	And	Process12 > IsNull(Process11, 0) Then 'Process12'
End
From #ProcessDates
order by ref

-- MVJ 1
select
	a.REF,
	Min_Col =
	(
  	select top 1
   		bb.xx
  	from
   		(
   		select xx = 'Process1', val = a.Process1	union all
   		select xx = 'Process2', val = a.Process2	union all
   		select xx = 'Process3', val = a.Process3	union all
   		select xx = 'Process4', val = a.Process4	union all
   		select xx = 'Process5', val = a.Process5	union all
   		select xx = 'Process6', val = a.Process6	union all
   		select xx = 'Process7', val = a.Process7	union all
   		select xx = 'Process8', val = a.Process8	union all
   		select xx = 'Process9', val = a.Process9	union all
   		select xx = 'Process10', val = a.Process10	union all
   		select xx = 'Process11', val = a.Process11	union all
   		select xx = 'Process12', val = a.Process12
   		) bb
	where
		bb.val is not null
	order by
		bb.val desc
  	)
from
 	#ProcessDates a
order by
	a.REF

-- MVJ 2 (edited by peso with removal of where)
select
	a.REF,
	Min_Col =
	(
  	select top 1
   		bb.xx
  	from
   		(
   		select xx = 'Process1', val = a.Process1	union all
   		select xx = 'Process2', val = a.Process2	union all
   		select xx = 'Process3', val = a.Process3	union all
   		select xx = 'Process4', val = a.Process4	union all
   		select xx = 'Process5', val = a.Process5	union all
   		select xx = 'Process6', val = a.Process6	union all
   		select xx = 'Process7', val = a.Process7	union all
   		select xx = 'Process8', val = a.Process8	union all
   		select xx = 'Process9', val = a.Process9	union all
   		select xx = 'Process10', val = a.Process10	union all
   		select xx = 'Process11', val = a.Process11	union all
   		select xx = 'Process12', val = a.Process12
   		) bb
	order by
		bb.val desc
  	)
from
 	#ProcessDates a
order by
	a.REF

-- Peso
SELECT		u.Ref,
		u.theCol,
		u.theDate
FROM		(
			SELECT		p.Ref,
					ROW_NUMBER() OVER (PARTITION BY p.Ref ORDER BY p.theDate DESC) AS RecID,
					p.theCol,
					p.theDate
			FROM		#ProcessDates AS s
			UNPIVOT		(
						theDate
						FOR	theCol IN (s.Process1, s.Process2, s.Process3, s.Process4,
							s.Process5, s.Process6, s.Process7, s.Process8,
							s.Process9, s.Process10, s.Process11, s.Process12)
					) AS p
		) AS u
WHERE		u.RecID = 1
ORDER BY	u.Ref

And the timings according to profiler is

Name	CPU	Reads
Qualis 	 2469	 6625
MVJ1	 4796	10493
MVJ2	 1734	 6529
Peso	13157	20429

My solution do not scale well.

And the same test again but without the last ORDER BY

Name	CPU	Reads
Qualis	 1921	 5719
MVJ1	 3750	 9817
MVJ2	 1454	 5720
Peso	11797	18718

I would go for MVJ2 approach.
Less code to maintain and the speediest of them.

EDIT: Added linebreaks.

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-12 : 17:18:06

Interesting results. I think that the difference between my method and Qualis method can change, depending on the number of rows in the table.

Basically, Qualis method (Method 2) takes much long to generate a query plan, so it does not perform as well on smaller sets of data, but when it gets into the millions of rows it performs much better then my method (Method 1). Of course, I didn't test this.

CODO ERGO SUM

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-12 : 17:23:03

I would also go with MVJ2 because of maintainability. The speed increase is marginal. I'm gonna run some more tests with more data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 17:23:08

I edited the timings, because I only dealt with three columns. I forgot to scale my suggestion to 12 columns.
All tests were made with qualis example for 400000 records.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 17:26:59

Humate, test the MVJ2 approach to your data and post back timings here.
The time should drop from 53 seconds to about 25-30 seconds.

How much time did your original code take?

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources