[thelist] subselect query?

Dallas Cahker christmasfruitcake at gmail.com
Tue Mar 7 09:09:27 CST 2006


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?

  On 3/6/06, EasyListBox.com Peter Brunone <peter at easylistbox.com> wrote:
> >
> > Hi Dallas,
> >
> >    Can you tell us (a) what kind of DBMS you are using and (b) the
> > structure of the tables involved?
> >
> > Cheers,
> >
> > Peter
> >
> > From: "Dallas Cahker" < christmasfruitcake at gmail.com>
> > Sent: Monday, March 06, 2006 12:47 PM
> > To: thelist at lists.evolt.org
> > Subject: [thelist] subselect query?
> >
> > I have a problem. My problem is this. I have a database of scans, the
> > scans are linked together by an invoice number, there might be 8-12
> > scans
> > per invoice, I need to get the invoice number of the scan that left area
> > 20
> > on a certain date, after that I need to get the area it is currently in.
> >
> > Can someone help me with getting the subselect correct for that, or is
> > there
> > a better way to do it, even if it is 650,000 records.
> >
> > 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.RDSTRX<>
> > '1940-01-01' AND B.RDSTAT = (SELECT MAX(C.RDSTAT) FROM ORPRDETL C WHERE
> > C.\"RDORD#\" = B.\"RDORD#\") GROUP BY B.RDSTAT - I dont get any invoice
> > numbers so I cant do any checks to see if the data is accurate.
> >
>
>



More information about the thelist mailing list