I have 2 tables (orders and order allocations). I need to take the total units from order allocations and apply it against the orders table. Can I use cursors to do this and can you give me an example please?

Question

Post reply

I have 2 tables (orders and order allocations). I need to take the total units from order allocations and apply it against the orders table. Can I use cursors to do this and can you give me an example please?

61563shanti

SSC Rookie

Points: 30
More actions
July 29, 2010 at 3:20 am

#387459

Order Table
Seq Units Agent Filled Due
1 200 A
2 200 A
3 200 C
Order Allocations
Agent Units
A 400
C 200

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Why use a cursor? Why not just do this?

DECLARE @ORDERS AS TABLE(

Seq INT,

Units INT,

Agent CHAR(1))

INSERT INTO @ORDERS

SELECT 1, 200, 'A'

UNION ALL SELECT 2, 200, 'A'

UNION ALL SELECT 3, 200, 'C'

SELECT Agent, SUM(Units) AS Units

FROM @ORDERS

GROUP BY Agent

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

61563shanti SSC Rookie Points: 30 More actions · Answer 2

Really appreciate your reply. I was advise to use cursors but really do not know how to use it for calculations in a Stored Procedure.

Another question - What if I have to use a running total which I then have to reduce it against the order table? In this case apply the 980 against Seq 1, 2 and 4; then show the over-allocation against Seq 4.

How can I best handle this?

--------------------------------------------------------------------

Order Table

Seq Units Agent Filled Due

1 200 A

2 200 A

3 200 C

4 500 A

Order Allocations

Agent Units

A 980

C 200

61563shanti SSC Rookie Points: 30 More actions · Answer 3

Sorry for the poor format. First time posting a question for help.:-)

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

61563shanti (7/29/2010)
Really appreciate your reply. I was advise to use cursors but really do not know how to use it for calculations in a Stored Procedure.

For this sort of thing, don't use a cursor. For most things, a cursor is bad.

61563shanti (7/29/2010)
Another question - What if I have to use a running total which I then have to reduce it against the order table? In this case apply the 980 against Seq 1, 2 and 4; then show the over-allocation against Seq 4.
How can I best handle this?
--------------------------------------------------------------------
Order Table
Seq Units Agent Filled Due
1 200 A
2 200 A
3 200 C
4 500 A
Order Allocations
Agent Units
A 980
C 200

I don't understand where you get 980 from. . . Was that supposed to be 900? 200+200+500 for 'Agent' A? If so, it's another fairly simple answer, if not then I need to understand why before I can show you how 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

61563shanti SSC Rookie Points: 30 More actions · Answer 5

61563shanti

SSC Rookie

Points: 30

July 31, 2010 at 10:35 pm

#1200725

Thank you.