Is there a better way to do this ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is there a better way to do this ...

Author

Topic

LaurieCox

158 Posts

Posted - 2007-01-16 : 15:07:18

Note: I have no control over the design of the original table.

Problem: Get all records where the total score is greater than or equal to 120.

ddl and sample data:


Create table #scores
(conid varchar(11),
sc1 varchar(5),
sc2 varchar(5),
sc3 varchar(5),
sc4 varchar(5),
sc5 varchar(5),
sc6 varchar(5),
sc7 varchar(5),
sc8 varchar(5))

Insert into #scores (conid, sc1, sc2, sc3, sc4, sc5, sc6, sc7, sc8)
select '00000031959', '30.00', '20.00', '00.00', '20.00', '20.00', '00.00', '00.00', '00.00' union all
select '00000006499', '30.00', '20.00', '30.00', '20.00', '00.00', '00.00', '00.00', '20.00' union all
select '00000027416', '20.00', '20.00', '10.00', '20.00', '10.00', '00.00', '00.00', '10.00' union all
select '00225532601', '30.00', '20.00', '20.00', '20.00', '20.00', '00.00', '10.00', '00.00' union all
select '00000023042', '20.00', '20.00', '20.00', '20.00', '10.00', '00.00', '10.00', '00.00' union all
select '00000005850', '20.00', '20.00', '00.00', '20.00', '30.00', '10.00', '00.00', '10.00' union all
select '00000002860', '20.00', '30.00', '20.00', '30.00', '30.00', '30.00', '00.00', '00.00' union all
select '00000511704', '30.00', '30.00', '00.00', '30.00', '20.00', '10.00', '30.00', '10.00' union all
select '00000021864', '20.00', '10.00', '00.00', '20.00', '20.00', '00.00', '00.00', '10.00' union all
select '00312048414', '30.00', '10.00', '00.00', '20.00', '20.00', '00.00', '00.00', '00.00' union all
select '0000mi35921', '20.00', '10.00', '30.00', '10.00', '20.00', '00.00', '00.00', '00.00' union all
select '00000029395', '20.00', '10.00', '00.00', '10.00', '20.00', '00.00', '00.00', '00.00' union all
select '00000032266', '20.00', '30.00', '30.00', '30.00', '20.00', '00.00', '00.00', '20.00' union all
select '00000007551', '30.00', '30.00', '30.00', '20.00', '00.00', '00.00', '10.00', '00.00' union all
select '00000026967', '20.00', '10.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00'

I have two possible solutions (both work):

Solution 1:


select conid, sc1,sc2,sc3,sc4,sc5,sc6,sc7,sc8,
       (cast(sc1 as real)  + cast(sc2 as real)  + cast(sc3 as real)  + cast(sc4 as real)  + 
        cast(sc5 as real)  + cast(sc6 as real)  + cast(sc7 as real)  + cast(sc8 as real)) as tot_score
  from #scores
 where (cast(sc1 as real) + cast(sc2 as real)  + cast(sc3 as real)  + cast(sc4 as real)  + 
            cast(sc5 as real)  + cast(sc6 as real)  + cast(sc7 as real)  + cast(sc8 as real)) >= 120.0

I tried replacing the where statement with where tot_score >= 120.0 but got the following error:
Invalid column name 'tot_score'.

Solution 2:
I created a function called addScores:


CREATE FUNCTION AddScores  
	(@sc1 as varchar(5),
	@sc2 as varchar(5),
	@sc3 as varchar(5),
	@sc4 as varchar(5),
	@sc5 as varchar(5),
	@sc6 as varchar(5),
	@sc7 as varchar(5),
	@sc8 as varchar(5))

RETURNS real
AS
begin
	return cast(@sc1 as real) + cast(@sc2 as real)  + cast(@sc3 as real)  + cast(@sc4 as real)  + 
      	       cast(@sc5 as real) + cast(@sc6 as real)  + cast(@sc7 as real)  + cast(@sc8 as real)
end

and then wrote the select statement:


select conid, sc1,sc2,sc3,sc4,sc5,sc6,sc7,sc8, dbo.addscores(sc1,sc2,sc3,sc4,sc5,sc6,sc7,sc8) as tot_score
  from #scores 
 where dbo.addscores(sc1,sc2,sc3,sc4,sc5,sc6,sc7,sc8)>= 120.0

This looks cleaner but I have to call the function in both the where clause and the select clause (the >= tot_score does not work here either).

Expected results:


conid		sc1	sc2	sc3	sc4	sc5	sc6	sc7	sc8	tot_score
00000006499	30.00	20.00	30.00	20.00	00.00	00.00	00.00	20.00	120.0
00225532601	30.00	20.00	20.00	20.00	20.00	00.00	10.00	00.00	120.0
00000002860	20.00	30.00	20.00	30.00	30.00	30.00	00.00	00.00	160.0
00000511704	30.00	30.00	00.00	30.00	20.00	10.00	30.00	10.00	160.0
00000032266	20.00	30.00	30.00	30.00	20.00	00.00	00.00	20.00	150.0
00000007551	30.00	30.00	30.00	20.00	00.00	00.00	10.00	00.00	120.0

Thanks,

Laurie

P.S. There is not a front end to do this in. I will be dumping the results to an Excel file straight from Query Analyzer and must not include any records where the tot_score is less than 120.0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 15:12:32

Or you can make use of a derived table like this

select	conid,
	score
from	(
		select	conid,
			cast(sc1 as real) + cast(sc2 as real) + cast(sc3 as real) + cast(sc4 as real) + 
			cast(sc5 as real) + cast(sc6 as real) + cast(sc7 as real) + cast(sc8 as real) as score
		from	#scores
	) x
where	score >= 120.0

Peter Larsson
Helsingborg, Sweden

LaurieCox

158 Posts

Posted - 2007-01-16 : 15:24:36

Hi Peso,

Thank you.

I do not know why I have a blind spot when it comes to derived tables, but if I want to get anywhere I am going to have to overcome it.

Again thank you,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources