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
mpdillonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
First I want to thank you for framing your question so well and including the appropriate schema reproduction and data insertion queries.

Using your Test example..this should get you the original order information.

Let me know if we're on the right track.

WITH cteTest (Period, OrdDt, CusNo, OrdNo, InvNo, ItemNo, Line_No, QtyOrdered, OrderItemRowNo)
AS
(
	SELECT Period, OrdDt, CusNo, OrdNo, InvNo, ItemNo, Line_No, QtyOrdered,
		ROW_NUMBER() OVER(PARTITION BY OrdNO, Line_No ORDER BY QtyOrdered DESC)
	FROM dbo.Test
)
SELECT Period, OrdDt, CusNo, OrdNo, InvNo, ItemNo, Line_No, QtyOrdered
FROM cteTest AS cte
WHERE OrderItemRowNo = 1
ORDER BY OrdNo, Line_No

Open in new window


These are the results I got

Period	OrdDt	CusNo	OrdNo	InvNo	ItemNo	Line_No	QtyOrdered
1	2013-01-01	1	100	2000	A	1	3000
1	2013-01-01	1	100	2001	B	2	100
1	2013-01-01	2	101	2001	A	1	2000
2	2013-02-01	3	102	2021	B	1	25
3	2013-03-01	4	103	2030	A	1	50

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mpdillonAuthor Commented:
Exactly what I wanted. I do not use partition often. Tomorrow I will expand to the real data set.
0
PortletPaulfreelancerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.