Query Help

[iceman42]

I'm running a query and I need some help going through the logic. First off the data is organized by an invoice number, however the invoice number is not important in the query, the query is looking for the current location of an item in a certain route that started on x date.

So lets say that invoice #456ASD is at area 7 and left the starting point which is area 3, notice not area 1, on 1/31/06.

So I need to get a count of all the invoices that left area 3 on 1/31/06, and then break down the count to which area they are in along the way. 4 are in area 4, 16 are in area 5 and so on.

 

[iceman42]

SELECT B.STAT, COUNT(*)
FROM ORTL A
,ORTL B
WHERE A.STAT = 3
AND B.INVOICE = A.INVOICE
AND B.NDX >= A.NDX
AND B.STAT > A.STAT
GROUP BY B.STAT

This query seems to get me the counts for each area that the order has been too, however I need the query to not count the area if an order is past that area, not sure this query is doing that.

So if an order is at area 8 I only need it to count that order is at area 8 not that it was at 2, 3, 4, 5, 6 and 7.

 

[MarcLodge]

This is getting evermore complex!

How about:

SELECT B.STAT, COUNT(*)
FROM ORTL A
,ORTL B
WHERE A.STAT = 3
AND B.INVOICE = A.INVOICE
AND B.NDX >= A.NDX
AND B.STAT = (SELECT MAX(C.STAT)
FROM ORTL C
WHERE C.INVOICE = B.INVOICE)
GROUP BY B.STAT

usual provisos about untested code etc.

Marc

 

[iceman42]

Result for that is 8 = 56814.

 

[iceman42]

I wish I could edit my posts sorry about the one line reply my mouse got ahead of my brain.

I changed it a little bit to
SELECT B.STAT, COUNT(*)
FROM ORTL A
,ORTL B
WHERE A.STAT = 3
AND B.INVOICE = A.INVOICE
AND B.NDX >= 2006-01-31
AND B.STAT = (SELECT MAX(C.STAT)
FROM ORTL C
WHERE C.INVOICE = B.INVOICE)
GROUP BY B.STAT

I still only get the count for area 8, I have to check the data and see if all items are actually only in area 8, but usually it takes a bit longer.

 

[iceman42]

The total for the last query is 131 out of a total number of 849 items that left area 2 on 3/1/2006.

 

[MarcLodge]

Iceman42,
I would take a look at the data and find 3 or more invoices that live in areas 8, 7 and 6. I would then change the query to specifically pick up just those invoices and see what the query produces. In theory, you should get back a count for each separate area.
Marc

 

[iceman42]

Yeah thats sounds like a better idea, I'll post back what I find out.

 

[iceman42]

I've been looking at this, although not with an open mind, for about 2 days and I dont see anyway to get the current location of an item that left area 2 on x date, I might be wrong.

This one got me counts.

SELECT B.STAT, COUNT(*)
FROM ORTL A
,ORTL B
WHERE A.STAT = 3
AND B.INVOICE = A.INVOICE
AND B.NDX >= 2006-01-31
AND B.STAT = (SELECT MAX(C.STAT)
FROM ORTL C
WHERE C.INVOICE = B.INVOICE)
GROUP BY B.STAT

However I cannot verify that the counts it gave me were correct, as the invoice numbers arent with the query. What i see however is that changing the 2006-01-31 date in the query to something further out will result with higher counts, and if I push it out even further the counts go up as well, which doesnt look correct. Again I cant verify anything since the invoice number does get printed. I have tried other ways, but mostly those tell me that it left 200 at x date, and other that its at this station at x date. I did notice one thing, if an invoice hasnt been scanned into a station yet, the date defaults to 1940-01-01.

I dont mean to sound ungrateful or unthankful, I am thankful for the help that you guys have given me.

 

[iceman42]

Okay this is where I am at.

SELECT B.RDSTAT, COUNT(*) FROM ORPRDETL A, ORPRDETL B WHERE A.RDSTAT = 200 AND B.\"RDORD#\" = A.\"RDORD#\" AND B.RDENDX = '2006-02-27' AND B.RDENDX <> '1940-01-01' AND B.RDSTAT = (SELECT MAX( C.RDSTAT) FROM ORPRDETL C WHERE C.\"RDORD#\" = B.\"RDORD#\" AND C.RDENDX <> '1940-01-01') GROUP BY B.RDSTAT

The numbers that come from this look plausable, however I dont get invoice numbers so I cannot confirm the numbers by looking up the invoices, any help? Also a couple of stations are missing, I believe it might be because they have 0 count so they are getting printed, any thoughts on that?

 

[iceman42]

Nevermind I forgot the group by