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.
| Author |
Topic |
|
eb1962
Starting Member
6 Posts |
 Posted - 2006-10-09 : 09:22:49
|
| I am loosing my mind over a rather "simple" matter.In a DTS I have I have created a new table 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 QUANTITYFROM DETAILED_ORDER_TABGROUP BY ORDER_ID, PRODUCT_IDHAVING (ORDER_ID IN(SELECT ORDER_IDFROM DETAILED_ORDER_TAB AS TmpGROUP BY ORDER_ID, PRODUCT_IDHAVING COUNT(*) > 0 AND PRODUCT_ID = DETAILED_ORDER_TAB.PRODUCT_ID))ORDER BY ORDER_ID, PRODUCT_IDNow I must number those lines.The structure of the SUMMARIZED_ORDER_TAB is the following:ORDER_ID varchar (20)ORDER_LINE_ID smallintPRODUCT_ID varchar (20)QUANTITY decimalThe 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
[SOLVED]Sum'ing quantities and numbering new rec's