Find best match - 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)

Find best match

Author

Topic

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-05-08 : 05:15:02

I thought this problem would have been covered before, but I couldn't find anything.

I have the following table:


CREATE TABLE [Samples] (
	[LotNr] [int] NOT NULL ,
	[TypeId] [int] NOT NULL ,
	[SizeId] [int] NOT NULL ,
	[YieldRaw] [decimal](3, 2) NOT NULL ,
	[YieldCooked] [decimal](3, 2) NOT NULL ,
	[YieldDefrosted] [decimal](3, 2) NULL ,
	[DestinationId] [int] NOT NULL ,
	CONSTRAINT [PK_Samples] PRIMARY KEY  CLUSTERED 
	(
		[LotNr]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Which holds sample information for our products. Results from several samples are stored in this table, and based on the TypeId, SizeId and the Yields a certain
DestenationId is appointed to the specific LotNr (this is 'manually' done by our QA staff)

What I want is to have the computer make a proposal which destinationId would suit the specific LotNr best, based on historical data in this table.

It's easy to find the top (=most chosen) destinationId for the specific TypeId and SizeId, but I'm having trouble finding the closest match for the three yield fields.

Thanks in advance

Peter

Edited by - Peter Dutch on 05/08/2003 05:16:21

Edited by - Peter Dutch on 05/08/2003 05:17:03

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-08 : 06:32:45

Could you do it by creating a mining model thingy in analysis services? Though how you would use what that spits out in your application I don't know.

Edited by - Arnold Fribble on 05/08/2003 06:37:32

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-05-08 : 06:35:54

Thanks for the reply, but I have no experience whatsoever with analysis service, and was kinda hoping for a 'normal' sql solution.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-08 : 06:42:41

I've never written a data mining model in SQL Server either.

Nonetheless, the question you're asking is a classic data mining scenario: given a large number of cases (LotNr) where I believe DestinationId can be predicted, find me a function that predicts a value of DestinationId for a given case of (TypeId, SizeId, YieldRaw, YieldCooked, YieldDefrosted).

Edited by - Arnold Fribble on 05/08/2003 06:43:54

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-05-08 : 06:47:11

Hmm, hearing you put it like that I think you're right.

I'll have a look into it, thanks.

(I'm still open to other solutions though)

[edit]
and what you said is exactly what I want. You just explain it a lot better then I can

(on the other hand, I still to it better in English than you could do in dutchI guess

)

[/edit]

Edited by - Peter Dutch on 05/08/2003 06:50:30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources