row scanning - 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 2000 Forums

Transact-SQL (2000)

row scanning

Author

Topic

oompa
Starting Member

5 Posts

Posted - 2005-08-10 : 16:11:47

I'm trying to scan a row of dates for the max date. Then, using that date, to get the corresponding code. Any ideas on the best way to do this? Thx.

CREATE TABLE [dbo].[xTest] (
[XDate1] [datetime] NULL ,
[XDate2] [datetime] NULL ,
[XDate3] [datetime] NULL ,
[XCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

--dummy data
INSERT INTO xTest (XDate1, XDate2, XDate3, XCode1, XCode2, XCode3)
VALUES ('1/1/2005', '2/1/2005', '3/1/2005', 'A', 'Z', 'E')

INSERT INTO xTest (XDate1, XDate2, XDate3, XCode1, XCode2, XCode3)
VALUES ('10/1/2005', '2/1/2005', '3/1/2005', 'A', 'Z', 'E')

INSERT INTO xTest (XDate1, XDate2, XDate3, XCode1, XCode2, XCode3)
VALUES ('1/1/2005', '12/1/2005', '3/1/2005', 'A', 'Z', 'E')

--desired results

Date Code
3/1/2005 E
10/1/2005 A
12/1/2005 Z

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-10 : 16:49:38

Hi there. Welcome to SQLTeam.

Can you please post some DDL and sample data /desired results.

Thanks!

Here are some guidelines:
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Nathan Skerl

oompa
Starting Member

5 Posts

Posted - 2005-08-10 : 18:57:44

Nathan

Thx for the code. However, I'm looking for something that scans each row of data then pulls out the max date. I think your solution does a column searche. I'm trying to avoid using a cursor, but, it looks like I have to use it.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-10 : 19:41:33

This should do it:

select
	[Date] =
		case [ColNum]
		when 1 then [XDate1]
		when 2 then [XDate2]
		when 3 then [XDate3]
		else null
		end,
	[Code] =
		case [ColNum]
		when 1 then [XCode1]
		when 2 then [XCode2]
		when 3 then [XCode3]
		else null
		end
from
	(

	select
		[ColNum] =
		case
		when	[XDate1] is not null and 
			( [XDate1] > [XDate2] or [XDate2] is null ) and
			( [XDate1] > [XDate3] or [XDate3] is null )
		then 	1
		when	[XDate2] is not null and 
			( [XDate2] > [XDate3] or [XDate3] is null )
		then 	2
		when 	[XDate3] is not null
		then	3
		else	4
		end, 
		[XDate1],
		[XDate2],
		[XDate3],
		[XCode1],
		[XCode2],
		[xCode4]
	from
		 [dbo].[xTest]
	) a

It is a lot harder than it needs to be, since you allow nulls in the columns. Also, this whole problem is due to the fact that the table is not in first normal form.

CODO ERGO SUM

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-10 : 20:46:15

Here is another way to do it. I guess it's debatable whether this method is easier or not, but it's an alternative:

--Create a temp table with an identity column
CREATE TABLE #tmp_xtest (
row int identity(1,1) not null,
[XDate1] [datetime] NULL ,
[XDate2] [datetime] NULL ,
[XDate3] [datetime] NULL ,
[XCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xCode3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

--Populate temp table with xtest data
INSERT INTO #tmp_xtest(xdate1, xdate2, xdate3, xcode1, xcode2, xcode3)
SELECT * FROM xtest

--"Pivot" data into another temp table
SELECT row, xdate1 AS xdate, xcode1 AS xcode
INTO #tmp_xtest_pivot
FROM #tmp_xtest
UNION ALL
SELECT row, xdate2 AS xdate, xcode2 AS xcode
FROM #tmp_xtest
UNION ALL
SELECT row, xdate3 AS xdate, xcode3 AS xcode
FROM #tmp_xtest

--Get final result
SELECT A.row, B.xdate, A.xcode
FROM #tmp_xtest_pivot A
JOIN
(SELECT row, MAX(xdate) AS xdate
FROM #tmp_xtest_pivot
GROUP BY row) AS B
ON A.row = B.row AND A.xdate = B.xdate
ORDER BY A.row

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources