Query Jobs and Operations

Yahoo Archive
1
One of these days im going to have to learn SQL...i hear alot of suggestions along those lines...

-----Original Message-----
From: saab_barracuda <chris.clunn@...>
Sent: Friday, October 08, 2010 7:28 AM
To: vantage@yahoogroups.com <vantage@yahoogroups.com>
Subject: [Vantage] Re: Query Jobs and Operations



Or a real query in SQL through ODBC...

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Rob Bucek" <rbucek@...> wrote:
>
> Or write yourself a handy dandy BPM to check if the labor record
> completes the op, then fling the date into a VB field..and voila..
>
>
>
> Rob Bucek
>
> Production Control Manager
>
> PH: (715) 284-5376 ext 311
>
> Mobile: (715)896-0590
>
> FAX: (715)284-4084
>
> <http://www.dsmfg.com/>
>
> (Click the logo to view our site) <http://www.dsmfg.com/>
>
>
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf
> Of Brian W. Spolarich
> Sent: Thursday, October 07, 2010 3:58 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: RE: [Vantage] Query Jobs and Operations
>
>
>
>
>
> What I meant to say here is that these sorts ensure 1) I get the first
> LaborDtl record for the Op, and 2) I can sort my entire dataset by
> DueDate, which is what my user wants.
>
> -bws
>
> -----Original Message-----
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On
> Behalf
> Of Brian W. Spolarich
> Sent: Thursday, October 07, 2010 4:56 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
> Subject: RE: [Vantage] Query Jobs and Operations
>
> [...snipped...]
>
> Specifying these sorts including PayrollDate will ensure that I can
> sort the data in my report by DueDate (I'm grouping on JobNum of
> course), as I can tell Crystal to keep the records in their original
> order.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
2
I'm interested in producing a BAQ report that provides a concise
status of open jobs and their operation status.



JobHead and JobOper are clearly the place to start.



I'm finding everything I want there, except I don't see the actual
start date and completion date for each operation.



I'm looking at LaborDtl and I can bring that into the query via a
natural join, but I'm not 100% if that's what I really want to do, or if
there's a simpler way.



Basically I want to show some Job header info, and then the operations
in order, with scheduled start date, actual start date, due date, actual
completion date, and qty info.



-bws



--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich@...> ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>





[Non-text portions of this message have been removed]
3
I think youre right about the labordtl table and not bringing it in.. or
if you did, you might only want to bring in the first dtl record for
each op in (if any) and the last (if any). The only field in the
joboper table that might give you any info along those lines is the
lastlabordate field, if you used this along with the opcomplete you
could show completion date, youre hosed on start date though.



Rob Bucek

Production Control Manager

PH: (715) 284-5376 ext 311

Mobile: (715)896-0590

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, October 07, 2010 1:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Query Jobs and Operations





I'm interested in producing a BAQ report that provides a concise
status of open jobs and their operation status.

JobHead and JobOper are clearly the place to start.

I'm finding everything I want there, except I don't see the actual
start date and completion date for each operation.

I'm looking at LaborDtl and I can bring that into the query via a
natural join, but I'm not 100% if that's what I really want to do, or if
there's a simpler way.

Basically I want to show some Job header info, and then the operations
in order, with scheduled start date, actual start date, due date, actual
completion date, and qty info.

-bws

--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich%40advancedphotonix.com>
<mailto:bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> > ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
4
I *think* I got it right by doing this:

for each JobHead where ( JobHead.JobClosed = False AND JobHead.JobFirm
= True AND JobHead.JobEngineered = True AND JobHead.JobReleased =
True) no-lock , each JobOper where (JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum) no-lock , first LaborDtl outer-join
where (JobOper.Company = LaborDtl.Company and JobOper.JobNum =
LaborDtl.JobNum and JobOper.AssemblySeq = LaborDtl.AssemblySeq and
JobOper.OprSeq = LaborDtl.OprSeq) no-lock , each Part where
(JobHead.Company = Part.Company and JobHead.PartNum = Part.PartNum)
no-lock by JobHead.DueDate by JobHead.JobNum by JobOper.OprSeq by
LaborDtl.PayrollDate.

Basically I bring in the first record from LaborDtl, and sort the
recordset by DueDate, JobNum, OprSeq, and LaborHed.PayrollDate.

Specifying these sorts including PayrollDate will ensure that I can
sort the data in my report by DueDate (I'm grouping on JobNum of
course), as I can tell Crystal to keep the records in their original
order.

The data *looks* okay, but will need further scrutiny. We're using
MES incompletely and inconsistently so the LaborDtl data isn't great
anyways.

I think its reasonable to assume if the op is complete that the
lastlabordate represents the date the op was completed. It would be
nice to have explicit start and enddates on joboper maintained by
triggers though.

-bws

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Rob Bucek
Sent: Thursday, October 07, 2010 3:17 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Query Jobs and Operations

I think youre right about the labordtl table and not bringing it in.. or
if you did, you might only want to bring in the first dtl record for
each op in (if any) and the last (if any). The only field in the
joboper table that might give you any info along those lines is the
lastlabordate field, if you used this along with the opcomplete you
could show completion date, youre hosed on start date though.



Rob Bucek

Production Control Manager

PH: (715) 284-5376 ext 311

Mobile: (715)896-0590

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, October 07, 2010 1:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Query Jobs and Operations





I'm interested in producing a BAQ report that provides a concise
status of open jobs and their operation status.

JobHead and JobOper are clearly the place to start.

I'm finding everything I want there, except I don't see the actual
start date and completion date for each operation.

I'm looking at LaborDtl and I can bring that into the query via a
natural join, but I'm not 100% if that's what I really want to do, or if
there's a simpler way.

Basically I want to show some Job header info, and then the operations
in order, with scheduled start date, actual start date, due date, actual
completion date, and qty info.

-bws

--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich%40advancedphotonix.com>
<mailto:bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> > ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
5
What I meant to say here is that these sorts ensure 1) I get the first
LaborDtl record for the Op, and 2) I can sort my entire dataset by
DueDate, which is what my user wants.

-bws

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, October 07, 2010 4:56 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Query Jobs and Operations

[...snipped...]

Specifying these sorts including PayrollDate will ensure that I can
sort the data in my report by DueDate (I'm grouping on JobNum of
course), as I can tell Crystal to keep the records in their original
order.
6
Or write yourself a handy dandy BPM to check if the labor record
completes the op, then fling the date into a VB field..and voila..



Rob Bucek

Production Control Manager

PH: (715) 284-5376 ext 311

Mobile: (715)896-0590

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, October 07, 2010 3:58 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Query Jobs and Operations





What I meant to say here is that these sorts ensure 1) I get the first
LaborDtl record for the Op, and 2) I can sort my entire dataset by
DueDate, which is what my user wants.

-bws

-----Original Message-----
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Brian W. Spolarich
Sent: Thursday, October 07, 2010 4:56 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Query Jobs and Operations

[...snipped...]

Specifying these sorts including PayrollDate will ensure that I can
sort the data in my report by DueDate (I'm grouping on JobNum of
course), as I can tell Crystal to keep the records in their original
order.





[Non-text portions of this message have been removed]
7
Or a real query in SQL through ODBC...


--- In vantage@yahoogroups.com, "Rob Bucek" <rbucek@...> wrote:
>
> Or write yourself a handy dandy BPM to check if the labor record
> completes the op, then fling the date into a VB field..and voila..
>
>
>
> Rob Bucek
>
> Production Control Manager
>
> PH: (715) 284-5376 ext 311
>
> Mobile: (715)896-0590
>
> FAX: (715)284-4084
>
> <http://www.dsmfg.com/>
>
> (Click the logo to view our site) <http://www.dsmfg.com/>
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of Brian W. Spolarich
> Sent: Thursday, October 07, 2010 3:58 PM
> To: vantage@yahoogroups.com
> Subject: RE: [Vantage] Query Jobs and Operations
>
>
>
>
>
> What I meant to say here is that these sorts ensure 1) I get the first
> LaborDtl record for the Op, and 2) I can sort my entire dataset by
> DueDate, which is what my user wants.
>
> -bws
>
> -----Original Message-----
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf
> Of Brian W. Spolarich
> Sent: Thursday, October 07, 2010 4:56 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: RE: [Vantage] Query Jobs and Operations
>
> [...snipped...]
>
> Specifying these sorts including PayrollDate will ensure that I can
> sort the data in my report by DueDate (I'm grouping on JobNum of
> course), as I can tell Crystal to keep the records in their original
> order.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>