Just trying to find out if this was the best solution.  I used the derived
table solution of a couple of posts ago.

What I am trying to do is to select a record from a table and compare the
fields to fields in another table.

For example, if I have a table with criteria of age, I want to see if the
age of the users  is greater than that and assign a value of 1 if it is and
0 if not.  Then if I have a criteria of whether he plays tennis or not - 1
if yes and 0 if no.  Then at the end, add up all the values for a total in
each record.  I could do the first part OK, but was having trouble with the
total so I tried the derived table and it seemed to work.

If I have the following tables and I want to compare the criteria like so:
Cat1 = Criteria1 then we have 1 else 0, Cat 2 < Criteria2 we have 1 else 0,
Cat3 > Criteria3 we have 1 else 0 and if Cat4 >= Critera4 we have 1 else 0.

For Tom the results would be:
I would compare the values with ProjectID = 100
Cat1(25) not = Criteria1(35) so = 0
Cat2(35) is < Criteria2(80) so 1
Cat3(75) is > Criteria3(15) so 1
Cat4(40) is > Criteria4(23) so 1 with a total of 3 (0+1+1+1)

DROP TABLE Projects
CREATE TABLE [dbo].[Projects] (
  [ProjectID] [int] NULL ,
  [Criteria1] [int] NULL ,
  [Criteria2] [int] NULL ,
  [Criteria3] [int] NULL ,
  [Criteria4] [int] NULL
) ON [PRIMARY]
GO
DROP TABLE Users
CREATE TABLE [dbo].[Users] (
  [UserName] [varchar] (30) NULL ,
  [ProjectID] [int] NULL ,
  [Cat1] [int] NULL ,
  [Cat2] [int] NULL ,
  [Cat3] [int] NULL ,
  [Cat4] [int] NULL
) ON [PRIMARY]
GO
INSERT Projects
(ProjectID,Criteria1,Criteria2,Criteria3,Criteria4)values(100,35,80,15,23)
INSERT Projects
(ProjectID,Criteria1,Criteria2,Criteria3,Criteria4)values(200,95,22,42,3)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Tom',100,25,35,75,40)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Larry',100,15,3,18,22)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Greg',100,100,100,100,1
00)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Mark',100,23,80,5,2)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Joe',100,50,50,50,50)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Frank',200,20,20,20,5)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Sam',200,25,22,100,80)
INSERT
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Alan',200,35,100,82,15)

To get the Values 1 or 0, I did:

SELECT Value1=CASE WHEN Cat1 = Criteria1 THEN 1 ELSE 0 END,
  Value2=CASE WHEN Cat2 < Criteria2 THEN 1 THEN 0 END,
  Value3=CASE WHEN Cat3 > Criteria3 THEN 1 THEN 0 END,
  Value4=CASE WHEN Cat4 >= Criteria4 THEN 1 THEN 0 END
FROM Projects p JOIN Users u ON (p.ProjectID = u.ProjectID)

And Got:

Value1      Value2      Value3      Value4
----------- ----------- ----------- -----------
0           1           1           1
0           1           1           0
0           0           1           1
0           0           0           0
0           1           1           1
0           1           0           1
0           0           1           1
0           0           1           1

(8 row(s) affected)

To get the Total I wrapped another Select around it:

SELECT Value1,Value2,Value3,Value4,Total = Value1+Value2+Value3+Value4
from (SELECT Value1=CASE WHEN Cat1 = Criteria1 THEN 1 ELSE 0 END,
  Value2=CASE WHEN Cat2 < Criteria2 THEN 1 ELSE 0 END,
  Value3=CASE WHEN  Cat3 > Criteria3 THEN 1 ELSE 0 END,
  Value4=CASE WHEN Cat4 >= Criteria4 THEN 1 ELSE 0 END
FROM Projects p JOIN Users u ON (p.ProjectID = u.ProjectID)) AS a

And got:
Value1      Value2      Value3      Value4      total
----------- ----------- ----------- ----------- -----------
0           1           1           1           3
0           1           1           0           2
0           0           1           1           2
0           0           0           0           0
0           1           1           1           3
0           1           0           1           2
0           0           1           1           2
0           0           1           1           2

(8 row(s) affected)

Just curious if this was the best way?

Thanks,

Tom

Show quote Hide quote Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Larry',100,15,3,18,22)
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Greg',100,100,100,100,1
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Frank',200,20,20,20,5)
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Sam',200,25,22,100,80)
Users(UserName,ProjectID,Cat1,Cat2,Cat3,Cat4)values('Alan',200,35,100,82,15)
Show quoteHide quoteI also wanted to get a message if all/most/none of criteria are met:

select Value1,Value2,Value3,Value4,total=Value1+value2+value3+value4,
  Case when Value1+value2+value3+value4 = 4 then 'Meets all'
       when Value1+value2+value3+value4 > 2 then 'Meets Most'
             else 'Fails To Meet' end
from (select  Value1=case when Cat1 = Criteria1 then 1 else 0 end,
  Value2=case when Cat2 < Criteria2 then 1 else 0 end,
  Value3=case when Cat3 > Criteria3 then 1 else 0 end,
  Value4=case when Cat4 >= Criteria4 then 1 else 0 end
from Projects p join Users u on (p.ProjectID = u.ProjectID)) as a

Gives me:

alue1      Value2      Value3      Value4      total
----------- ----------- ----------- ----------- ----------- -------------
0           1           1           1           3           Meets Most
0           1           1           0           2           Fails To Meet
0           0           1           1           2           Fails To Meet
0           0           0           0           0           Fails To Meet
0           1           1           1           3           Meets Most
0           1           0           1           2           Fails To Meet
0           0           1           1           2           Fails To Meet
0           0           1           1           2           Fails To Meet

(8 row(s) affected)

In my case statements where I analyse the totals to get the verbage I have
to run the calculations each time (Value1+value2+value3+value4 ).

Is there a better way to do this?  In my actual file, I will be doing about
40 comparisons, which means SQL will have to recalculate 40 times for each
record.

Thanks,

Tom
Show quoteHide quote

snip
Show quoteHide quoteOne last problem, I wanted to get the name to display, but I can't seem to
get it to work.  I end up with 34 rows instead of 8 when I do:

select
UserName,Value1,Value2,Value3,Value4,total=Value1+value2+value3+value4,
  Case when Value1+value2+value3+value4 = 4 then 'Meets all'
       when Value1+value2+value3+value4 > 2 then 'Meets Most'
             else 'Fails To Meet' end
from (select p.ProjectID,Value1=case when Cat1 = Criteria1 then 1 else 0
end,
  Value2=case when Cat2 < Criteria2 then 1 else 0 end,
  Value3=case when Cat3 > Criteria3 then 1 else 0 end,
  Value4=case when Cat4 >= Criteria4 then 1 else 0 end
from Projects p join Users u on (p.ProjectID = u.ProjectID)) as a
Join users u2 on (a.ProjectID = u2.ProjectID)

I tried to do the join as (p.ProjectID = u2.ProjectID) and (u.ProjectID =
u2.ProjectID), but that doesn't work - gives me an error.

Tom

Show quoteHide quote

Why do you have an extra join to users?  Why not just add u.UserName to the
subquery?

By the way, Hurricane Celko will probably be making landfall soon.  Putting
the terms "fields" and "records" in the subject line is an engraved
invitation for one of his canned drubbings.

Show quoteHide quote

I thought of that and may do that.  I would have to put it in twice.  In my
actual statement, there are about 20 "fields" (just kidding) that I have to
display, so that would mean putting 20 fields in twice - which probably
isn't a bad thing, just looking for the best way to do it and maybe this is
it.

I know I'm going to get it. :)

Thanks,

Tom
Show quoteHide quote

In fairness, this guy constantly posts some of the worst stuff in the
newsgroup.  He does not know the basics and have obviously not bother
to read even one book.  His entire mental model is based on a 1950's
COBOL file model and needlessly proprietary syntax.  .

Not trying to be a star.

Could be - but my training is in Fortran and C - not Cobol, BTW.

Maybe so, but it's been my experience that a polite admonishment goes a lot
farther than a boorish dressing-down.

Show quoteHide quote

On Thu, 3 Nov 2005 23:59:23 -0800, tshad wrote:

(snip)
Show quoteHide quoteHi Tom,

You can get rid of one of the three current additions by changing the
CASE expression::

CASE Value1+value2+value3+value4
WHEN 4 THEN 'Meets all'
WHEN 3 THEN 'Meets Most'
ELSE 'Fails to Meet'
END

You can get rid of all but one of them by repeating the derived table
trick, giving you a nested derived table:

SELECT Value1, Value2, Value3, Value4, Total,
       CASE Total WHEN 4 THEN 'Meets all' /* etc */ END
FROM  (SELECT Value1, Value2, Value3, Value4,
              Value1 + Value2 + Value3 + Value4 AS Total
       FROM  (SELECT Value1 = CASE ... END,
                     Value2 = CASE ... END,
                     ...
              FROM   ...) AS a) AS b

Best, Hugo

Show quote Hide quote This worked pretty well.  Haven't tried the one below yet.  What would the
impact on the system be going to the nested derived table verses just doing
the multiple additions?

Also, this was just a test case.  What I am doing is 2 sets of additions
where I have a set of categories and a set of values (one cat for each
value, ie cat1, value1, cat2,value2, cat3, cat3...) so my case statement
looks like:

CASE WHEN (Cat1+Cat2+Cat3) = (Value1+Value2+Value3) THEN 'Meets all'
           WHEN (Value1+Value2+Value3) >= ((Cat1+Cat2+Cat3)/2) THEN "Meets
Most'
            ELSE 'Fails' END

Can I put some of this before the WHEN statement as you did above to shorten
it?

Thanks,

Tom
Show quoteHide quote

On Tue, 8 Nov 2005 16:50:11 -0800, tshad wrote:

Show quoteHide quoteHi Tom,

I'm not in a position to tell. You've got the database with the tables
and the data, you've got the hardware that this will have to run on. So
why don't you just test both versions, then compare execution plans, IO
statistics and elapsed time? (Remember to clean your cache before each
test).

That being said, I *think* that you'd get identical execution plans for
both. But that's just what I *think* - the only way to *know* is by
testing.


No. The "simple CASE" (as it is called) can only be used if one
expression (that you list after the CASE keyword) has to be compared for
equality against other expressions (listed after the WHEN keywords).
Only the "searched CASE" can be used for other comparisons.

Best, Hugo

Show quote Hide quote Ok.

It doesn't make that much difference, just thought I'd check.

Thanks,

Tom
Show quoteHide quote

Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC
grouping and showing concatenated varchar column?