SP Help, Is there a better way? 2

[ecobb]

I have a Stored Procedure that does exactly what I want, but I'm wondering if there's a better way to write it. Instead of returning individual variables, I would like to just return a query recordset with the same info.

The info I'm trying to pull is actually pretty simple. In my table, I have several bit columns for Sales, Quality, Production, etc... I need to get a count of how many of each of them have a value of 1. What I have below works, I just wind up with 16 individual variables. I would like to have each of them as a value in a query instead.

Set @Totals = (Select Count(*) From dbo.Incidents Where Removed = 0)
Set @Sales = (Select Count(*) From dbo.Incidents Where Removed = 0 And Sales = 1)
Set @Quality = (Select Count(*) From dbo.Incidents Where Removed = 0 And Quality = 1)
Set @Production = (Select Count(*) From dbo.Incidents Where Removed = 0 And Production = 1)
Set @Complaint = (Select Count(*) From dbo.Incidents Where Removed = 0 And Complaint = 1)
Set @Observation = (Select Count(*) From dbo.Incidents Where Removed = 0 And Observation = 1)
Set @OrderEntry = (Select Count(*) From dbo.Incidents Where Removed = 0 And OrderEntry = 1)
Set @Damage = (Select Count(*) From dbo.Incidents Where Removed = 0 And Damage = 1)
Set @Injury = (Select Count(*) From dbo.Incidents Where Removed = 0 And Injury = 1)
Set @Service = (Select Count(*) From dbo.Incidents Where Removed = 0 And Service = 1)
Set @Loading = (Select Count(*) From dbo.Incidents Where Removed = 0 And Loading = 1)
Set @ProductReturn = (Select Count(*) From dbo.Incidents Where Removed = 0 And ProductReturn = 1)
Set @Hauling = (Select Count(*) From dbo.Incidents Where Removed = 0 And Hauling = 1)
Set @Credit = (Select Count(*) From dbo.Incidents Where Removed = 0 And Credit = 1)
Set @UndeliveredLoad = (Select Count(*) From dbo.Incidents Where Removed = 0 And UndeliveredLoad = 1)
Set @WaitTime = (Select Count(*) From dbo.Incidents Where Removed = 0 And WaitTime = 1)

Is this something that can be easily done, or should I just keep going like I have it? I'm using SQL Server 2000 if that helps.



Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[monksnake]

I have a question, are the only valid values for (example) the column Sales 1 and 0??

<.

 

[ecobb]

Yes, they are all 'bit' columns with a default of '0', so every value will be either a 1 or 0.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[gmmastros]

[COLOR=blue]Select[/color] @Totals  = [COLOR=#FF00FF]Count[/color](*),
       @Sales   = Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Sales = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]),
       @Quality = Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Quality = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]),
       @Production = Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Production = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]),
       @Complaint = Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Complaint = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]),
       etc....
[COLOR=blue]From[/color]   dbo.Incidents
[COLOR=blue]Where[/color]  Removed = 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Crowley16]

you could use a union query to select the results in one go instead of setting them to parameters, e.g.

select blah from blah = blah
union select blah1 from blah1 = blah1

or you could create a temp table/variable and just dump the values into there and finally select from the table...


--------------------
Procrastinate Now!

 

[roccorocks]

You could read up on the CASE structure, then you could have something like this:
DECLARE @TEST
DECLARE @TEST2
-- Declare the rest of your variables

SELECT @TEST = CASE WHEN Crit1 = 1 AND Crit2 = 0 THEN RetValue END, @TEST2 = CASE WHEN Crit1 = 1 THEN RetValue END FROM EstimBidPlan

Thanks,

Tim

 

[monksnake]

Ok ECAR, then this can be easy, the following query will return 1 row with all the info you need.

Select count(*) as TotalCount, sum(Sales), sum(Quality), sum(Production) ....
From   dbo.Incidents
Where  Removed = 0

<.

 

[gmmastros]

Monksnake,

Since they are bit fields, you cannot use SUM directly, or you will get this...

[!]The sum or average aggregate operation cannot take a bit data type as an argument.[/!]

You could convert the bit field to integer, or use the case structure I show above.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Oooops, my bad George, thanks for straightening me out.

I did a test on non bit fields and I assumed too much.

<.

 

[monksnake]

Let me fix my post:

Select count(*) as TotalCount, sum(convert(int, Sales)), sum(convert(int, Quality)), sum(convert(int, Production)) ....
From   dbo.Incidents
Where  Removed = 0

My apologies.

<.

 

[SQLDenis]

using CASE or SUM by converting to int will produce the same execution plan
However SUM and converting to int seems like less work

create table blahbit (col1 bit, col2 bit)
insert blahbit
select 1,1 union all
select 1,1 union all
select 1,1 union all
select 1,0


select sum(convert(int,col1)),sum(convert(int,col2))
from blahbit


select   col1 = Sum(Case When col1 = 1 Then 1 Else 0 End),
       col2 = Sum(Case When col2 = 1 Then 1 Else 0 End)
from blahbit

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ecobb]

Sweet! You guys rock! I don't know why it never occurred to me to use Sum. Thanks for that, monksnake, and thanks for pointing out the need to convert it gmmastros . The below combined result works perfectly!

Select count(*) as TotalCount, sum(Convert(int,Sales)), sum(Convert(int,Quality)), sum(Convert(int,Production))...
From   dbo.Incidents
Where  Removed = 0

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

I guess I was writing it the same time you were posting it monksnake. Thanks!


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[monksnake]

to ECAR and George

<.