conditional column selection - 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

conditional column selection

Author

Topic

b
Starting Member

12 Posts

Posted - 2007-09-13 : 06:53:22

I have one table with 6 columns (Entrance1,Exit1,Entrance2,Exit2,Entrance3,Exit3).
This columns can take null values.
I want to select just the last one of this columns that is not null.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 07:09:22

coalesce(col1,col2,col3,col4,col5,col6)

PS: If anyone is interested in a SQL Server Job in Connecticut with excellent pay send me a resume to ValterBorges@msn.com

b
Starting Member

12 Posts

Posted - 2007-09-13 : 07:17:11

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:21:02

quote:
Originally posted by b

I want to select just the last one of this columns that is not null.

coalesce(col6,col5,col4,col3,col2,col1)

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

b
Starting Member

12 Posts

Posted - 2007-09-13 : 07:30:52

Thank you Peso.
Can you tell me how can i get the name of the column that grants
coalesce(col6,col5,col4,col3,col2,col1)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:35:36

[code]SELECT CASE
WHEN Exit3 IS NOT NULL THEN Exit3
WHEN Entrance3 IS NOT NULL THEN Entrance3
WHEN Exit2 IS NOT NULL THEN Exit2
WHEN Entrance2 IS NOT NULL THEN Entrance2
WHEN Exit1 IS NOT NULL THEN Exit1
WHEN Entrance1 IS NOT NULL THEN Entrance1
END AS theValue,
CASE
WHEN Exit3 IS NOT NULL THEN 'Exit3'
WHEN Entrance3 IS NOT NULL THEN 'Entrance3'
WHEN Exit2 IS NOT NULL THEN 'Exit2'
WHEN Entrance2 IS NOT NULL THEN 'Entrance2'
WHEN Exit1 IS NOT NULL THEN 'Exit1'
WHEN Entrance1 IS NOT NULL THEN 'Entrance1'
END AS theColumn
FROM Table1[/code]

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

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 07:36:09

select
case
when col6 is not null then 'col6'
when col5 is not null then 'col5'
when col4 is not null then 'col4'
when col3 is not null then 'col3'
when col2 is not null then 'col2'
when col1 is not null then 'col1'
else 'na'
end as revnonnullcol,
coalesce(col6,col5,col4,col3,col2,col1) as revnonnullval
from
tablex

PS: If anyone is interested in a SQL Job in Connecticut with excellent pay please send me a resume to ValterBorges@msn.com

b
Starting Member

12 Posts

Posted - 2007-09-13 : 07:55:28

I have this record.
col1 col2 col3 col4 col5 col6
05/09/2007 06/09/2007 NULL NULL NULL NULL

I want to update this record and insert data just on col3.

If i have this other record
col1 col2 col3 col4 col5 col6
05/09/2007 06/09/2007 13/09/2007 NULL NULL NULL

I want to insert data on col4.
So i want to insert data on the first null column that commes after the last column that is not null.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 08:02:11

This is your original problem? Why didn't you tell us?

DECLARE	@NewDate DATETEIM

SET @NewDate = '20070914'

UPDATE	Table1
SET	Entrance1	=	CASE
					WHEN Entrance1 IS NULL THEN @NewDate
					ELSE Entrance1
				END,
	Exit1		=	CASE
					WHEN Exit1 IS NULL AND Entrance1 IS NOT NULL THEN @NewDate
					ELSE Entrance1
				END,
	Entrance2	=	CASE
					WHEN Entrance2 IS NULL AND Exit1 IS NOT NULL AND Entrance1 IS NOT NULL THEN @NewDate
					ELSE Entrance1
				END,
	Exit2		=	CASE
					WHEN Exit2 IS NULL AND Entrance2 IS NOT NULL AND Exit1 IS NOT NULL AND Entrance1 IS NOT NULL THEN @NewDate
					ELSE Entrance1
				END,
	Entrance3	=	CASE
					WHEN Entrance3 IS NULL AND Exit2 IS NOT NULL AND Entrance2 IS NOT NULL AND Exit1 IS NOT NULL AND Entrance1 IS NOT NULL THEN @NewDate
					ELSE Entrance1
				END,
	Exit3		=	CASE
					WHEN Exit3 IS NULL AND Entrance3 IS NOT NULL AND Exit2 IS NOT NULL AND Entrance2 IS NOT NULL AND Exit1 IS NOT NULL AND Entrance1 IS NOT NULL THEN @NewDate
					ELSE Entrance1
				END

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-13 : 08:13:43

You should really normalize your table, if you can. Your design is making something that is normally short, simple and efficient very complicated.

http://www.datamodel.org/NormalizationRules.html

- Jeff
http://weblogs.sqlteam.com/JeffS

b
Starting Member

12 Posts

Posted - 2007-09-13 : 08:24:46

If you can give me an idea.
I want to keep in a database the entrances and exits of persons, and i want to keep information about the time they enter and exit each day. Because i need a kind of history too.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 09:23:52

[code]-- Normalized table design
CREATE TABLE History
(
MemberID INT,
theDate DATETIME
)

-- Get last insert date
SELECT MemberID,
MAX(theDate) AS LastInsertDate
FROM History
GROUP BY MemberID

-- Get all inserts with sequence number
SELECT MemberID,
theDate,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY theDate) AS SequenceNumber
FROM History[/code]

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