Please help with queries - 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)

Please help with queries

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2006-11-07 : 15:26:04

Hello all,

I have a table like this:

create table #address (entityCode varchar(100),addresstype char(1),
address1 varchar(50),address2 varchar(50),city varchar(50),enddate datetime)

insert into #address
select '111111','M','1233 chate dr',null,'gardena',getdate()-30
union
select '111111','F','123 chate dr',null,'gardena',getdate()- 20
union
select '111111','D','122 chate dr',null,'gardena',getdate() - 10
union
select '111111','D','122 rose dr',null,'gardena',Null
union
select '22222','F','100 rose dr',null,'gardena',getdate()- 20
union
select '22222','D','108 chate dr',null,'gardena',getdate() - 10
union
select '22222','D','145 chate dr',null,'gardena',getdate() -2
union
select '22222','D','190 rose dr',null,'gardena',Null
union
select '34536','D','100 rose dr',null,'gardena',getdate()- 20
union
select '34536','D','108 chate dr',null,'gardena',NULL
----------------------
i'd like to have a select result as below:

111111 M 1233 chate dr NULL gardena 2006-10-08 14:15:13.443
22222 F 100 rose dr NULL gardena 2006-10-18 14:15:13.443
34536 D 108 chate dr NULL gardena NULL
--------------------------
what i 'd like to accomplish is
if addresstype = 'M' then AddressM
elseif addresstype = 'F' then addressF
else addressD.
and also, i need to select the Max (enddate) if there're 2 records of one type of address, or if enddate is null then that the record i want. In order to get the result i wanted , i have to create 3 temp tables where addresstype = 'F' and 'M' and 'D' and do the select max(...) , i wonder if there're a shorter way to do it? .

Thanks for all your idea.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 15:38:49

[code]-- prepare test data
create table #address(entityCode varchar(100),addresstype char(1),
address1 varchar(50),address2 varchar(50),city varchar(50),enddate datetime)

insert into #address
select '111111','M','1233 chate dr',null,'gardena',getdate()-30
union
select '111111','F','123 chate dr',null,'gardena',getdate()- 20
union
select '111111','D','122 chate dr',null,'gardena',getdate() - 10
union
select '111111','D','122 rose dr',null,'gardena',Null
union
select '22222','F','100 rose dr',null,'gardena',getdate()- 20
union
select '22222','D','108 chate dr',null,'gardena',getdate() - 10
union
select '22222','D','145 chate dr',null,'gardena',getdate() -2
union
select '22222','D','190 rose dr',null,'gardena',Null
union
select '34536','D','100 rose dr',null,'gardena',getdate()- 20
union
select '34536','D','108 chate dr',null,'gardena',NULL

-- stage the data
declare @address table (rowid int identity(1, 1), entityCode varchar(100),addresstype char(1),
address1 varchar(50),address2 varchar(50),city varchar(50),enddate datetime)

insert @address
(
entityCode,
addresstype,
address1,
address2,
city,
enddate
)
SELECT entityCode,
addresstype,
address1,
address2,
city,
enddate
FROM #Address
ORDER BY EntityCode,
CASE
WHEN AddressType = 'F' THEN 0
WHEN AddressType = 'M' THEN 1
ELSE 2
END,
CASE
WHEN EndDate IS NULL THEN 0
ELSE 1
END,
EndDate

-- do the work
SELECT entityCode,
addresstype,
address1,
address2,
city,
enddate
FROM @Address
WHERE rowid in (select min(rowid) from @address group by entitycode)

DROP TABLE #address[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources