I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
Showing posts with label division. Show all posts
Showing posts with label division. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Help with relational set division, please! Good challange!
/*
Please help.
Definition:
I am trying to compose a query that will do part matching based on certain
input criteria, by item class.
I want to define a table called criteria and let users define sets of
criteria that may satisfy a part match. if I have
an input set of parameters, i want to match this set to all defined criteria
sets. The first six define size matching, via range.
It is assumend that indexes of 1-2 is x size range, 3-4 is y size range and
5-6 is z size range. Therefore the first part is to ensure that
any xyz size part by class is to ensure that it fits XYZ specified criteria
ranges for that class.
I got that working below. Any parameter (index > 6) has to match exactly, no
ranges. Like if a part has material M1, matching set(s) have to have that
material as well, = input material.
The second requirement is that an input criteria must find record sets that
matches defined input exactly or can have extra elements in criteria,
that can be ignored, as long as it is not specified in input set.
I do understand that this is a case of set relational division.
I am trying to find sets that contain a specific subset.
If I get multiple sets matching, the top ranked (defined later)
ResultingItem (set) will be used as the final part match.
This seems to be the difficult part, escaping me.
Please help. Your effort and time is greatly appreciated.
farmer
In the example below, input set matches criteria for ItemA, not ItemX,
therefore ItemA is the match.
If input record index 10 would not exists, then both would match.
Appropriate characteristics have the same index (7 to N)
*/
set nocount on
declare @.criteria table
(
ItemClass varchar(10) not null
,indID int
,indValue varchar(50)
,ResultingItem varchar(10) not null
,primary key (ItemClass, indID, ResultingItem)
)
declare @.Input table
(
ItemClass varchar(10)
,indID int
,indValue varchar(50)
)
-- this is defined criteria
insert @.criteria values ('Panel', 1, '20', 'ItemA') -- x from
insert @.criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert @.criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert @.criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert @.criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert @.criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert @.criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
insert @.criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert @.criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert @.criteria values ('Panel', 1, '20', 'ItemX') -- x from
insert @.criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert @.criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert @.criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert @.criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert @.criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert @.criteria values ('Panel', 10, 'A1', 'ItemX') -- color -- defined but
IMOS data will not have it
insert @.criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert @.criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert @.Input values ('Panel', 1, '20.5') -- x value
insert @.Input values ('Panel', 2, '20.5') -- x value, same as prior index
insert @.Input values ('Panel', 3, '564.75') --y value
insert @.Input values ('Panel', 4, '564.75') --y value, same as prior index
insert @.Input values ('Panel', 5, '6.0') -- z value
insert @.Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert @.Input values ('Panel', 10, 'c1') -- color specied
insert @.Input values ('Panel', 14, 'M1') -- mat
-- Now, let us try to match
set nocount off
select c.ItemClass, c.ResultingItem, COUNT(*)
FROM @.criteria c
LEFT JOIN @.Input i
on c.ItemClass = i.ItemClass
AND c.indID = i.indID
AND (case when i.indID = 1 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 2 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID > 6 then c.indValue else i.indValue end) = i.indValue
GROUP BY c.ItemClass, c.ResultingItem--, c.ItemClass
--HAVING COUNT(*) <=
--AND COUNT(c.ItemClass) =
--(
-- SELECT COUNT(c2.ItemClass)
-- FROM @.criteria c2
-- WHERE c2.ItemClass = i.ItemClass
-- GROUP BY c2.ItemClass
--)
select *
from @.Input i
LEFT JOIN @.criteria c
--on c.ItemClass = i.ItemClass AND c.indID = i.indID
on c.ItemClass = i.ItemClass AND c.indID = i.indID
AND (case when i.indID = 1 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 2 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID > 6 then c.indValue else i.indValue end) = i.indValue
--SELECT PS1.pilot
--FROM PilotSkills AS PS1
--JOIN Hangar AS H1 ON PS1.plane = H1.plane
--GROUP BY PS1.pilot
--HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
--print 'Fourth way'
--SELECT PS1.pilot
--FROM PilotSkills AS PS1
--LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane
--GROUP BY PS1.pilot
--HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
--AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);thank you all
I did come up with an answer. Any optimizations you see?
if object_id('tempdb..#criteria') is not null
drop table #criteria
go
if object_id('tempdb..#Input') is not null
drop table #Input
go
CREATE TABLE #Criteria
(
ItemFamily varchar(10) not null
,PropertyID int
,CriteriaValue varchar(50)
,ItemMatch varchar(10) not null
,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
)
CREATE TABLE #Input
(
ItemFamily varchar(10)
,PropertyID int
,CriteriaValue varchar(50)
,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
)
go
set nocount on
-- this is defined criteria
insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert #Criteria values ('Panel', 10, 'a1', 'ItemX') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
insert #Criteria values ('Back', 10, 'c1', 'ItemZ') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert #Input values ('Panel', 1, '20.50') -- x value
insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
insert #Input values ('Panel', 3, '564.75') --y value
insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
insert #Input values ('Panel', 5, '6.0') -- z value
insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert #Input values ('Panel', 10, 'c1') -- color specied
insert #Input values ('Panel', 14, 'M1') -- mat
insert #Input values ('Back', 1, '20.50') -- x value
insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
insert #Input values ('Back', 3, '564.75') --y value
insert #Input values ('Back', 4, '564.75') --y value, same as prior index
insert #Input values ('Back', 5, '6.0') -- z value
insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
insert #Input values ('Back', 10, 'a1') -- color specied
insert #Input values ('Back', 14, 'M1') -- mat
--insert #Input values ('Panel', 15, 'a') -- mat
-- Now, let us try to match
--set nocount off
-- multirecord match
SELECT v.ItemFamily, v.ItemMatch
FROM
(
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c
JOIN #Input i
ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID
AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue end
= i.CriteriaValue
GROUP BY c.ItemFamily, c.ItemMatch
)v
JOIN
(
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
"Farmer" <someone@.somewhere.com> wrote in message
news:ecWGFEyEGHA.376@.TK2MSFTNGP12.phx.gbl...
> /*
> Please help.
> Definition:
> I am trying to compose a query that will do part matching based on certain
> input criteria, by item class.
> I want to define a table called criteria and let users define sets of
> criteria that may satisfy a part match. if I have
> an input set of parameters, i want to match this set to all defined
> criteria sets. The first six define size matching, via range.
> It is assumend that indexes of 1-2 is x size range, 3-4 is y size range
> and 5-6 is z size range. Therefore the first part is to ensure that
> any xyz size part by class is to ensure that it fits XYZ specified
> criteria ranges for that class.
> I got that working below. Any parameter (index > 6) has to match exactly,
> no ranges. Like if a part has material M1, matching set(s) have to have
> that
> material as well, = input material.
> The second requirement is that an input criteria must find record sets
> that matches defined input exactly or can have extra elements in criteria,
> that can be ignored, as long as it is not specified in input set.
> I do understand that this is a case of set relational division.
> I am trying to find sets that contain a specific subset.
> If I get multiple sets matching, the top ranked (defined later)
> ResultingItem (set) will be used as the final part match.
> This seems to be the difficult part, escaping me.
> Please help. Your effort and time is greatly appreciated.
> farmer
> In the example below, input set matches criteria for ItemA, not ItemX,
> therefore ItemA is the match.
> If input record index 10 would not exists, then both would match.
> Appropriate characteristics have the same index (7 to N)
> */
> set nocount on
> declare @.criteria table
> (
> ItemClass varchar(10) not null
> ,indID int
> ,indValue varchar(50)
> ,ResultingItem varchar(10) not null
> ,primary key (ItemClass, indID, ResultingItem)
> )
> declare @.Input table
> (
> ItemClass varchar(10)
> ,indID int
> ,indValue varchar(50)
> )
> -- this is defined criteria
> insert @.criteria values ('Panel', 1, '20', 'ItemA') -- x from
> insert @.criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
> insert @.criteria values ('Panel', 3, '564.75', 'ItemA') --y from
> insert @.criteria values ('Panel', 4, '564.75', 'ItemA') --y to
> insert @.criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
> insert @.criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
> insert @.criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined
> but IMOS data will not have it
> insert @.criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
> insert @.criteria values ('Panel', 15, 'a', 'ItemA') -- other
> characteristic
> insert @.criteria values ('Panel', 1, '20', 'ItemX') -- x from
> insert @.criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
> insert @.criteria values ('Panel', 3, '564.75', 'ItemX') --y from
> insert @.criteria values ('Panel', 4, '564.75', 'ItemX') --y to
> insert @.criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
> insert @.criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
> insert @.criteria values ('Panel', 10, 'A1', 'ItemX') -- color -- defined
> but IMOS data will not have it
> insert @.criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
> insert @.criteria values ('Panel', 15, 'c', 'ItemX') -- other
> characteristic
> -- in this set, duplicate input values for sizes so we have matching index
> insert @.Input values ('Panel', 1, '20.5') -- x value
> insert @.Input values ('Panel', 2, '20.5') -- x value, same as prior index
> insert @.Input values ('Panel', 3, '564.75') --y value
> insert @.Input values ('Panel', 4, '564.75') --y value, same as prior index
> insert @.Input values ('Panel', 5, '6.0') -- z value
> insert @.Input values ('Panel', 6, '6.0') -- z value, same as prior index
> insert @.Input values ('Panel', 10, 'c1') -- color specied
> insert @.Input values ('Panel', 14, 'M1') -- mat
> -- Now, let us try to match
> set nocount off
> select c.ItemClass, c.ResultingItem, COUNT(*)
> FROM @.criteria c
> LEFT JOIN @.Input i
> on c.ItemClass = i.ItemClass
> AND c.indID = i.indID
> AND (case when i.indID = 1 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 2 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID > 6 then c.indValue else i.indValue end) =
> i.indValue
> GROUP BY c.ItemClass, c.ResultingItem--, c.ItemClass
> --HAVING COUNT(*) <=
> --AND COUNT(c.ItemClass) =
> --(
> -- SELECT COUNT(c2.ItemClass)
> -- FROM @.criteria c2
> -- WHERE c2.ItemClass = i.ItemClass
> -- GROUP BY c2.ItemClass
> --)
>
>
> select *
> from @.Input i
> LEFT JOIN @.criteria c
> --on c.ItemClass = i.ItemClass AND c.indID = i.indID
> on c.ItemClass = i.ItemClass AND c.indID = i.indID
> AND (case when i.indID = 1 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 2 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID > 6 then c.indValue else i.indValue end) =
> i.indValue
>
>
> --SELECT PS1.pilot
> --FROM PilotSkills AS PS1
> --JOIN Hangar AS H1 ON PS1.plane = H1.plane
> --GROUP BY PS1.pilot
> --HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
> --
> --print 'Fourth way'
> --SELECT PS1.pilot
> --FROM PilotSkills AS PS1
> --LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane
> --GROUP BY PS1.pilot
> --HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
> --AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);
>
>|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Improved solution
if object_id('tempdb..#criteria') is not null
drop table #criteria
go
if object_id('tempdb..#Input') is not null
drop table #Input
go
CREATE TABLE #Criteria
(
ItemFamily varchar(10) not null
,PropertyID int
,CriteriaValue varchar(50)
,ItemMatch varchar(10) not null
,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
)
CREATE TABLE #Input
(
ItemFamily varchar(10)
,PropertyID int
,CriteriaValue varchar(50)
,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
)
go
set nocount on
-- this is defined criteria
insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
--insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemX') -- color
insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemZ') -- color
insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color
insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert #Input values ('Panel', 1, '20.50') -- x value
insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
insert #Input values ('Panel', 3, '564.75') --y value
insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
insert #Input values ('Panel', 5, '6.0') -- z value
insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert #Input values ('Panel', 10, 'c1') -- color specied
insert #Input values ('Panel', 14, 'M1') -- mat
insert #Input values ('Back', 1, '20.50') -- x value
insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
insert #Input values ('Back', 3, '564.75') --y value
insert #Input values ('Back', 4, '564.75') --y value, same as prior index
insert #Input values ('Back', 5, '6.0') -- z value
insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
insert #Input values ('Back', 10, 'a1') -- color specied
insert #Input values ('Back', 14, 'M1') -- mat
--insert #Input values ('Panel', 15, 'a') -- mat
-- Now, let us try to match
--set nocount off
-- Multi family record match
SELECT c.ItemFamily, c.ItemMatch--, COUNT(*) as Cnt
FROM #Criteria c
JOIN #Input i
ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID
AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue end
= i.CriteriaValue
GROUP BY c.ItemFamily, c.ItemMatch
HAVING COUNT(*) =
(
SELECT COUNT(*) as Cnt
FROM #Input i2
WHERE i2.ItemFamily = c.ItemFamily
GROUP BY i2.ItemFamily
)
"Farmer" <someone@.somewhere.com> wrote in message
news:esqym97EGHA.3348@.TK2MSFTNGP10.phx.gbl...
> thank you all
> I did come up with an answer. Any optimizations you see?
> if object_id('tempdb..#criteria') is not null
> drop table #criteria
> go
> if object_id('tempdb..#Input') is not null
> drop table #Input
> go
> CREATE TABLE #Criteria
> (
> ItemFamily varchar(10) not null
> ,PropertyID int
> ,CriteriaValue varchar(50)
> ,ItemMatch varchar(10) not null
> ,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
> )
> CREATE TABLE #Input
> (
> ItemFamily varchar(10)
> ,PropertyID int
> ,CriteriaValue varchar(50)
> ,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
> )
> go
> set nocount on
> -- this is defined criteria
> insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
> insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
> insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
> insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
> insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
> insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
> insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
> insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other
> characteristic
> insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
> insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
> insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
> insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
> insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
> insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
> insert #Criteria values ('Panel', 10, 'a1', 'ItemX') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
> insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other
> characteristic
>
> insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
> insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
> insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
> insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
> insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
> insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
> insert #Criteria values ('Back', 10, 'c1', 'ItemZ') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
> insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
> insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
> insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
> insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
> insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
> insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
> insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
> insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
> insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
>
> -- in this set, duplicate input values for sizes so we have matching index
> insert #Input values ('Panel', 1, '20.50') -- x value
> insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
> insert #Input values ('Panel', 3, '564.75') --y value
> insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
> insert #Input values ('Panel', 5, '6.0') -- z value
> insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
> insert #Input values ('Panel', 10, 'c1') -- color specied
> insert #Input values ('Panel', 14, 'M1') -- mat
> insert #Input values ('Back', 1, '20.50') -- x value
> insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
> insert #Input values ('Back', 3, '564.75') --y value
> insert #Input values ('Back', 4, '564.75') --y value, same as prior index
> insert #Input values ('Back', 5, '6.0') -- z value
> insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
> insert #Input values ('Back', 10, 'a1') -- color specied
> insert #Input values ('Back', 14, 'M1') -- mat
> --insert #Input values ('Panel', 15, 'a') -- mat
> -- Now, let us try to match
> --set nocount off
> -- multirecord match
> SELECT v.ItemFamily, v.ItemMatch
> FROM
> (
> SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
> FROM #Criteria c
> JOIN #Input i
> ON c.ItemFamily = i.ItemFamily
> AND c.PropertyID = i.PropertyID
> AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue
> end = i.CriteriaValue
> GROUP BY c.ItemFamily, c.ItemMatch
> )v
> JOIN
> (
> SELECT ItemFamily, COUNT(*) as Cnt
> FROM #Input i
> GROUP BY i.ItemFamily
> ) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
>
>
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:ecWGFEyEGHA.376@.TK2MSFTNGP12.phx.gbl...
>|||thank you for your reply.
In my design, I try to "normalize" this type of schema.
criteria table
keyID, class, col1, col2,col3,col4, ItemMatchID
select *
from criteria
where class= @.someclassvalue and col1=@.someproperty1 AND
col2=@.someproperty12 and on to narrow or match finite ItemMatchID.
this gets complicated when the size as a property is required to support
ranges. this range (for now) in my design requires duplication so that two
edges can be compared in a record set, fromrange vs value vs torange.
Turning the above mentioned table into "tower" record set, makes it flexible
as there is no limit as to how many properties can be defined. and the
answer for matches can be answered via relational division, ie a fixed list
of properties must be contained in the criteria set.
if range would not be requred, then pure case of relational division can be
used. due to range requirement, I had to imprivise in my own way, that I had
published. Criteria is a fixed permanent table and Input will be temp table
in stored procedure.
Any better ideas?
I sent better solution in another post to relational division problem. All
this answeres my problem well.
thanks
farmer
"Razvan Socol" <RazvanSocol@.discussions.microsoft.com> wrote in message
news:5CFF4D42-2FD2-4FFC-9A45-E5CB431213F3@.microsoft.com...
> Hi, Farmer
> I think the following query is equivalent to your query:
> SELECT v.ItemFamily, v.ItemMatch
> FROM (
> SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
> FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
> AND c.PropertyID = i.PropertyID AND (
> i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
> OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
> OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
> )
> GROUP BY c.ItemFamily, c.ItemMatch
> ) v JOIN (
> SELECT ItemFamily, COUNT(*) as Cnt
> FROM #Input i
> GROUP BY i.ItemFamily
> ) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
> However, if the #Input is really a table (not a view), I don't like the
> idea of duplicating the values in the #Input table, just so you have a
> matching index. You should consider putting more meaning into the
> database: those comments that you have next to each row in the sample
> data... they should be in some table. I don't know the specifics of
> your database (and I don't think I want to know all of them), but you
> should give it another thought.
> Razvan
>|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan
Please help.
Definition:
I am trying to compose a query that will do part matching based on certain
input criteria, by item class.
I want to define a table called criteria and let users define sets of
criteria that may satisfy a part match. if I have
an input set of parameters, i want to match this set to all defined criteria
sets. The first six define size matching, via range.
It is assumend that indexes of 1-2 is x size range, 3-4 is y size range and
5-6 is z size range. Therefore the first part is to ensure that
any xyz size part by class is to ensure that it fits XYZ specified criteria
ranges for that class.
I got that working below. Any parameter (index > 6) has to match exactly, no
ranges. Like if a part has material M1, matching set(s) have to have that
material as well, = input material.
The second requirement is that an input criteria must find record sets that
matches defined input exactly or can have extra elements in criteria,
that can be ignored, as long as it is not specified in input set.
I do understand that this is a case of set relational division.
I am trying to find sets that contain a specific subset.
If I get multiple sets matching, the top ranked (defined later)
ResultingItem (set) will be used as the final part match.
This seems to be the difficult part, escaping me.
Please help. Your effort and time is greatly appreciated.
farmer
In the example below, input set matches criteria for ItemA, not ItemX,
therefore ItemA is the match.
If input record index 10 would not exists, then both would match.
Appropriate characteristics have the same index (7 to N)
*/
set nocount on
declare @.criteria table
(
ItemClass varchar(10) not null
,indID int
,indValue varchar(50)
,ResultingItem varchar(10) not null
,primary key (ItemClass, indID, ResultingItem)
)
declare @.Input table
(
ItemClass varchar(10)
,indID int
,indValue varchar(50)
)
-- this is defined criteria
insert @.criteria values ('Panel', 1, '20', 'ItemA') -- x from
insert @.criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert @.criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert @.criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert @.criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert @.criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert @.criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
insert @.criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert @.criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert @.criteria values ('Panel', 1, '20', 'ItemX') -- x from
insert @.criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert @.criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert @.criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert @.criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert @.criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert @.criteria values ('Panel', 10, 'A1', 'ItemX') -- color -- defined but
IMOS data will not have it
insert @.criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert @.criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert @.Input values ('Panel', 1, '20.5') -- x value
insert @.Input values ('Panel', 2, '20.5') -- x value, same as prior index
insert @.Input values ('Panel', 3, '564.75') --y value
insert @.Input values ('Panel', 4, '564.75') --y value, same as prior index
insert @.Input values ('Panel', 5, '6.0') -- z value
insert @.Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert @.Input values ('Panel', 10, 'c1') -- color specied
insert @.Input values ('Panel', 14, 'M1') -- mat
-- Now, let us try to match
set nocount off
select c.ItemClass, c.ResultingItem, COUNT(*)
FROM @.criteria c
LEFT JOIN @.Input i
on c.ItemClass = i.ItemClass
AND c.indID = i.indID
AND (case when i.indID = 1 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 2 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID > 6 then c.indValue else i.indValue end) = i.indValue
GROUP BY c.ItemClass, c.ResultingItem--, c.ItemClass
--HAVING COUNT(*) <=
--AND COUNT(c.ItemClass) =
--(
-- SELECT COUNT(c2.ItemClass)
-- FROM @.criteria c2
-- WHERE c2.ItemClass = i.ItemClass
-- GROUP BY c2.ItemClass
--)
select *
from @.Input i
LEFT JOIN @.criteria c
--on c.ItemClass = i.ItemClass AND c.indID = i.indID
on c.ItemClass = i.ItemClass AND c.indID = i.indID
AND (case when i.indID = 1 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 2 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
i.indValue
AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
i.indValue
AND (case when i.indID > 6 then c.indValue else i.indValue end) = i.indValue
--SELECT PS1.pilot
--FROM PilotSkills AS PS1
--JOIN Hangar AS H1 ON PS1.plane = H1.plane
--GROUP BY PS1.pilot
--HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
--print 'Fourth way'
--SELECT PS1.pilot
--FROM PilotSkills AS PS1
--LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane
--GROUP BY PS1.pilot
--HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
--AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);thank you all
I did come up with an answer. Any optimizations you see?
if object_id('tempdb..#criteria') is not null
drop table #criteria
go
if object_id('tempdb..#Input') is not null
drop table #Input
go
CREATE TABLE #Criteria
(
ItemFamily varchar(10) not null
,PropertyID int
,CriteriaValue varchar(50)
,ItemMatch varchar(10) not null
,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
)
CREATE TABLE #Input
(
ItemFamily varchar(10)
,PropertyID int
,CriteriaValue varchar(50)
,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
)
go
set nocount on
-- this is defined criteria
insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert #Criteria values ('Panel', 10, 'a1', 'ItemX') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
insert #Criteria values ('Back', 10, 'c1', 'ItemZ') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color -- defined but
IMOS data will not have it
insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert #Input values ('Panel', 1, '20.50') -- x value
insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
insert #Input values ('Panel', 3, '564.75') --y value
insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
insert #Input values ('Panel', 5, '6.0') -- z value
insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert #Input values ('Panel', 10, 'c1') -- color specied
insert #Input values ('Panel', 14, 'M1') -- mat
insert #Input values ('Back', 1, '20.50') -- x value
insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
insert #Input values ('Back', 3, '564.75') --y value
insert #Input values ('Back', 4, '564.75') --y value, same as prior index
insert #Input values ('Back', 5, '6.0') -- z value
insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
insert #Input values ('Back', 10, 'a1') -- color specied
insert #Input values ('Back', 14, 'M1') -- mat
--insert #Input values ('Panel', 15, 'a') -- mat
-- Now, let us try to match
--set nocount off
-- multirecord match
SELECT v.ItemFamily, v.ItemMatch
FROM
(
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c
JOIN #Input i
ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID
AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue end
= i.CriteriaValue
GROUP BY c.ItemFamily, c.ItemMatch
)v
JOIN
(
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
"Farmer" <someone@.somewhere.com> wrote in message
news:ecWGFEyEGHA.376@.TK2MSFTNGP12.phx.gbl...
> /*
> Please help.
> Definition:
> I am trying to compose a query that will do part matching based on certain
> input criteria, by item class.
> I want to define a table called criteria and let users define sets of
> criteria that may satisfy a part match. if I have
> an input set of parameters, i want to match this set to all defined
> criteria sets. The first six define size matching, via range.
> It is assumend that indexes of 1-2 is x size range, 3-4 is y size range
> and 5-6 is z size range. Therefore the first part is to ensure that
> any xyz size part by class is to ensure that it fits XYZ specified
> criteria ranges for that class.
> I got that working below. Any parameter (index > 6) has to match exactly,
> no ranges. Like if a part has material M1, matching set(s) have to have
> that
> material as well, = input material.
> The second requirement is that an input criteria must find record sets
> that matches defined input exactly or can have extra elements in criteria,
> that can be ignored, as long as it is not specified in input set.
> I do understand that this is a case of set relational division.
> I am trying to find sets that contain a specific subset.
> If I get multiple sets matching, the top ranked (defined later)
> ResultingItem (set) will be used as the final part match.
> This seems to be the difficult part, escaping me.
> Please help. Your effort and time is greatly appreciated.
> farmer
> In the example below, input set matches criteria for ItemA, not ItemX,
> therefore ItemA is the match.
> If input record index 10 would not exists, then both would match.
> Appropriate characteristics have the same index (7 to N)
> */
> set nocount on
> declare @.criteria table
> (
> ItemClass varchar(10) not null
> ,indID int
> ,indValue varchar(50)
> ,ResultingItem varchar(10) not null
> ,primary key (ItemClass, indID, ResultingItem)
> )
> declare @.Input table
> (
> ItemClass varchar(10)
> ,indID int
> ,indValue varchar(50)
> )
> -- this is defined criteria
> insert @.criteria values ('Panel', 1, '20', 'ItemA') -- x from
> insert @.criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
> insert @.criteria values ('Panel', 3, '564.75', 'ItemA') --y from
> insert @.criteria values ('Panel', 4, '564.75', 'ItemA') --y to
> insert @.criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
> insert @.criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
> insert @.criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined
> but IMOS data will not have it
> insert @.criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
> insert @.criteria values ('Panel', 15, 'a', 'ItemA') -- other
> characteristic
> insert @.criteria values ('Panel', 1, '20', 'ItemX') -- x from
> insert @.criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
> insert @.criteria values ('Panel', 3, '564.75', 'ItemX') --y from
> insert @.criteria values ('Panel', 4, '564.75', 'ItemX') --y to
> insert @.criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
> insert @.criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
> insert @.criteria values ('Panel', 10, 'A1', 'ItemX') -- color -- defined
> but IMOS data will not have it
> insert @.criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
> insert @.criteria values ('Panel', 15, 'c', 'ItemX') -- other
> characteristic
> -- in this set, duplicate input values for sizes so we have matching index
> insert @.Input values ('Panel', 1, '20.5') -- x value
> insert @.Input values ('Panel', 2, '20.5') -- x value, same as prior index
> insert @.Input values ('Panel', 3, '564.75') --y value
> insert @.Input values ('Panel', 4, '564.75') --y value, same as prior index
> insert @.Input values ('Panel', 5, '6.0') -- z value
> insert @.Input values ('Panel', 6, '6.0') -- z value, same as prior index
> insert @.Input values ('Panel', 10, 'c1') -- color specied
> insert @.Input values ('Panel', 14, 'M1') -- mat
> -- Now, let us try to match
> set nocount off
> select c.ItemClass, c.ResultingItem, COUNT(*)
> FROM @.criteria c
> LEFT JOIN @.Input i
> on c.ItemClass = i.ItemClass
> AND c.indID = i.indID
> AND (case when i.indID = 1 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 2 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID > 6 then c.indValue else i.indValue end) =
> i.indValue
> GROUP BY c.ItemClass, c.ResultingItem--, c.ItemClass
> --HAVING COUNT(*) <=
> --AND COUNT(c.ItemClass) =
> --(
> -- SELECT COUNT(c2.ItemClass)
> -- FROM @.criteria c2
> -- WHERE c2.ItemClass = i.ItemClass
> -- GROUP BY c2.ItemClass
> --)
>
>
> select *
> from @.Input i
> LEFT JOIN @.criteria c
> --on c.ItemClass = i.ItemClass AND c.indID = i.indID
> on c.ItemClass = i.ItemClass AND c.indID = i.indID
> AND (case when i.indID = 1 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 2 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 3 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 4 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID = 5 then c.indValue else i.indValue end) <=
> i.indValue
> AND (case when i.indID = 6 then c.indValue else i.indValue end) >=
> i.indValue
> AND (case when i.indID > 6 then c.indValue else i.indValue end) =
> i.indValue
>
>
> --SELECT PS1.pilot
> --FROM PilotSkills AS PS1
> --JOIN Hangar AS H1 ON PS1.plane = H1.plane
> --GROUP BY PS1.pilot
> --HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);
> --
> --print 'Fourth way'
> --SELECT PS1.pilot
> --FROM PilotSkills AS PS1
> --LEFT OUTER JOIN Hangar AS H1 ON PS1.plane = H1.plane
> --GROUP BY PS1.pilot
> --HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
> --AND COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar);
>
>|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Improved solution
if object_id('tempdb..#criteria') is not null
drop table #criteria
go
if object_id('tempdb..#Input') is not null
drop table #Input
go
CREATE TABLE #Criteria
(
ItemFamily varchar(10) not null
,PropertyID int
,CriteriaValue varchar(50)
,ItemMatch varchar(10) not null
,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
)
CREATE TABLE #Input
(
ItemFamily varchar(10)
,PropertyID int
,CriteriaValue varchar(50)
,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
)
go
set nocount on
-- this is defined criteria
insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined but
IMOS data will not have it
--insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other characteristic
insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
insert #Criteria values ('Panel', 10, 'c1', 'ItemX') -- color
insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other characteristic
insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemZ') -- color
insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color
insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
-- in this set, duplicate input values for sizes so we have matching index
insert #Input values ('Panel', 1, '20.50') -- x value
insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
insert #Input values ('Panel', 3, '564.75') --y value
insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
insert #Input values ('Panel', 5, '6.0') -- z value
insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
insert #Input values ('Panel', 10, 'c1') -- color specied
insert #Input values ('Panel', 14, 'M1') -- mat
insert #Input values ('Back', 1, '20.50') -- x value
insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
insert #Input values ('Back', 3, '564.75') --y value
insert #Input values ('Back', 4, '564.75') --y value, same as prior index
insert #Input values ('Back', 5, '6.0') -- z value
insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
insert #Input values ('Back', 10, 'a1') -- color specied
insert #Input values ('Back', 14, 'M1') -- mat
--insert #Input values ('Panel', 15, 'a') -- mat
-- Now, let us try to match
--set nocount off
-- Multi family record match
SELECT c.ItemFamily, c.ItemMatch--, COUNT(*) as Cnt
FROM #Criteria c
JOIN #Input i
ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID
AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue end
<= i.CriteriaValue
AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue end
>= i.CriteriaValue
AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue end
= i.CriteriaValue
GROUP BY c.ItemFamily, c.ItemMatch
HAVING COUNT(*) =
(
SELECT COUNT(*) as Cnt
FROM #Input i2
WHERE i2.ItemFamily = c.ItemFamily
GROUP BY i2.ItemFamily
)
"Farmer" <someone@.somewhere.com> wrote in message
news:esqym97EGHA.3348@.TK2MSFTNGP10.phx.gbl...
> thank you all
> I did come up with an answer. Any optimizations you see?
> if object_id('tempdb..#criteria') is not null
> drop table #criteria
> go
> if object_id('tempdb..#Input') is not null
> drop table #Input
> go
> CREATE TABLE #Criteria
> (
> ItemFamily varchar(10) not null
> ,PropertyID int
> ,CriteriaValue varchar(50)
> ,ItemMatch varchar(10) not null
> ,PRIMARY KEY CLUSTERED (PropertyID, ItemFamily, ItemMatch)
> )
> CREATE TABLE #Input
> (
> ItemFamily varchar(10)
> ,PropertyID int
> ,CriteriaValue varchar(50)
> ,PRIMARY KEY CLUSTERED(PropertyID, ItemFamily)
> )
> go
> set nocount on
> -- this is defined criteria
> insert #Criteria values ('Panel', 1, '20.50', 'ItemA') -- x from
> insert #Criteria values ('Panel', 2, '20.75', 'ItemA') -- x to
> insert #Criteria values ('Panel', 3, '564.75', 'ItemA') --y from
> insert #Criteria values ('Panel', 4, '564.75', 'ItemA') --y to
> insert #Criteria values ('Panel', 5, '5.75', 'ItemA') -- z from
> insert #Criteria values ('Panel', 6, '6.75', 'ItemA') -- z to
> insert #Criteria values ('Panel', 10, 'c1', 'ItemA') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Panel', 14, 'M1', 'ItemA') -- mat
> insert #Criteria values ('Panel', 15, 'a', 'ItemA') -- other
> characteristic
> insert #Criteria values ('Panel', 1, '20.00', 'ItemX') -- x from
> insert #Criteria values ('Panel', 2, '20.75', 'ItemX') -- x to
> insert #Criteria values ('Panel', 3, '564.75', 'ItemX') --y from
> insert #Criteria values ('Panel', 4, '564.75', 'ItemX') --y to
> insert #Criteria values ('Panel', 5, '5.75', 'ItemX') -- z from
> insert #Criteria values ('Panel', 6, '6.75', 'ItemX') -- z to
> insert #Criteria values ('Panel', 10, 'a1', 'ItemX') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Panel', 14, 'M1', 'ItemX') -- mat
> insert #Criteria values ('Panel', 15, 'c', 'ItemX') -- other
> characteristic
>
> insert #Criteria values ('Back', 1, '20.50', 'ItemZ') -- x from
> insert #Criteria values ('Back', 2, '20.75', 'ItemZ') -- x to
> insert #Criteria values ('Back', 3, '564.75', 'ItemZ') --y from
> insert #Criteria values ('Back', 4, '564.75', 'ItemZ') --y to
> insert #Criteria values ('Back', 5, '5.75', 'ItemZ') -- z from
> insert #Criteria values ('Back', 6, '6.75', 'ItemZ') -- z to
> insert #Criteria values ('Back', 10, 'c1', 'ItemZ') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Back', 14, 'M1', 'ItemZ') -- mat
> insert #Criteria values ('Back', 15, 'a', 'ItemZ') -- other characteristic
> insert #Criteria values ('Back', 1, '20.00', 'ItemY') -- x from
> insert #Criteria values ('Back', 2, '20.75', 'ItemY') -- x to
> insert #Criteria values ('Back', 3, '564.75', 'ItemY') --y from
> insert #Criteria values ('Back', 4, '564.75', 'ItemY') --y to
> insert #Criteria values ('Back', 5, '5.75', 'ItemY') -- z from
> insert #Criteria values ('Back', 6, '6.75', 'ItemY') -- z to
> insert #Criteria values ('Back', 10, 'a1', 'ItemY') -- color -- defined
> but IMOS data will not have it
> insert #Criteria values ('Back', 14, 'M1', 'ItemY') -- mat
> insert #Criteria values ('Back', 15, 'c', 'ItemY') -- other characteristic
>
> -- in this set, duplicate input values for sizes so we have matching index
> insert #Input values ('Panel', 1, '20.50') -- x value
> insert #Input values ('Panel', 2, '20.50') -- x value, same as prior index
> insert #Input values ('Panel', 3, '564.75') --y value
> insert #Input values ('Panel', 4, '564.75') --y value, same as prior index
> insert #Input values ('Panel', 5, '6.0') -- z value
> insert #Input values ('Panel', 6, '6.0') -- z value, same as prior index
> insert #Input values ('Panel', 10, 'c1') -- color specied
> insert #Input values ('Panel', 14, 'M1') -- mat
> insert #Input values ('Back', 1, '20.50') -- x value
> insert #Input values ('Back', 2, '20.50') -- x value, same as prior index
> insert #Input values ('Back', 3, '564.75') --y value
> insert #Input values ('Back', 4, '564.75') --y value, same as prior index
> insert #Input values ('Back', 5, '6.0') -- z value
> insert #Input values ('Back', 6, '6.0') -- z value, same as prior index
> insert #Input values ('Back', 10, 'a1') -- color specied
> insert #Input values ('Back', 14, 'M1') -- mat
> --insert #Input values ('Panel', 15, 'a') -- mat
> -- Now, let us try to match
> --set nocount off
> -- multirecord match
> SELECT v.ItemFamily, v.ItemMatch
> FROM
> (
> SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
> FROM #Criteria c
> JOIN #Input i
> ON c.ItemFamily = i.ItemFamily
> AND c.PropertyID = i.PropertyID
> AND case when i.PropertyID = 1 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 2 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID = 3 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 4 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID = 5 then c.CriteriaValue else i.CriteriaValue
> end <= i.CriteriaValue
> AND case when i.PropertyID = 6 then c.CriteriaValue else i.CriteriaValue
> end
> AND case when i.PropertyID > 6 then c.CriteriaValue else i.CriteriaValue
> end = i.CriteriaValue
> GROUP BY c.ItemFamily, c.ItemMatch
> )v
> JOIN
> (
> SELECT ItemFamily, COUNT(*) as Cnt
> FROM #Input i
> GROUP BY i.ItemFamily
> ) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
>
>
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:ecWGFEyEGHA.376@.TK2MSFTNGP12.phx.gbl...
>|||thank you for your reply.
In my design, I try to "normalize" this type of schema.
criteria table
keyID, class, col1, col2,col3,col4, ItemMatchID
select *
from criteria
where class= @.someclassvalue and col1=@.someproperty1 AND
col2=@.someproperty12 and on to narrow or match finite ItemMatchID.
this gets complicated when the size as a property is required to support
ranges. this range (for now) in my design requires duplication so that two
edges can be compared in a record set, fromrange vs value vs torange.
Turning the above mentioned table into "tower" record set, makes it flexible
as there is no limit as to how many properties can be defined. and the
answer for matches can be answered via relational division, ie a fixed list
of properties must be contained in the criteria set.
if range would not be requred, then pure case of relational division can be
used. due to range requirement, I had to imprivise in my own way, that I had
published. Criteria is a fixed permanent table and Input will be temp table
in stored procedure.
Any better ideas?
I sent better solution in another post to relational division problem. All
this answeres my problem well.
thanks
farmer
"Razvan Socol" <RazvanSocol@.discussions.microsoft.com> wrote in message
news:5CFF4D42-2FD2-4FFC-9A45-E5CB431213F3@.microsoft.com...
> Hi, Farmer
> I think the following query is equivalent to your query:
> SELECT v.ItemFamily, v.ItemMatch
> FROM (
> SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
> FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
> AND c.PropertyID = i.PropertyID AND (
> i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
> OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
> OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
> )
> GROUP BY c.ItemFamily, c.ItemMatch
> ) v JOIN (
> SELECT ItemFamily, COUNT(*) as Cnt
> FROM #Input i
> GROUP BY i.ItemFamily
> ) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
> However, if the #Input is really a table (not a view), I don't like the
> idea of duplicating the values in the #Input table, just so you have a
> matching index. You should consider putting more meaning into the
> database: those comments that you have next to each row in the sample
> data... they should be in some table. I don't know the specifics of
> your database (and I don't think I want to know all of them), but you
> should give it another thought.
> Razvan
>|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan|||Hi, Farmer
I think the following query is equivalent to your query:
SELECT v.ItemFamily, v.ItemMatch
FROM (
SELECT c.ItemFamily, c.ItemMatch, COUNT(*) as Cnt
FROM #Criteria c JOIN #Input i ON c.ItemFamily = i.ItemFamily
AND c.PropertyID = i.PropertyID AND (
i.PropertyID IN (1,3,5) AND c.CriteriaValue<=i.CriteriaValue
OR i.PropertyID IN (2,4,6) AND c.CriteriaValue>=i.CriteriaValue
OR i.PropertyID>6 AND c.CriteriaValue=i.CriteriaValue
)
GROUP BY c.ItemFamily, c.ItemMatch
) v JOIN (
SELECT ItemFamily, COUNT(*) as Cnt
FROM #Input i
GROUP BY i.ItemFamily
) grp ON grp.ItemFamily = v.ItemFamily AND grp.Cnt = v.Cnt
However, if the #Input is really a table (not a view), I don't like the
idea of duplicating the values in the #Input table, just so you have a
matching index. You should consider putting more meaning into the
database: those comments that you have next to each row in the sample
data... they should be in some table. I don't know the specifics of
your database (and I don't think I want to know all of them), but you
should give it another thought.
Razvan
Help with Relational Division
I am trying to create a matrix of counts to help identify our data holdings.
Thanks to Joe Celko I now know I need to perform relational division to find
datasets that match the criteria set up in a secondary table.
I'd be happy to initially just get the counts for each category, as I could
pass the "DSType" parameter in on the fly.
Problem is I can't figure out how from the "Plane/Hanger" example how to
form the relational division query to come up with the counts. Or even a
view listing the datasets that match each "Category"s "Topic" criteria.
There are 4 main tables involved:
-Datasets that we provide information on.
-Types of data that they hold (Regional, Historical, Global, etc.)
-Topics that describe the datasets (Ocean, Carbon, Sea Level, etc.)
-GOOS Matrix that lists Categories that we want to correlate the datasets to
The SQL script to create the tables and insert data into them is at:
http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
A PDF of the relationships between the tables is at:
http://oceanic.cms.udel.edu/matrix/..._GOOSMatrix.pdf
And a PDF of the desired matrix view is at:
http://oceanic.cms.udel.edu/matrix/...trix_Output.pdf
TIA,
D
--
"If Pro is the opposite of Con, does that mean that Congress is the opposite
of Progress?"
--GallegherDoes this help? (using Joes's example):
CREATE TABLE PilotSkills (pilot CHAR(15) NOT NULL, plane CHAR(15) NOT
NULL, PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar (plane CHAR(15) NOT NULL PRIMARY KEY);
INSERT INTO PilotSkills (pilot, plane)
SELECT 'Celko', 'Piper Cub' UNION ALL
SELECT 'Higgins', 'B-52 Bomber' UNION ALL
SELECT 'Higgins', 'F-14 Fighter' UNION ALL
SELECT 'Higgins', 'Piper Cub' UNION ALL
SELECT 'Jones', 'B-52 Bomber' UNION ALL
SELECT 'Jones', 'F-14 Fighter' UNION ALL
SELECT 'Smith', 'B-1 Bomber' UNION ALL
SELECT 'Smith', 'B-52 Bomber' UNION ALL
SELECT 'Smith', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'B-1 Bomber' UNION ALL
SELECT 'Wilson', 'B-52 Bomber' UNION ALL
SELECT 'Wilson', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'F-17 Fighter' ;
INSERT INTO Hangar (plane)
SELECT 'B-1 Bomber' UNION ALL
SELECT 'B-52 Bomber' UNION ALL
SELECT 'F-14 Fighter' ;
SELECT PS1.pilot,
COUNT(PS1.plane) total_skills,
T.total_planes
FROM PilotSkills AS PS1, Hangar AS H1,
(SELECT COUNT(plane) FROM Hangar) AS T(total_planes)
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot, T.total_planes ;
> The SQL script to create the tables and insert data into them is at:
> http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
That's not up to the task in my opinion, which is why I've not used it.
No alternate keys and mostly nullable columns. IDENTITY should not be
the only key of any table and it's very hard to write and verify
queries against such a schema.
David Portas
SQL Server MVP
--
Thanks to Joe Celko I now know I need to perform relational division to find
datasets that match the criteria set up in a secondary table.
I'd be happy to initially just get the counts for each category, as I could
pass the "DSType" parameter in on the fly.
Problem is I can't figure out how from the "Plane/Hanger" example how to
form the relational division query to come up with the counts. Or even a
view listing the datasets that match each "Category"s "Topic" criteria.
There are 4 main tables involved:
-Datasets that we provide information on.
-Types of data that they hold (Regional, Historical, Global, etc.)
-Topics that describe the datasets (Ocean, Carbon, Sea Level, etc.)
-GOOS Matrix that lists Categories that we want to correlate the datasets to
The SQL script to create the tables and insert data into them is at:
http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
A PDF of the relationships between the tables is at:
http://oceanic.cms.udel.edu/matrix/..._GOOSMatrix.pdf
And a PDF of the desired matrix view is at:
http://oceanic.cms.udel.edu/matrix/...trix_Output.pdf
TIA,
D
--
"If Pro is the opposite of Con, does that mean that Congress is the opposite
of Progress?"
--GallegherDoes this help? (using Joes's example):
CREATE TABLE PilotSkills (pilot CHAR(15) NOT NULL, plane CHAR(15) NOT
NULL, PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar (plane CHAR(15) NOT NULL PRIMARY KEY);
INSERT INTO PilotSkills (pilot, plane)
SELECT 'Celko', 'Piper Cub' UNION ALL
SELECT 'Higgins', 'B-52 Bomber' UNION ALL
SELECT 'Higgins', 'F-14 Fighter' UNION ALL
SELECT 'Higgins', 'Piper Cub' UNION ALL
SELECT 'Jones', 'B-52 Bomber' UNION ALL
SELECT 'Jones', 'F-14 Fighter' UNION ALL
SELECT 'Smith', 'B-1 Bomber' UNION ALL
SELECT 'Smith', 'B-52 Bomber' UNION ALL
SELECT 'Smith', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'B-1 Bomber' UNION ALL
SELECT 'Wilson', 'B-52 Bomber' UNION ALL
SELECT 'Wilson', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'F-17 Fighter' ;
INSERT INTO Hangar (plane)
SELECT 'B-1 Bomber' UNION ALL
SELECT 'B-52 Bomber' UNION ALL
SELECT 'F-14 Fighter' ;
SELECT PS1.pilot,
COUNT(PS1.plane) total_skills,
T.total_planes
FROM PilotSkills AS PS1, Hangar AS H1,
(SELECT COUNT(plane) FROM Hangar) AS T(total_planes)
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot, T.total_planes ;
> The SQL script to create the tables and insert data into them is at:
> http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
That's not up to the task in my opinion, which is why I've not used it.
No alternate keys and mostly nullable columns. IDENTITY should not be
the only key of any table and it's very hard to write and verify
queries against such a schema.
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)