Need help with stored procedure!!!!!!!!!!!

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 2005 Forums

Transact-SQL (2005)

Need help with stored procedure!!!!!!!!!!!

Author

Topic

arishtech
Starting Member

6 Posts

Posted - 2009-03-28 : 20:27:20

Write a stored procedure that would accept the LocationID as its parameter. It would then cursor through the WorkOrderRouting table. For each Product found, the procedure should print the percentage of the work orders for that product at that location and at all other locations.

CREATE TABLE WorkOrderRoutingAA
(WorkOrderID int NOT NULL,
ProductID int NOT NULL,
OperationSequence smallint NOT NULL,
LocationID smallint NOT NULL,
ScheduledStartDate datetime NOT NULL,
ScheduledEndDate datetime NOT NULL,
ActualStartDate datetime NULL,
ActualEndDate datetime NULL,
ActualResourceHrs decimal(9, 4) NULL,
PlannedCost money NOT NULL,
ActualCost money NULL,
ModifiedDate datetime NOT NULL DEFAULT (getdate())
)

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-03-28 : 20:32:49

>>Write a stored procedure that would accept the LocationID as its parameter

show us how far you have reached programming stored procedure?

arishtech
Starting Member

6 Posts

Posted - 2009-03-28 : 20:38:45

I dont really know where to start. I tried many select statements but failed to create a proper logic. I have never done loops with tsql.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-28 : 21:58:11

Well, I can tell you that you probably won't need a cursor for this, and:

quote:
the procedure should print the percentage of the work orders for that product at that location and at all other locations

is worded in such a way that it sounds like you're processing ALL locations (another reason not to use a cursor), so a LocationID parameter makes no sense.

Can you post some sample data, with the exact output you're trying to achieve?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:38:10

sounds like this is what you want


SELECT ProductID,
COUNT(CASE WHEN LocationID=@LocationID THEN WorkOrderID ELSE NULL END) * 100.0/COUNT(WorkOrderID) AS [%AtLocation],
COUNT(CASE WHEN LocationID<>@LocationID THEN WorkOrderID ELSE NULL END) * 100.0/COUNT(WorkOrderID) AS [%OutsideLocation]
FROM WorkOrderRoutingAA

@LocationID is passed location value

arishtech
Starting Member

6 Posts

Posted - 2009-03-29 : 08:57:14

OK. Lets clarify a few things
1. For the first column I would have to put

Create procedure
select DISTINCT(ProductID) from workorderroutingAA where locationid = @locid

since there are multiple orders for each product and I want each product to be listed only once.
First column is the easy part.
2. For the second column I need to calculate for each product found in that first column
(number of workorders with that product id at the requested location location)/(total number of orders for that product from all other locations) * 100

for example if we ran the query
exec orderspercentage 45
and there is a productID 330
number orders for that product at location 45 = 20
and total number of orders for that product (at all other locations) = 100

so final table would look like
productid Percentage of orders at location 45
330 20%

arishtech
Starting Member

6 Posts

Posted - 2009-03-29 : 08:59:41

--------- correction to previous entry ---------------

Create procedure orderspercentage
@locid
as
select DISTINCT(ProductID) from workorderroutingAA where locationid = @locid

arishtech
Starting Member

6 Posts

Posted - 2009-03-29 : 09:58:44

--------- Please disregard my previous posts ----------------

Was reading through it again removed some abiguity and have finally ended up with this:

1. Accept the LocationID as its parameter
2. For each distinct Product found in WorkorderRoutingAA table
print 3 columns
a.The product ID
b.The percentage of the work orders for that product at that given location
c.The percentage of the work orders for that product at all other locations

First column is straight forward:
Create procedure orderspercentage
@locid
as
select DISTINCT(ProductID) from workorderroutingAA

Please help me with the second and the third column

for second column What I think I need is
(number of orders matching productid(in the firstcolumn) and location id matching @locid)/(total number of orders for that productid irrespective of location) * 100

And for third column
(number of orders with productid (the one printed in column a in that row) and location id <> @locid)/(total number of orders for that productid irrespective of location) * 100

well third column can also be calculated by simply doing (100 - secondcolumn)

thanks in advance for all the help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources