Need a suggestion on a query situation

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

SQL Server 2000 Forums

SQL Server Development (2000)

Need a suggestion on a query situation

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-10-12 : 15:29:34

Hello. I am writing a report in ASP that allows the user to compare their item to other items to see how their item is performing.

Here's an example to better illustrate this. Users can compare vehicles to other vehicles, so they can compare the fuel efficiency of a Ford F-150 to a Chevy Silverado. This query would pull all of the fuelings for each vehicle, do some simple calculations and then create 2 records (one for each vehicle).

Now, imagine the user wants to compare their vehicle's fuel efficiency against the fuel records for all Ford F-150s and Chevy Silverados in the database. Let's say the user's vehicle is a Chevy Silverado. This would create 3 records: one for all of the Ford F-150 fuelings, one for all of the Chevy Silverado fuelings, and one for just the user's fuelings for his/her Chevy Silverado. This means the user's Chevy Silverado actually makes up a subset of the results used to calculate the results for all of the Chevy Silverados in the database. Additionally, when the results are displayed the user can sort the results by clicking on the column they are interested it.

I already have the functionality done to allow users to compare the fuel efficiency of all the Ford F-150s to all of the Chevy Silverados. Users can also sort the query by clicking on the column they care about. I'm doing this using a dynamic stored procedure. I feed it two strings for the WHERE and ORDER BY statements.

It feels like I need two stored procedures (one for all the vehicles a user selectes and one for their vehicle). Then I would need another stored procedure to put the records from both stored procedures together so they can be sorted. I don't even know if this is possible.

What do you think? What's the best solution for what I want to do? I appreciate any suggestions you can provide. Thanks.

Huligan

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-12 : 17:44:57

maybe you can avoid using dsql by using optional parameters and make the select something like

SELECT Field1, Field2, etc.
FROM MyView
WHERE (Field1 = @Field1 OR @Field1 IS NULL)
AND (Field2 = @Field2 OR @Field2 IS NULL)
etc.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-10-13 : 07:43:16

Hello tuenty. I don't think that will work, but maybe I'm missing something. Your suggestion looks like the user's Chevy Silverado fuelings will be included in the record for all Chevy Silverados, but there won't be another record for just the user's Chevy Silverado.

Here's another example to help explain what I need. Here are the records in the database.

ID - FruitType - Qty - Owner
-------------------------------------------
3 - Apple - 3 - Joe
7 - Orange - 2 - Joe
4 - Apple - 4 - Sam
9 - Apple - 1 - Joe
2 - Orange - 5 - Sam

Here are the results (records) I need.

Sum of all apples: 8
Sum of all oranges: 7
Sum of Joe's apples: 4

These 3 records will then allow me to do a sort on the sum column. Thanks again for your help.

Huligan

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-13 : 08:26:50

from your second example...

select FruitType , sum(qty) from table1 group by FruitType
union all
select Owner , sum(qty) from table1 where Owner = 'Joe' and FruitType = 'Apple' group by Owner

create a query for the fuel efficiency of all the Ford F-150s to all of the Chevy Silverados and union this with the query for the fuel efficiency of the user's vehicle. Assuming you would be extracting the similar (datatype) columns in both the queries, you should be able to UNION them.

does this help at all..?

Hemanth Gorijala
BI Architect / DBA...

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-10-13 : 09:09:01

Hello hgorijal. I think that is exactly what I'm looking for and all of the columns I am extracting have the same datatype. Thanks. I'll give it a try.

Huligan

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-13 : 11:41:29

use cube or rollup

Declare @myFruit table(
    id int,
    fruitType nvarchar(12),
    Qty tinyint,
    owner nvarchar(12))
Insert into @myFruit
   Select 3,'Apple',3,'Joe' union
    Select 7,'Orange',2,'Joe' union
    Select 4,'Apple',4,'Sam' union
    Select 9,'Apple',1,'Joe' union
    Select 2,'Orange',5,'Sam'

SELECT   FruitType,Owner, sum(Qty)
FROM @myFruit
GROUP BY FruitType,Owner WITH Rollup --or

SELECT   FruitType,Owner, sum(Qty)
FROM @myFruit
GROUP BY FruitType,Owner WITH CUBE

Results

FruitType    Owner                    
------------ ------------ ----------- 
Apple        Joe          4
Apple        Sam          4
Apple        NULL         8	total apples
Orange       Joe          2
Orange       Sam          5
Orange       NULL         7	total oranges
NULL         NULL         15	total fruits
                                              --or
FruitType    Owner                    
------------ ------------ ----------- 
Apple        Joe          4
Apple        Sam          4
Apple        NULL         8	total apples
Orange       Joe          2
Orange       Sam          5
Orange       NULL         7	total oranges
NULL         NULL         15	total fruits
NULL         Joe          6	total Joe's fruit
NULL         Sam          9	total Same's fruit

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-10-13 : 13:53:17

Hello hgorijal. I tried it and it works. I have it working in a stored procedure being called through Query Analyzer. I'm going to put it to work in ASP now, but I don't anticipate any problems. Thanks again for the help.

Huligan

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-13 : 14:24:54

glad to be ..

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources