subquery with parameter question.

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

Transact-SQL (2000)

subquery with parameter question.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-03-25 : 13:07:53

Hello all.

I have the following fields in one tabLe:

DATE
ITEM NUMBER
NAME
ORDER NO
QTY

I am also requesting for the following 2 parameters:

@ITEM NO = ITEM NUMBER
@DATE = DATE

I would like to diplay the following table as a result on a web page:

NAME
ALL ORDER COUNT = (count distinct of all order numbers where date >= @date)
ITEM NUMBER ORDER COUNT = (count distinct of all order numbers where date>= @date AND ITEM NUMBER = @ITEM NO
QTY = sum(qty) where date>= @date AND ITEM NUMBER = @ITEM NO

this all needs to be done without the use of a temporarty table since I will have many users executing this Sproc at one time.

I tried using a subquery as a derived table but I get stuck due to my lack of experience.

Can anyone help please!

thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-25 : 13:16:39

select
NAME ,
"ALL ORDER COUNT" = (select count(distinct [ORDER NO
]) from tbl where date >= @date) ,
"ITEM NUMBER ORDER COUNT" = count(distinct [ORDER NO
]) from tbl where date >= @date AND ITEM NUMBER = @ITEM NO) ,
QTY = (select sum(qty) from tbl where date >= @date AND ITEM NUMBER = @ITEM NO)
from tbl

If you have a lot of users executing this at the same time it might be as well to have a table with the agregates already calculated and updated whenever anything changes - particularly the 'all order no'.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-03-25 : 13:33:21

That won't work. I need to have the final result set broken out by name.

After implementing your solution, I get something that looks like this:

Joe 5,081
Frank 5,081
Bob 5,081
Mike 5,081

I need to have these numbers by name.

thanks.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-03-25 : 15:42:28

>> I have the following fields in one tabLe: <<

Tables have columns, not fields and there is a huge difference. DATE is a reserved word in SQL and should never be used for a column name
(it is also bad because it does not tell you "date of what??" Tables have names and keys. If you had bothered to post DDL, would it look like this?

CREATE TABLE Foobar
(order_nbr INTEGER NOT NULL PRIMARY KEY
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CHECK (CAST (CEILING(CAST order_date AS FLOAT) AS DATETIME)
= order_date), --date only
item_nbr INTEGER NOT NULL,
customer_name VARCHAR(30) NOT NULL,
qty INTEGER NOT NULL,
UNIQUE (order_nbr, item_nbr) -- item appears once per order
);

>> I am also requesting for the following 2 parameters:

@my_item_nbr INTEGER
@my_date DATETIME

>> I would like to diplay the following table as a result on a web page: <<

Display is a job for the front end, not the database.

Then your spec fall apart. You asked for the same summary data to appear beside every name, which is pretty useless. I am guessing this is what you meant.

SELECT customer_name,
COUNT (order_nbr) AS total_orders,
SUM(CASE WHEN item_nbr = @my_item_nbr
THEN 1 ELSE 0 END) AS total_item_orders,
SUM(CASE WHEN item_nbr = @my_item_nbr
THEN qty ELSE 0 END) AS total_qty
FROM Foobar
WHERE order_date >= @my_order_date
GROUP BY customer_name;

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources