mpdillon
asked on
Query assistance (partition over... maybe?)
I have to create an order booking report. It has to be recreated from Invoice history. The issue I am having trouble with is that an Item on an ORDER can be shipped more than once (partial shipment). Yet I only want to count the first occurrence of the of the order in the Invoice history files.
Attached are two files.
1. The excel sheets graphically shows what the original orders were and how they actually appear in the Invoice history file.
2. A text file containing a table CREATE statement and SELECT statement to populate the new table.
I need to query this table to return the orders as they were originally entered.
Ultimately I am going to return by period, by itemno, by Customer the quantities ordered. But if you can help me get started by just recreating the orders from the invoice history, I will try to do the rest.
Thanks,
pat
Booking.xlsx
booking-2.txt
Attached are two files.
1. The excel sheets graphically shows what the original orders were and how they actually appear in the Invoice history file.
2. A text file containing a table CREATE statement and SELECT statement to populate the new table.
I need to query this table to return the orders as they were originally entered.
Ultimately I am going to return by period, by itemno, by Customer the quantities ordered. But if you can help me get started by just recreating the orders from the invoice history, I will try to do the rest.
Thanks,
pat
Booking.xlsx
booking-2.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'd like to echo BriCrowe's comment regarding your question preparation
- it is very much appreciated.
Just as an observation you have asked for " the first occurrence of "
when using row_number():
over (partition by ... order by [date_data] ASC) -- typically for "first"/"earliest"
over (partition by ... order by [date_data] DESC) -- typically for "latest"/"most recent"
I was rather hoping your OrdDt field would be datetime (with times) and therefore be useful in identifying the first occurrences. As a date field you might need some additional tie-breaker(s).
Do you have records where QtyOrd = QtyToShip perhaps? (and would that help indicate 'first'?)
just as a sample it is possible to combine case expressions into the order by.
... order by OrdDt ASC, case when QtyOrd = QtyToShip then 0 else 1 end ASC )
hope this helps.
- it is very much appreciated.
Just as an observation you have asked for " the first occurrence of "
when using row_number():
over (partition by ... order by [date_data] ASC) -- typically for "first"/"earliest"
over (partition by ... order by [date_data] DESC) -- typically for "latest"/"most recent"
I was rather hoping your OrdDt field would be datetime (with times) and therefore be useful in identifying the first occurrences. As a date field you might need some additional tie-breaker(s).
Do you have records where QtyOrd = QtyToShip perhaps? (and would that help indicate 'first'?)
just as a sample it is possible to combine case expressions into the order by.
... order by OrdDt ASC, case when QtyOrd = QtyToShip then 0 else 1 end ASC )
hope this helps.
ASKER