Use Results from a Cross Join for dynamic SQL
-
quote:
Ah! Problem is me, me thinks. Sometimes it's difficult to explain what u have and what u want to someone else but I think we are getting there. I agree that a cursor could be a solution but I am trying to keep to set based as much as possible to satisfy the purists (albeit with loops).Since we are so close can u clarify things a bit further. Based on your last post if the data is
1 A Y
1 B N
1 C Y
2 A N
2 B Y
2 C N
What exactly should the output be.
As to how long. Depends on volume. You said you have 50+ products, can u indicate how many responses there are. What is the definition of product, if you have 50+ they cannot be 1 char!!
Personally I think a cursor will be the only way to accomplish this due to the unknown factor raised when you change the number of options 3 or 50 or 100 etc. However, take a look at this script at anyone who wants to incorporate might find it will help http://www.sqlservercentral.com/scripts/contributions/204.asp. Note: There is a small bug in the script I haven't corrected yet but when I get a chance I will, if you find a way to correct let me know.
-
Can yot elaborate on this approach, using an OLAP tool might be an option for this job
Regards
quote:
This sounds more and more like the shopping basket analysis (OLAP).Old example of beer and nappies.
-
Here's a completely different method. Some explanations may be needed, but some of you get the idea. Working out the cost of each Product combination should be easy.
here goes (Code may not be std format):
/*
Drop Table ProdX
Create Table Prodx(
Rid int identity(0, 1)
,ProdName Char(1)
)
--insert into prodx select ''
insert into prodx select 'A'
insert into prodx select 'B'
insert into prodx select 'C'
Select * from Prodx
create table #Responses (Resp int, ProdName char(1), Value varchar(3))
insert into #Responses values (1, 'A', 'Yes')
insert into #Responses values (1, 'B', 'No')
insert into #Responses values (1, 'C', 'Yes')
insert into #Responses values (1, 'D', 'Yes')
insert into #Responses values (2, 'A', 'Yes')
insert into #Responses values (2, 'B', 'Yes')
insert into #Responses values (3, 'C', 'No')
insert into #Responses values (3, 'D', 'Yes')
insert into #Responses values (4, 'A', 'Yes')
insert into #Responses values (4, 'B', 'No')
insert into #Responses values (4, 'C', 'Yes')
insert into #Responses values (5, 'A', 'Yes')
insert into #Responses values (5, 'B', 'Yes')
insert into #Responses values (5, 'C', 'Yes')
insert into #Responses values (6, 'A', 'Yes')
insert into #Responses values (6, 'B', 'Yes')
insert into #Responses values (6, 'C', 'Yes')
*/
--update prodx set prodname = '' where prodname is null
/*** unique list of combinations ***********/
Drop Table #Combinations
Create Table #Combinations(
Combo int
)
Declare @ii int
Select @ii = Max( rid )+ 1 from Prodx
Select @ii = Power( 2, @ii ) - 1
While @ii > 0 begin
Insert into #Combinations Select @ii
set @ii = @ii -1
End
--Select * From #Combinations
/******** create list of respondents *******/
Drop Table #respondents
Create table #respondents(
rid int identity
,resp int
,Combo int
)
Insert into #Respondents( Resp )
Select distinct
Resp
from#Responses
--Select * from #respondents
/***** Set combinations selected by each Respondent *****/
Declare @rid int, @Combo int
Select @rid = max( rid ) from #respondents
While @rid> 0 begin
Set @Combo = 0
Select @Combo = @Combo | power(2, c.rid )
From #respondents as a
Inner join #Responses as b
on b.resp = a.resp
Inner join Prodx as c
onc.ProdName = b.Prodname
Where a.rid = @rid
Andb.value = 'yes'
Update #Respondents
Set Combo = @Combo
Where rid = @rid
Set @Rid = @rid -1
End
/***** Count number of each combination chosen ****************/
select c.combo
,Count(r.resp)
from #Combinations as c
Left Join #Respondents as r
onr.Combo = c.Combo
Group By
c.combo
Anzio
-
Sorry gents. I seem to have left some coomment markers in the code.
The top block is just to get Temporary table to work with.
-
You could code and store the different product combinations as bitmasks, where each product corresponds to a bit of the bitmask (A=1, B=2, C=4, etc.). For 3 products there are 7 possible combinations:
create table #combis (code int, products varchar(100))
insert #combis values (1,'A')
insert #combis values (2,'B')
insert #combis values (3,'AB')
insert #combis values (4,'C')
insert #combis values (5,'AC')
insert #combis values (6,'BC')
insert #combis values (7,'ABC')
Now the following query yields the desired output:
selectproducts, count(*)
from(
selectresp, pref = sum(power(2,rid))
from#responses r,
prodx p
wherep.prodname = r.prodname
andvalue = 'Yes'
group by resp
) t,
#combis
wherecode & pref > 0
group by code, products
order by len(products), products
The filter code & pref > 0 gives the number of respondents that would buy at least one product of the given product combination. For a variable number of products the #combis table can easily be populated dynamically by using a loop or a cursor. The maximum number of different products with this method is 31, but extra code fields may be added if needed.
Jorg Jansen
Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands
Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands -
AKShah1,
There is an error in the code I sent you
Here is the corrected code:
Drop Table #ProdX
Create Table #Prodx(
Rid int identity(0, 1)
,ProdName Char(1)
)
--insert into prodx select ''
insert into #prodx select 'A'
insert into #prodx select 'B'
insert into #prodx select 'C'
Drop Table #Responses
create table #Responses (Resp int, ProdName char(1), Value varchar(3))
insert into #Responses values (1, 'A', 'Yes')
insert into #Responses values (1, 'B', 'No')
insert into #Responses values (1, 'C', 'Yes')
insert into #Responses values (1, 'D', 'Yes')
insert into #Responses values (2, 'A', 'Yes')
insert into #Responses values (2, 'B', 'Yes')
insert into #Responses values (3, 'C', 'No')
insert into #Responses values (3, 'D', 'Yes')
insert into #Responses values (4, 'A', 'Yes')
insert into #Responses values (4, 'B', 'No')
insert into #Responses values (4, 'C', 'Yes')
insert into #Responses values (5, 'A', 'Yes')
insert into #Responses values (5, 'B', 'Yes')
insert into #Responses values (5, 'C', 'Yes')
insert into #Responses values (6, 'A', 'Yes')
insert into #Responses values (6, 'B', 'Yes')
insert into #Responses values (6, 'C', 'Yes')
--update prodx set prodname = '' where prodname is null
/*** unique list of combinations ***********/
Drop Table #Combinations
Create Table #Combinations(
Combo int,
Prods varchar(100)
)
Declare @ii int, @Prod varchar(10)
Select @ii = Max( rid )+ 1 from #Prodx
Select @ii = Power( 2, @ii ) - 1
While @ii > 0 begin
Set @Prod = ''
Select @Prod = LTrim( @Prod + ' ' + ProdName )
From #Prodx
Where @ii & Power(2, rid ) = power( 2, rid )
Insert into #Combinations Select @ii, @Prod
set @ii = @ii -1
End
--==******* create list of respondents *******
Drop Table #respondents
Create table #respondents(
rid int identity
,resp int
,Combo int
)
Insert into #Respondents( Resp )
Select distinct
Resp
from #Responses
--Select * from #respondents
--***** Set combinations selected by each Respondent *****
Declare @rid int, @Combo int
Select @rid = max( rid ) from #respondents
While @rid> 0 begin
Set @Combo = 0
Select @Combo = @Combo | power(2, c.rid )
From #respondents as a
Inner join #Responses as b
on b.resp = a.resp
Inner join #Prodx as c
on c.ProdName = b.Prodname
Where a.rid = @rid
And b.value = 'yes'
Update #Respondents
Set Combo = @Combo
Where rid = @rid
Set @Rid = @rid -1
End
--***** Count number of each combination chosen ****************
select c.combo, c.Prods
,Count(r.resp)
from #Combinations as c
Left Join #Respondents as r
on r.Combo = c.Combo
Group By
c.combo, c.prods
Order by 1
-
Hi,
This does not work either.
A quick way to test it is to simply do something like
Select count(distinct resp)
From tblrespondent
Where Prod='A' or Prod='B'
change the where clause to the combincation you wish to test.
e.g. for only product 'A' combination we would use Where Prod='A'
Hope that clarifies things.
Regards
quote:
AKShah1,There is an error in the code I sent you
Here is the corrected code:
Drop Table #ProdX
Create Table #Prodx(
Rid int identity(0, 1)
,ProdName Char(1)
)
--insert into prodx select ''
insert into #prodx select 'A'
insert into #prodx select 'B'
insert into #prodx select 'C'
Drop Table #Responses
create table #Responses (Resp int, ProdName char(1), Value varchar(3))
insert into #Responses values (1, 'A', 'Yes')
insert into #Responses values (1, 'B', 'No')
insert into #Responses values (1, 'C', 'Yes')
insert into #Responses values (1, 'D', 'Yes')
insert into #Responses values (2, 'A', 'Yes')
insert into #Responses values (2, 'B', 'Yes')
insert into #Responses values (3, 'C', 'No')
insert into #Responses values (3, 'D', 'Yes')
insert into #Responses values (4, 'A', 'Yes')
insert into #Responses values (4, 'B', 'No')
insert into #Responses values (4, 'C', 'Yes')
insert into #Responses values (5, 'A', 'Yes')
insert into #Responses values (5, 'B', 'Yes')
insert into #Responses values (5, 'C', 'Yes')
insert into #Responses values (6, 'A', 'Yes')
insert into #Responses values (6, 'B', 'Yes')
insert into #Responses values (6, 'C', 'Yes')
--update prodx set prodname = '' where prodname is null
/*** unique list of combinations ***********/
Drop Table #Combinations
Create Table #Combinations(
Combo int,
Prods varchar(100)
)
Declare @ii int, @Prod varchar(10)
Select @ii = Max( rid )+ 1 from #Prodx
Select @ii = Power( 2, @ii ) - 1
While @ii > 0 begin
Set @Prod = ''
Select @Prod = LTrim( @Prod + ' ' + ProdName )
From #Prodx
Where @ii & Power(2, rid ) = power( 2, rid )
Insert into #Combinations Select @ii, @Prod
set @ii = @ii -1
End
--==******* create list of respondents *******
Drop Table #respondents
Create table #respondents(
rid int identity
,resp int
,Combo int
)
Insert into #Respondents( Resp )
Select distinct
Resp
from #Responses
--Select * from #respondents
--***** Set combinations selected by each Respondent *****
Declare @rid int, @Combo int
Select @rid = max( rid ) from #respondents
While @rid> 0 begin
Set @Combo = 0
Select @Combo = @Combo | power(2, c.rid )
From #respondents as a
Inner join #Responses as b
on b.resp = a.resp
Inner join #Prodx as c
on c.ProdName = b.Prodname
Where a.rid = @rid
And b.value = 'yes'
Update #Respondents
Set Combo = @Combo
Where rid = @rid
Set @Rid = @rid -1
End
--***** Count number of each combination chosen ****************
select c.combo, c.Prods
,Count(r.resp)
from #Combinations as c
Left Join #Respondents as r
on r.Combo = c.Combo
Group By
c.combo, c.prods
Order by 1
-
The Idea Works you Are just not Querying the tables correctly
Add this to the end of the script to do what you want:
Select Count(Distinct resp )
From
#Combinations as c
Left Join #Respondents as r
on r.Combo = c.Combo
Left Join #Prodx as x
onc.combo & power(2, x.rid ) > 0
Where x.Prodname in( 'a', 'b' )
-
Ok, had to use a cursor, try this.
create table #tblResponses (Resp int, ProdName char(1), Value varchar(3))
insert into #tblResponses values (1, 'A', 'Yes')
insert into #tblResponses values (1, 'B', 'No')
insert into #tblResponses values (1, 'C', 'Yes')
insert into #tblResponses values (1, 'D', 'Yes')
insert into #tblResponses values (2, 'A', 'Yes')
insert into #tblResponses values (2, 'B', 'Yes')
insert into #tblResponses values (3, 'C', 'No')
insert into #tblResponses values (3, 'D', 'Yes')
insert into #tblResponses values (4, 'A', 'Yes')
insert into #tblResponses values (4, 'B', 'No')
insert into #tblResponses values (4, 'C', 'Yes')
--Create table of unique products
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod SELECT DISTINCT ProdName FROM #tblResponses ORDER BY ProdName
--Count them
DECLARE @ProdCT int,@CT int,@Keys varchar(7000),@Combi varchar(100),@ProdName char(1),@TempKeys varchar(7000)
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
--Create table of all unique product combinations
INSERT INTO #AllProd2 SELECT ProdName FROM #prod
INSERT INTO #AllProd SELECT Combi FROM #AllProd2
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllProd3
INSERT INTO #AllProd3 SELECT a.Combi+b.ProdName FROM #AllProd2 a CROSS JOIN #prod b WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
INSERT INTO #AllProd SELECT Combi FROM #AllProd3
TRUNCATE TABLE #AllProd2
INSERT INTO #AllProd2 SELECT Combi FROM #AllProd3
END
--create table of Yes responses
CREATE TABLE #resp (Resp int,ProdName char(1))
INSERT INTO #resp SELECT Resp,ProdName FROM #tblResponses WHERE Value = 'Yes'
CREATE TABLE #AllResp (Combi varchar(100),Keys varchar(100),KeyCount int)
CREATE TABLE #AllResp2 (Combi varchar(100),Keys varchar(100),KeyCount int)
CREATE TABLE #AllResp3 (Combi varchar(100),Keys varchar(100),KeyCount int)
--create table of products and responses
SET @CT = 1
WHILE @CT <= @ProdCT
BEGIN
SET @Keys=''
SELECT @Keys=@Keys+CONVERT(varchar,r.Resp)+',' FROM #resp r INNER JOIN #prod p ON p.ProdName = r.ProdName AND p.ProdID = @CT
INSERT INTO #AllResp2 SELECT ProdName,@Keys,0 FROM #prod WHERE ProdID = @CT
END
INSERT INTO #AllResp SELECT Combi,Keys,KeyCount FROM #AllResp2
--Add combinations
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllResp3
--Get new combinations
INSERT INTO #AllResp3
SELECT DISTINCT a.Combi+b.ProdName,a.Keys,0
FROM #AllResp2 a
CROSS JOIN #resp b
WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
--for each new combination get responses
DECLARE curs CURSOR FOR SELECT Combi,Keys FROM #AllResp3
OPEN curs
FETCH NEXT FROM curs INTO @Combi,@TempKeys
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Keys=''
SET @ProdName = RIGHT(@Combi,1)
SELECT @Keys=@Keys+CONVERT(varchar,r.Resp)+',' FROM #resp r WHERE r.ProdName = @ProdName AND CHARINDEX(CONVERT(varchar,r.Resp)+',',@TempKeys)=0
--and add to record
UPDATE #AllResp3 SET Keys=Keys+@Keys WHERE CURRENT OF curs
FETCH NEXT FROM curs INTO @Combi,@TempKeys
END
CLOSE curs
DEALLOCATE curs
--put new combinations and responses in table
INSERT INTO #AllResp SELECT Combi,Keys,KeyCount FROM #AllResp3
TRUNCATE TABLE #AllResp2
INSERT INTO #AllResp2 SELECT Combi,Keys,KeyCount FROM #AllResp3
END
--Update combination count
SELECT @CT = COUNT(*) FROM #AllResp WHERE Keys <> ''
WHILE @CT > 0
BEGIN
UPDATE #AllResp SET KeyCount=KeyCount+1,Keys=REPLACE(Keys,LEFT(Keys,CHARINDEX(',',Keys)),'') WHERE Keys <> ''
SELECT @CT = COUNT(*) FROM #AllResp WHERE Keys <> ''
END
--Show list
SELECT a.Combi,ISNULL(r.KeyCount,0) AS 'Count'
FROM #AllProd a
LEFT OUTER JOIN #AllResp r ON r.Combi = a.Combi
ORDER BY LEN(a.Combi),a.Combi
Far away is close at hand in the images of elsewhere.
Anon. -
Problem solved
--------------
Hi Just wanted to thank Dave Barrows on solving this one!! I tested his solution with 15 variables (A-O) and with 300 responses the solution time took approx 20min. On our older unix program it takes 15 mins, but thats a much better spec machine.
I am still exploring the angle from the approach taken by Anzio, as it might be better in performance.
Dave's solution can be modiffied to include more variables by simply increasing the column length, although again this impacts performance.
Heres the final solution from Dave that gives the correct results:
create table #tblResponses (Resp int, ProdName char(1), Value varchar(3))
insert into #tblResponses values (1, 'A', 'Yes')
insert into #tblResponses values (1, 'B', 'No')
insert into #tblResponses values (1, 'C', 'Yes')
insert into #tblResponses values (1, 'D', 'Yes')
insert into #tblResponses values (2, 'A', 'Yes')
insert into #tblResponses values (2, 'B', 'Yes')
insert into #tblResponses values (3, 'C', 'No')
insert into #tblResponses values (3, 'D', 'Yes')
insert into #tblResponses values (4, 'A', 'Yes')
insert into #tblResponses values (4, 'B', 'No')
insert into #tblResponses values (4, 'C', 'Yes')
insert into #tblResponses values (5, 'A', 'Yes')
insert into #tblResponses values (5, 'B', 'Yes')
insert into #tblResponses values (5, 'C', 'Yes')
insert into #tblResponses values (6, 'A', 'Yes')
insert into #tblResponses values (6, 'B', 'Yes')
insert into #tblResponses values (6, 'C', 'Yes')
CREATE TABLE #prod (ProdID int IDENTITY(1,1),ProdName char(1))
INSERT INTO #prod VALUES ('A')
INSERT INTO #prod VALUES ('B')
INSERT INTO #prod VALUES ('C')
INSERT INTO #prod VALUES ('D')
INSERT INTO #prod VALUES ('E')
INSERT INTO #prod VALUES ('F')
INSERT INTO #prod VALUES ('G')
INSERT INTO #prod VALUES ('H')
CREATE TABLE #AllProd (Combi varchar(100))
CREATE TABLE #AllProd2 (Combi varchar(100))
CREATE TABLE #AllProd3 (Combi varchar(100))
INSERT INTO #AllProd2 SELECT ProdName FROM #prod
INSERT INTO #AllProd SELECT Combi FROM #AllProd2
DECLARE @ProdCT int,@CT int
SET @ProdCT = (SELECT COUNT(*) FROM #prod)
SET @CT = 2
WHILE @CT <= @ProdCT
BEGIN
TRUNCATE TABLE #AllProd3
INSERT INTO #AllProd3 SELECT a.Combi+b.ProdName FROM #AllProd2 a CROSS JOIN #prod b WHERE CHARINDEX(b.ProdName,a.Combi)=0 AND b.ProdName > RIGHT(a.Combi,1)
INSERT INTO #AllProd SELECT Combi FROM #AllProd3
TRUNCATE TABLE #AllProd2
INSERT INTO #AllProd2 SELECT Combi FROM #AllProd3
END
SELECT a.Combi,COUNT(DISTINCT RESP)
FROM #AllProd a
LEFT OUTER JOIN #tblResponses r ON CHARINDEX(r.ProdName,a.Combi)>0 AND r.Value = 'Yes'
GROUP BY a.Combi
ORDER BY a.Combi
Edited by - AKshah1 on 05/09/2003 08:27:48 AM
-
I obviously missunderstood the question. If that is what you want just change the final query to this.
Select C.PRODS, Count(Distinct resp )
From
#Combinations as c
Left Join #Respondents as r
on r.Combo & c.Combo > 0
Left Join #Prodx as x
onpower(2, x.rid )& c.combo > 0
GROUP BY C.PRODS
Using bits has some limitations, i.e If you are using integer you are limited to 32 products. You could use big int and get 64, else you have to use something like below ( I used tinyint (8 bit ) just to illustrate the idea. ) :
--Variables =========================================
Declare @ii int
,@PL Int
,@Prods varchar(7000)
,@P2 varchar(100)
,@P3 varchar(100)
,@P4 varchar(100)
-- Products =========================================
Drop Table #ProdX
Create Table #Prodx(
Rid int identity(0, 1)
,Level As Floor( Rid / 8 )
,RI As Convert( tinyint, ( Rid % 8 ) )
,PKey As Convert( varchar(6), Floor( Rid / 8 ) ) + '-' +
Convert( Varchar(6), ( Rid % 8 ) )
,ProdName Char(1)
)
Select @ii = 0
While @ii < 16 Begin
insert into #prodx select Char( 65 + @ii )
Set @ii = @ii + 1
End
--Select * From #Prodx
Drop Table #Responses
create table #Responses (Resp int, ProdName char(1), Value varchar(3))
insert into #Responses values (1, 'A', 'Yes')
insert into #Responses values (1, 'B', 'No')
insert into #Responses values (1, 'C', 'Yes')
insert into #Responses values (1, 'D', 'Yes')
insert into #Responses values (2, 'A', 'Yes')
insert into #Responses values (2, 'B', 'Yes')
insert into #Responses values (3, 'C', 'No')
insert into #Responses values (3, 'D', 'Yes')
insert into #Responses values (4, 'A', 'Yes')
insert into #Responses values (4, 'B', 'No')
insert into #Responses values (4, 'C', 'Yes')
insert into #Responses values (5, 'A', 'Yes')
insert into #Responses values (5, 'B', 'Yes')
insert into #Responses values (5, 'C', 'Yes')
insert into #Responses values (6, 'A', 'Yes')
insert into #Responses values (6, 'B', 'Yes')
insert into #Responses values (6, 'C', 'Yes')
-- Possible Numbers =======================================
Drop Table #Digits
Create Table #digits(
No int
)
Set @ii = 0
While @ii < 10 Begin
Insert into #digits Values( @ii )
Set @ii = @ii + 1
End
--Select * from #Digits
/*** unique list of combinations ***********/
Drop Table #Combinations
Create Table #Combinations(
Level int
,Combo int,
Prods varchar(7000)
)
Select @PL = Max( Level ) From #Prodx
While @Pl >= 0 Begin
Select @ii = Power( 2, Max( ri )+ 1 ) - 1 from #Prodx Where Level = @Pl
While @ii > 0 begin
Set @Prods = ''
Select @Prods = LTrim( @Prods + ProdName )
From #Prodx
Where Level = @Pl
And@ii & Power(2, ri ) = power( 2, ri )
Insert Into #Combinations ( Level, Combo, Prods )
Select @Pl, @ii, @Prods
set @ii = @ii -1
End
Set @PL = @Pl - 1
End
--Select * From #Combinations order by 1, 2
--==******* create list of respondents *******
Drop Table #respondents
Create table #respondents(
rid int identity
,resp int
,Level int
,Combo int
)
Insert into #Respondents( Resp )
Select distinct
Resp
from #Responses
--Select * from #respondents
--***** Set combinations selected by each Respondent *****
Declare @rid int, @Combo int
Select @rid = max( rid ) from #respondents
While @rid> 0 begin
Select @PL = Max( Level ) From #Prodx
While @Pl >= 0 Begin
Set @Combo = 0
Select @Combo = @Combo | power(2, c.rid )
From #respondents as a
Inner join #Responses as b
on b.resp = a.resp
Inner join #Prodx as c
on c.ProdName = b.Prodname
Where a.rid = @rid
And b.value = 'yes'
Andc.Level = @pl
Update #Respondents
Set Combo = @Combo
Where rid = @rid
Andlevel = @pl
Insert Into #Respondents( resp, level, combo )
Select Resp, @pl, @Combo
From #Respondents
Whererid = @rid
Set @pl= @pl -1
End
Set @Rid = @rid -1
End
--Select * from #respondents
--***** Count number of each combination chosen ****************
/*
select c.combo, c.Prods
,Count(r.resp)
from #Combinations as c
Left Join #Respondents as r
on r.Combo & c.Combo > 0
Andr.level = c.level
Group By
c.combo, c.prods
Order by 2
*/
Select C.PRODS, c.level, c.combo, Count(Distinct resp )
From
#Combinations as c
Left Join #Respondents as r
on r.Combo & c.Combo > 0
Andr.level = c.level
Left Join #Prodx as x
onpower(2, x.rid )& c.combo > 0
Where x.Prodname in( 'g', 'o' )
GROUP BY C.PRODS, c.level, c.combo
If you are still interested there is a stored procedure solution using the original Solution.
-
Do we have a winner? Or do we think there might yet be a better, faster solution?
Andy
-
Anzios final solution was sligtly faster then Daves with my data, however Daves seems to perform better when there are more variables.
Both are very good, and I would appreciate some public opinion as to which is the best.
Regards
Manoj
quote:
Do we have a winner? Or do we think there might yet be a better, faster solution?Andy
http://www.sqlservercentral.com/columnists/awarren/
-
How about this - we'll call it a tie! Anzio/Dave, I'll contact you offline about letting you pick a book.
Akshah1 - thanks for coming up with a great problem to solve!
Readers, feel free to post to this topic if you have a better idea, but as of now the contest is complete.
Andy
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply