• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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.

1 Solution
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)
	SELECT Period, OrdDt, CusNo, OrdNo, InvNo, ItemNo, Line_No, QtyOrdered,
	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

mpdillonAuthor Commented:
Exactly what I wanted. I do not use partition often. Tomorrow I will expand to the real data set.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now