Stored Procedure or is there a better way?

Question

Stored Procedure or is there a better way?

mstevens92

Old Hand

Points: 320
More actions
November 8, 2009 at 10:19 am

#216527

I have a table with the following columns:
CREATE TABLE [dbo].[Trucks](
Company [nvarchar](255) NOT NULL,
License [nvarchar](255) NULL,
Year [float] NULL,
Make [nvarchar](255) NULL,
Model [nvarchar](255) NULL,
Location [nvarchar](255) NULL,
UnitNumber [float] NOT NULL,
PrimaryAir [nvarchar](255) NULL,
PrimaryAirQty [float] NULL,
SecondaryAir [nvarchar](255) NULL,
SecondaryAirQty [float] NULL,
PrimaryOil [nvarchar](255) NULL,
PrimaryOilQty [float] NULL,
SecondaryOil [nvarchar](255) NULL,
SecondaryOilQty [float] NULL,
PrimaryFuel [nvarchar](255) NULL,
PrimaryFuelQty [float] NULL,
SecondaryFuel [nvarchar](255) NULL,
SecondaryFuelQty [float] NULL)
and I worked out a way to count a particular filter with the following:
SELECT DISTINCT PrimaryAir, count(PrimaryAir) as AirFilters
From dbo.Trucks
Where Company = 'XYZ'
GROUP BY PrimaryAir
The problem is I have a collum for each filter quantity (PrimaryAirQty), and of course this doesn't count that. I've tried the following but I get the same results as the above:
SELECT DISTINCT PrimaryAir, count(PrimaryAirQty) as AirFilters
From dbo.Trucks
Where Company = 'XYZ'
GROUP BY PrimaryAir
I've been able to put this code into a stored procedure and create a list of all filters a particular customer uses, but the result is woefully bad on the eyes and just about impossible to print. Are views used to make the layout more readable?
Thank you for any ideas.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

Do you mean things like the secondary air item?

Views are there to make it easier for you to write code. They're permanent objects, so if you need to use them in other places, I'd do that. If you want to just do it here, you might consider a CTE. You could write a series of CTEs at the top, one for each subquery, and then join them back later.

mstevens92 Old Hand Points: 320 More actions · Answer 2

Well, I really have 2 problems here. First problem is getting the results of the SELECT DISTINCT statement correct. The OP code works great if each truck in the list uses only one filter, but this is not the case, as some trucks use 2 oil filters, or 2 air filters.

Here is an example of the output:

PrimaryAirAirFilters

EAF505319

LAF33021

LAF95001

LAF184939

LAF253652

LAF48169

LAF69187

LAF920133

LAF93966

LAF94722

LAF95004

P53409663

P61605625

Now, take the filter LAF4816, the query from the OP gives me a total of 9 filters, actually, there are 9 trucks that use this filter, but each truck uses 2 filters each, so I would like the result to take this into account.

So for the first problem, how do I get this:

SELECT DISTINCT PrimaryAir, count(PrimaryAir) as AirFilters

From dbo.Trucks

Where Company = @Company

GROUP BY PrimaryAir

...to take PrimaryAirQty into account in the results?

I'll try to tackle the other problem with the stored proc results layout later. Data integrity first, right?

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

I'm not sure what you mean here. You aren't listing primary air qty here. Do you mean to multiply the count() by this value somehow? Is it stored separately from each truck?

you can

select a, count(b) * c

from MyTable

if you need to. Or you could sum the values if that makes more sense. That might give you the values you need.

select a, sum(b)

from MyTable

If that works.

mstevens92 Old Hand Points: 320 More actions · Answer 4

Using Sum rather than Count worked for me. The way I have the table designed, I have one column that lists the filter number (PrimarAir) and the next column gives the quantity of that filter (PrimarAirQty).

Now for my next problem, I have the following code repeated for each filter type (PrimaryAir, PrimaryOil, etc) in a stored procedure. And now that I fixed the above problem, it works great. The issue now is that the results given by the stored proc are a little difficult to print out.

SELECT DISTINCT PrimaryAir, sum(PrimaryAirQty) as AirFilters

From dbo.Trucks

Where Company = 'XYZ'

GROUP BY PrimaryAir

What are some options for putting the output into a format that is easier to read and easier to print?

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 5

Hi,

Would the results in a pivot be in a more readible/printable format?