[SOLVED] Nasty nested records!

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)

[SOLVED] Nasty nested records!

Author

Topic

eb1962
Starting Member

6 Posts

Posted - 2006-10-09 : 09:19:15

I am loosing my mind over a rather "simple" matter.

I have a table which I have created by summarizing some quantities from a detailed order table, in order to have summarized quantities by product, through the following SQL:
INSERT INTO SUMMARIZED_ORDER_TAB
(ORDER_ID, PRODUCT_ID, QUANTITY)
SELECT ORDER_ID, PRODUCT_ID, SUM(QUANTITY) AS QUANTITY
FROM DETAILED_ORDER_TAB
GROUP BY ORDER_ID, PRODUCT_ID
HAVING (ORDER_ID IN
(SELECT ORDER_ID
FROM DETAILED_ORDER_TAB AS Tmp
GROUP BY ORDER_ID, PRODUCT_ID
HAVING COUNT(*) > 0 AND PRODUCT_ID = DETAILED_ORDER_TAB.PRODUCT_ID))
ORDER BY ORDER_ID, PRODUCT_ID

Now I must number those lines.

The structure of the SUMMARIZED_ORDER_TAB is the following:
ORDER_ID varchar (20)
ORDER_LINE_ID smallint
PRODUCT_ID varchar (20)
QUANTITY decimal

The order line id is currently blank in ALL records and the following records are available:
+ ORDER_ID + ORDER_LINE_ID + PRODUCT_ID + QTY +
+----------+---------------+------------+-----+
| 1 | | ABC1 | 100 |
| 1 | | ABC2 | 130 |
| 1 | | ABC3 | 20 |
| 2 | | ABC1 | 200 |
| 2 | | ABC3 | 10 |

Result should be:
+ ORDER_ID + ORDER_LINE_ID + PRODUCT_ID + QTY +
+----------+---------------+------------+-----+
| 1 | 1 | ABC1 | 100 |
| 1 | 2 | ABC2 | 130 |
| 1 | 3 | ABC3 | 20 |
| 2 | 1 | ABC1 | 200 |
| 2 | 2 | ABC3 | 10 |

What would you suggest to create the ORDER_LINE_ID directly when creating the new table or to amend it at a later stage?

How can I do it?
I have tried in several ways but I am not getting anywhere...
Could you please help me?

Thanks,
Emanuele

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-09 : 09:28:49

Please dont cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73225

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 09:31:20

[code]Select Order_Id,
(Select count(*) from SUMMARIZED_ORDER_TAB y
where y.Order_ID = x.Order_ID
and y.ProductID <= x.ProductID) as Order_Line_ID,
Product_ID,
Qty
From SUMMARIZED_ORDER_TAB x
order by x.order_id, product_id[/code]

Harsh Athalye
India.
"Nothing is Impossible"

eb1962
Starting Member

6 Posts

Posted - 2006-10-09 : 09:46:39

First, I do apologize for having cross my post... I am new and I hope u do bear with me.

Harsh, I have tried what you have suggested but it doesn't give the expected results...
Here they are, between parenthesys I have written what they should be:
ORDER LINE PRODUCT QTY
016342 1 (1) 5002720 6,3
016342 3 (2) 5112100 18,298
016342 3 (3) 5112101 54,388
016342 4 (4) 5205800 24900
016343 1 (1) 5107600 750
016343 2 (2) 5245500 56,25
016343 4 (3) 5266000 85,056
016343 4 (4) 5266001 27,444
016470 1 (1) 5204600 877,5
016470 2 (2) 5207000 21,965
016470 3 (3) 7870230 87,75
016474 5 (1) 1170050 19355
016474 5 (2) 1170051 19481
016474 5 (3) 1170052 19770
016474 5 (4) 1170053 18932
016474 5 (5) 1170054 19569
016474 6 (6) 3445220 104494
016474 7 (7) 603022100 32716
016474 8 (8) 6129810 771
016474 9 (9) 614397500 113354
016474 10(10) 628010600 32216
016474 11(11) 6340049 237,399
016474 12(12) BO020660066 39273

And finally I must update the table with these new line numbers...

Thanks everybody for your patience too!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 10:20:26

Take a look at the cross postings, you might have got an answer there.

Peter Larsson
Helsingborg, Sweden

eb1962
Starting Member

6 Posts

Posted - 2006-10-09 : 10:42:51

I figured there is an extra parameter I have to take into account being a lot number.....
Thus the new structure is
ORDER_ID
ORDER_LINE_ID
PRODUCT_ID
LOT_NUMBER
Quantity

So if I have
ORDER ORDER_LINE_ID PRODUCT_ID LOT_NUMBER QUANTITY
1 ABC ABC1 10
1 ABC ABC2 15
1 ABC ABC3 20
1 ABD ABD1 5
1 ABD ABD2 3
1 ABE ABE1 2
2 ABC ABC1 10
2 ABC ABC2 15
2 ABC ABC3 20
2 ABD ABD1 5
3 ABC ABC1 10
3 ABC ABC2 15
3 ABC ABC3 20
3 ABD ABD2 3

This should result in:
ORDER ORDER_LINE_ID PRODUCT_ID LOT_NUMBER QUANTITY
1 1 ABC ABC1 10
1 2 ABC ABC2 15
1 3 ABC ABC3 20
1 4 ABD ABD1 5
1 5 ABD ABD2 3
1 6 ABE ABE1 2
2 1 ABC ABC1 10
2 2 ABC ABC2 15
2 3 ABC ABC3 20
2 4 ABD ABD1 5
3 1 ABC ABC1 10
3 2 ABC ABC2 15
3 3 ABC ABC3 20
3 4 ABD ABD2 3

Am I asking too much? Certainly I am for my capabilities!

eb1962
Starting Member

6 Posts

Posted - 2006-10-09 : 11:43:21

I HAVE MADE IT!!!
Thanks to Peter and Harsh's suggestions!!!
Here it is!!

UPDATE b
SET b.ORDER_LINE_ID =
(SELECT COUNT(*) FROM SUMMARIZED_ORDER_TAB a
WHERE b.ORDER_ID = a.ORDER_ID AND ((a.PRODUCT_ID < b.PRODUCT_ID)
OR ((b.PRODUCT_ID = a.PRODUCT_ID) AND b.LOT_NUMBER <= a.LOT_NUMBER)))
FROM SUMMARIZED_ORDER_TAB b

THANKS FOR YOUR SPLENDID HELP! I would have never made it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources