I started creating a stored procedure in which i create two variable tables, one to store all the orders where version number = 1, and type = 2 (item) which...

Hi J Livingston

thank you so much for bringing up the oversimplification point.

This is what I can say about the questions you posted.

Item being deleted from order - This...

no that column is just a placeholder for the quantity that changes in other versions.

I see that we are turning 5 rows into 1. Why do we use the dates from version 1 and the Posting Date from version (4 or 5?). Do you...

I have attached an excel file using the two orders I used in the sql scrip, those are s19856 and s20026

on sheet 1 is the data as in the table,...

here is the updated script.

sorry for the delay.

yes, it is a ms NAV database.

thank you 😀

CREATE TABLE Orders

(

SalesOrderNumberNVARCHAR(20)

,VersionNumberINT

,ItemNumberNVARCHAR(20)

,OrderDateDATETIME

,RequestedDeliveryDateDATETIME

,PromisedDeliveryDateDATETIME

,QuantityOrderedDECIMAL(38,18)

,PostingDateDATETIME

,QuantityShippedDECIMAL(38,18)

,LocationCodeNVARCHAR(10)

)

INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate,PromisedDeliveryDate, QuantityOrdered, PostingDate,QuantityShipped, LocationCode)

VALUES ('S19856',1,'10568','2014-02-13...

I made a comment that reads "the desired output"

That is what the output after processing the orders, respectively s19856 and s20026 should look like.

The column QuantityChanged should show to...

i want to select the rows that can share same order number, customer number, and have campaign id, different hu id and different ship date.

an order can have multiple hu_ids (boxes)

each box belonging to an order is shipped separately.

let's say an order is conformed of 3 boxes, 2 boxes are shipped on...

Thank you for your help and an apology for not providing all the necessary details.

Answering your question

Do you have the same customer in the bcustomer_notification_call_list table more than once?

yes,...

i do need to retrieve campaign_id, i did try to run the query without it and i was able to get rid of the duplicates, but i need to find...

i do have hu_id on the result dataset i pasted above.

campaign id customer_number order_number hu_id customer_name phone_number ...

Yes I did try it and it seems to work fine. I just have not finished the app. coding. to a point where I can test it from there.

Anyhow,...

I had to make some changes to the numbers ranges table.

Now the last number used is no longer stored in a different table.

II just added a column that will stored...

thank you for your post.

i forgot to mention one thing.

the value for the current_number_in_use will be stored in a table named current_number_value column current_number_in_use

this table has...