T-SQL problem! how to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

T-SQL problem! how to do this?

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-17 : 06:00:48

Greetings once again SQL friends,

Consider the following test data:


if object_id('tempdb..#VehMakeVehModel') > 0
drop table #VehMakeVehModel

create table #VehMakeVehModel (VehModelId int, VehMake varchar(100), VehModel varchar(100), EngineCC int, NumberOfDoors int, VehFuelType char(1),
								VehTransmission char(1), ManufacturedFrom int, ManufacturedTo int)
insert into #VehMakeVehModel
select 1, 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 5, 'D', 'M', 2006, 2007 union all
select 2, 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 4, 'D', 'M', 2006, 2007 union all
select 3, 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 4, 'D', 'A', 2006, 2007 union all
select 4, 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 5, 'D', 'A', 2006, 2007 union all
select 5, 'ALFA ROMEO', '200 LUSSO JTDM (200)', 2500, 4, 'P', 'M', 2006, 2006 union all
select 6, 'VOLKSWAGEN', 'POLO', 2500, 3, 'P', 'M', 2002, 2006 union all
select 7, 'VOLKSWAGEN', 'POLO', 2500, 4, 'P', 'M', 2002, 2006



if object_id('tempdb..#MyStagingTable') > 0
drop table #MyStagingTable

create table #MyStagingTable (VehModelId int, VehMake varchar(100), Vehmodel varchar(100), EngineCC int, NumberOfDoors int, 
								VehFuelType char(1), VehTransmission char(1), ManufacturedFrom int, ManufacturedTo int)
insert into #MyStagingTable(VehMake, Vehmodel, EngineCC, NumberOfDoors, VehFuelType, VehTransmission, ManufacturedFrom , ManufacturedTo)
select 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 5, null, null, 2006, 2007 union all
select 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 5, null, 'A', 2006, 2007 union all
select 'ALFA ROMEO', '200 LUSSO JTDM (200)', 1000, 5, null, null, 2006, 2006 union all
select 'VOLKSWAGEN', 'POLO', 2500, 4, 'P', 'M', 2002, 2006


select * from #VehMakeVehModel
select * from #MyStagingTable

What we have is two tables and the idea is to populate the VehModelId in the MyStagingTable table by finding the best match in the VehMakeVehModel table.

Now if we look at the very first record in MyStagingTable table we can see that we only have NumberOfDoors, ManufacturedTo and ManufacturedFrom fields. In the VehMakeVehModel there are 5 ALFA ROMEO cars but only two have 5 doors but they have different transmission types (one is Automatic and the other is Manual). As you can see, it's impossible to decide which of these two matches our model so we can not assign an ID. That's perfectly fine.

Now the next record in #MyStagingTable has NumberOfDoors AND also a Transmission type but no FuelType. In #VehMakeVehModel we can see that VehModeld 5 is a good match and since there is only record that has NumberOfDoors = 5 and Transmission = 'A', we can safely assume that it's the same model even though we have no fuel type.

Next we have another ALFA ROMEO but a slightly different model. This one has only number of doors but in #VehMakeVehModel there is only ONE record with the same model name so we will assume that they are the same models.

Finally we have a VOLKSWAGEN POLO and we can see that there is a straight match with the record in #VehMakeVehModel so we can also assign an ID.

If that makes sense so far, my question is how can I do develop a single SQL query to do this?!

I have done some SQL for this but for the life of me I can't do this in one query or even two.

Your help would be much appreciated.

Sorry I forgot to add, the following is the desired output:


if object_id('tempdb..#MyStagingTable_Outcome') > 0
drop table #MyStagingTable_Outcome

create table #MyStagingTable_Outcome (VehModelId int, VehMake varchar(100), Vehmodel varchar(100), EngineCC int, NumberOfDoors int, 
								VehFuelType char(1), VehTransmission char(1), ManufacturedFrom int, ManufacturedTo int)
insert into #MyStagingTable_Outcome(VehModelId, VehMake, Vehmodel, EngineCC, NumberOfDoors, VehFuelType, VehTransmission, ManufacturedFrom , ManufacturedTo)
select null, 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 5, null, null, 2006, 2007 union all
select 4, 'ALFA ROMEO', '159 LUSSO JTDM (200)', 2387, 5, null, 'A', 2006, 2007 union all
select 5, 'ALFA ROMEO', '200 LUSSO JTDM (200)', 1000, 5, null, null, 2006, 2006 union all
select 7, 'VOLKSWAGEN', 'POLO', 2500, 4, 'P', 'M', 2002, 2006

select * from #MyStagingTable_Outcome

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-17 : 07:25:33

Over 30 views and not even a suggestion? Is there a problem with my explanation?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-17 : 08:42:22

Greetings:

This is my solution, if anyone can come up with a better way I would love to hear from them!


-- Stage 1

update a
set VehModelId = b.VehModelId
from #MyStagingTable as a
inner join (select max(VehModelId) as VehModelId, VehMake, VehModel 
			from #VehMakeVehModel
			group by VehMake, VehModel  having count(*) = 1) as b
	on a.VehMake = b.VehMake
	and a.VehModel = b.VehModel

-- Stage 2

update a
set VehModelId = b.VehModelId
from #MyStagingTable as a
inner join #VehMakeVehModel as b
	on a.VehMake = b.VehMake
	and a.VehModel = b.VehModel
	and a.EngineCC = b.EngineCC
	and a.NumberOfDoors = b.NumberOfDoors
	and a.VehFuelType = b.VehFuelType
	and a.VehTransmission = b.VehTransmission

-- Stage 3

update a
set VehModelId = b.VehModelId
from #MyStagingTable as a
inner join #VehMakeVehModel as b
	on a.VehMake = b.VehMake
	and a.VehModel = b.VehModel
where a.VehModelId is null
and PersonId not in (select a.PersonId
						from #MyStagingTable as a
						inner join #VehMakeVehModel as b
							on a.VehMake = b.VehMake
						where a.VehModel = b.VehModel
							and (isnull(a.EngineCC, 0) = case when a.EngineCC is null then 0 else b.EngineCC end
							and isnull(a.NumberOfDoors, 0) = case when a.NumberOfDoors is null then 0 else b.NumberOfDoors end
							and isnull(a.VehFuelType, 'X') = case when a.VehFuelType is null then 'X' else b.VehFuelType end
							and isnull(a.VehTransmission, 'X') = case when a.VehTransmission is null then 'X' else b.VehTransmission end)
						group  by  a.PersonId having count(*) > 1)
and (isnull(a.EngineCC, 0) = case when a.EngineCC is null then 0 else b.EngineCC end
and isnull(a.NumberOfDoors, 0) = case when a.NumberOfDoors is null then 0 else b.NumberOfDoors end
and isnull(a.VehFuelType, 'X') = case when a.VehFuelType is null then 'X' else b.VehFuelType end
and isnull(a.VehTransmission, 'X') = case when a.VehTransmission is null then 'X' else b.VehTransmission end)

raja_saminathan
Starting Member

12 Posts

Posted - 2008-06-17 : 10:04:41

You can combine all them into a single select statement and update .

Try using like this

select Distinct v.VehModelId from #VehMakeVehModel v inner join #MyStagingTable s on ((v.NumberOfDoors= s.NumberOfDoors and v.vehtransmission = s.vehtransmission) or (v.NumberOfDoors = s.NumberOfDoors and v.VehModel =s.VehModel))

Regards,

Rajesh

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-17 : 10:39:19

Hi Rajesh,

This doesn't quite do what I want but thanks anyway!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources