Re: How to create this kind of SQL SELECT statement.

The first question that comes to mind is
How do you differentiate the records between the records containing
quantity and the records containing amount?
Can you post your example in some better way, as the e-mail I received was
wrapped in such a format as to make it almost unreadable?



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill



midrange-l-bounces@xxxxxxxxxxxx wrote on 05/22/2007 09:27:54 AM:

Hi

I would like to create a SELECT with diffrent criteria for fields in one
file.
I have a file with fields amount and quantity and additional fields
custnumb, ordernum, ordertype, orderdate etc.
The problem is that data for (sum of) amount and (sum of) quantity are
stored in diffrent rows. What is more complicated when row is "amount" in
field "quantity" value of weight is stored.

Next example

custnumb ordnumb ordtype orderdate amount quantity
1 1 A something 10
10
1 2 A something 20
20
1 1 B something 0
10
2 4 A something 10
15
2 4 B something 0
20
2 5 B something 0
20


And on report I would like to see (for same date range)

Custnumb amount quantity
1 30 10
2 10 40

Amount is sum of "amount" from rows A, quantity is sum of "quantity" from
rows B.
But how to write it? I created many joins but without success.

Have you any idea?

Regards

Tomasz Skorza

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.