[thelist] subselect query?

Dallas Cahker christmasfruitcake at gmail.com
Mon Mar 6 14:23:07 CST 2006


DB2 is the DBMS and there is only one table.

RDORD#      RDPTYP      RDLIN#      RDPRC#      RDSEQ#      RDSTAT
RDSTRX      RDSTRT      RDSTRU      RDENDX      RDENDT      RDENDU
RDEDAY      RDADAY      RDCOMP      RDECMP

RDORD# = Invoice
RDSTAT = Area
RDSTRX = Start Date
RDSTRT = Start Time
RDENDX = Exit Date
RDENDT = Exit Time

Example #1 this one is finished and out the door.
658015 E 1 1 10 100 2006-02-07 14:29:00 56 2006-02-07 14:29:00 56 0 1.00 C
1940-01-01
658015 E 2 1 20 150 2006-02-07 14:14:00 5 2006-02-07 14:14:00 5 0 1.00 C
1940-01-01
658015 E 3 1 30 200 2006-02-07 14:29:00 56 2006-02-10 13:56:00 56 0 3.00 C
1940-01-01
658015 E 4 1 40 300 2006-02-16 12:08:00 101 2006-02-20 14:56:00 10 0 3.00 C
1940-01-01
658015 E 5 1 50 400 2006-02-22 05:00:00 26 2006-02-22 05:00:00 26 0 1.00 C
1940-01-01
658015 E 6 1 60 500 2006-02-26 13:29:00 12 2006-02-26 13:29:00 12 0 1.00 C
1940-01-01
658015 E 7 1 70 600 2006-02-25 13:40:00 66 2006-02-25 13:40:00 66 0 0 C
1940-01-01
658015 E 8 1 80 700 2006-02-27 07:39:00 5 2006-03-01 09:10:00 5 0 3.00 C
1940-01-01
658015 E 9 1 90 800 2006-03-01 14:41:00 18 2006-03-01 14:41:00 18 0 1.00 C
1940-01-01

Example #2 this one is finished in area 200 but not to area 300 yet.
657834 E 1 1 10 100 2006-02-03 13:31:00 7 2006-02-03 13:31:00 7 0 0 C
1940-01-01
657834 E 2 1 20 150 2006-02-02 16:37:00 5 2006-02-02 16:37:00 5 0 1.00 C
1940-01-01
657834 E 3 1 30 200 2006-02-03 13:31:00 7 2006-03-06 13:56:00 7 0 22.00 C
1940-01-01
657834 E 4 1 40 300 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS
1940-01-01
657834 E 5 1 50 400 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS
1940-01-01
657834 E 6 1 60 500 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS
1940-01-01
657834 E 7 1 70 600 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS
1940-01-01
657834 E 8 1 80 700 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS
1940-01-01
657834 E 9 1 90 800 1940-01-01 00:00:00 0 1940-01-01 00:00:00 0 0 0 GAAS
1940-01-01

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.
> --
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list