looping thur same table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

looping thur same table

Author

Topic

jbannister
Starting Member

6 Posts

Posted - 2009-11-02 : 14:39:43

Hi,
My question is this. I have a table which includes the fields job#, Workcenter, sequence, startdate, status. each job can have many workcenters, which can have a status of open, started, complete. What I am trying to do is find the current workcenter.

Ex. Job workcenter sequence status
1234 program 0 complete
1234 cut 1 start
1234 bend 2 open
1234 weld 3 open
I would like to find all open weld jobs and which workcenter those jobs are in. In the example above that workcenter would be cut. if the status of cut was open it would still be the current workcenter.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-02 : 16:40:54

Does this address your requirements?

select Job, Workcenter
from MyTable
where sequence = 'weld' and status = 'open'

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

jbannister
Starting Member

6 Posts

Posted - 2009-11-03 : 09:48:33

This does not address the requirements, but thanks for the input.
I need to be able to return the workcenter where the job is currently at, not the workcenter that I am reporting on. I am using CR 10 to return all jobs where status = start or open and workcenter = @workcenter parameter. All is good until I try to find where the job(s) are currently at(at the time the report runs).I know i will need to do a sub-report or some type of loop BOF/EOF to return this, but I am having no luck. My guess is the statement would be something like:
select @workcenter
if status = s then @currentWC = @workcenter else if
previous workcenter status = c then @currentWC = @workcenter else if
previous workcenter status = s then @currentWC = previous workcenter
--- and so on.
I think looping backwards based on sequence number should get me what I am looking for, but I do not know how to do.
Sorry for the long explanation.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-03 : 11:22:20

in the example above how could cut be the workcenter because it does not match your requirement. that job is not open it is start. there is something missing here. the long explanation is because of that, something is missing in your design imho. maybe a date field of some sort. also how is complete 0 if it is the last sequence for example. please post some more details of real data and exactly what you want
tell us more about that date that might be your key, if you order by that date SELECT TOP 1 order by date desc
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jbannister
Starting Member

6 Posts

Posted - 2009-11-03 : 13:07:13

This 1st query returns jobs for selected workcenter

SELECT Job_Operation.Job, Job_Operation.Work_Center, Job_Operation.Sequence, Job_Operation.Sched_Start, Job_Operation.Status

FROM Job_Operation INNER JOIN Job ON Job_Operation.Job = Job.Job

WHERE (Job_Operation.Work_Center = 'brake cell') AND (Job.Status = 'active') AND (Job_Operation.Inside_Oper = 1) AND
(Job_Operation.Status = 'o' or Job_Operation.Status = 's')and (Job_Operation.Sched_Start <= CONVERT(DATETIME, '2009-11-02 00:00:00', 102))

ORDER BY Job_Operation.Sched_Start

RESULTS 64 ROWS

37849-16A7 BRAKE CELL 2 2009-10-07 O
37849-177 BRAKE CELL 2 2009-10-21 O
7849-176B2 BRAKE CELL 2 2009-10-23 O
37847SA7 BRAKE CELL 2 2009-10-23 S
37849-174 BRAKE CELL 1 2009-10-23 O
37849-175 BRAKE CELL 1 2009-10-23 O
40022 BRAKE CELL 2 2009-10-27 O
39999-1 BRAKE CELL 2 2009-10-28 O
39999-2 BRAKE CELL 2 2009-10-28 O
0024129074 BRAKE CELL 1 2009-10-28 O
0024129074 BRAKE CELL 2 2009-10-28 O
0024129076 BRAKE CELL 2 2009-10-28 O
40027 BRAKE CELL 1 2009-10-28 O
40027 BRAKE CELL 2 2009-10-28 O
40023 BRAKE CELL 1 2009-10-29 O
40170-A01 BRAKE CELL 2 2009-10-29 O
40173 BRAKE CELL 2 2009-10-29 S
39911 BRAKE CELL 2 2009-10-30 O
39913 BRAKE CELL 2 2009-10-30 O
39914 BRAKE CELL 2 2009-10-30 O
40026 BRAKE CELL 3 2009-10-30 O

Job 37847SA7 and 40173 have status of S (started) so the currentWC would be BRAKE CELL. Since all other status are O (open) I need to find out where the job is.

2nd query a few selected job numbers from above
SELECT Job, Work_Center, Sequence, Status, Sched_Start
FROM Job_Operation
WHERE (Job = '37847SA7' or Job = '40026' or Job = '37849-177') and Inside_Oper = 1

RESULTS

37847SA7 MITS 4000 0 C NULL
37847SA7 MANUAL OPS 1 C NULL
37847SA7 BRAKE CELL 2 S 2009-10-23

37849-177 MITS 4000 0 S 2009-10-20
37849-177 MANUAL OPS 1 O 2009-10-21
37849-177 BRAKE CELL 2 O 2009-10-21
37849-177 WELD 3 O 2009-10-22
37849-177 WELD 4 O 2009-10-22

40026 PROGRAM 0 C NULL
40026 TURRETS 1 O 2009-10-29
40026 PEMSERT 2 O 2009-10-29
40026 BRAKE CELL 3 O 2009-10-30

Job 37847SA7 currentWC is brake because status is S (someone in brake area has logged into this job. Now if the status was O the currentWC would still be brake, because MANUAL OPS, the wc before has status of C (Complete).
Job 37849-177 currentWC is Mits because status is S.
Job 40026 currentWC is TURRETS because it is the 1st WC, in sequence with status of S OR O.

Note. When jobs are created in our system the routing of workcenters always starts the 1st workcenter at 0 then 1, 2, etc.
So select workcenter do until status = S or seq# = 0 if status = s then currentwc = workcenter else loop seq = seq-1
if seq = 0 and status = O then currentwc = this workcenter.

I hope this helps

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-03 : 15:03:34

can each Sequence have 1 or more status? What is the relationship between Sequence and status


Sequence   0     Status  Open
Sequence   0     Status  Start
Sequence   0     Status  Complete

Sequence   1     Status  Open
Sequence   1     Status  Start
Sequence   1     Status  Complete

Sequence   2     Status  Open
Sequence   2     Status  Start
Sequence   2     Status  Complete


Sequence   3     Status  Open
Sequence   3     Status  Start
Sequence   3    Status  Complete

Is it something like the above? with Status starting at Open and finishing off on Complete. Can status be NULL? Can Sequence be NULL?
Can a job be in more than one sequence with different statuses?
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-03 : 15:13:57

quote:
Originally posted by jbannister

Hi,
My question is this. I have a table which includes the fields job#, Workcenter, sequence, startdate, status. each job can have many workcenters, which can have a status of open, started, complete.

What I am trying to do is find the current workcenter.

Define "Current"

If it's dependant on WHEN it was added to the database, then your table would need an identity column, datetime column with a default of GetDate(), or a procedurally added value that would indicate that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jbannister
Starting Member

6 Posts

Posted - 2009-11-04 : 13:41:17

I will try to answer both posts.
As stated in my last post when a job is entered into the system the routing information is added to the job_operation table(This is done within the front-end interface). Job 40026 has a route of 1st program, 2nd turrets, 3rd pemsert, and last(4th) brake cell. This is how this job(part) must go on the manufacturing floor. All Statuses are set to open when the system writes to the job_operation table. The system also writes the sequence number based on the routing info(starting at 0). The sequence and status fields cannot be null. Once a job is active(in the system) Sequence # does not change. The Status field changes if someone logs into that route. Once the route is logged in by user, status changes to S. When user logs out of job they can leave status as started(S) or Completed(C). Status changes do not write new records, the change just updates the same record, which means each record(with sequence#)can only have one status value. A job can be in more than one sequence with different statuses, because the table job_operation has a many-to-one relationship with table JOB. The results in my last post shows 4 records with job# 40026.
Current is being define by us the user. We define current as the workcenter with the GREATEST sequence number that has a status of S.
If no workcenter has the status of S then current would be the workcenter with the LOWEST sequence number that has a status of O.
I hope this help and do thank you for your help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-04 : 14:14:30

try this and post back.


SELECT jo.Job,         
       jo.Sequence, 
       jo.Sched_Start, 
       jo.Status,
       CurrentWorkCenter = 
       CASE 
          WHEN jo.Status = 'S' THEN (SELECT TOP 1 Sequence FROM Job_Operation gs1 WHERE gs1.Job = j.Job AND gs1.Status = 'S' ORDER BY Sequence DESC)
          WHEN jo.Status <> 'S' THEN (SELECT TOP 1 Sequence FROM Job_Operation gs2 WHERE gs2.Job = j.Job AND gs2.Status = 'O' ORDER BY Sequence ASC)  
          ELSE jo.Work_Center,      
       END 
  FROM Job_Operation jo
 INNER JOIN Job j
    ON Job_Operation.Job = Job.Job

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jbannister
Starting Member

6 Posts

Posted - 2009-11-09 : 11:57:16

By updating yosiasz query I have managed to almost get the results we are looking for.
STATEMENT:

SELECT A.JNUM, A.WC, A.CS, A.CurrentWorkCenter, A.SEQ
FROM ( SELECT Job.Job as JNUM,
Job_Operation.Sequence as SEQ,
Job_Operation.Status as CS,
Job_Operation.Work_Center as WC,
CurrentWorkCenter =
CASE
WHEN Job_Operation.Status = 'S' THEN (SELECT TOP 1 Sequence FROM Job_Operation gs1 WHERE gs1.Job = Job.Job AND gs1.Status = 'S' ORDER BY Sequence DESC)
WHEN Job_Operation.Status <> 'S' THEN (SELECT TOP 1 Sequence FROM Job_Operation gs2 WHERE gs2.Job = Job.Job AND gs2.Status = 'O' ORDER BY Sequence ASC)
ELSE Job_Operation.Work_Center
END
FROM Job_Operation
INNER JOIN Job
ON Job_Operation.Job = Job.Job
where Job.Status = 'ACTIVE' AND (Job_Operation.Status = 'O' OR Job_Operation.Status = 'S') AND Job_Operation.Inside_Oper = 1
) AS a
WHERE a.CurrentWorkCenter = a.SEQ
order by a.jnum

RESULTS:

0040516401 MITS 4000 S 0 0
0040516401 BRAKE CELL O 1 1
0040516402 MITS 4000 S 0 0
0040516402 MANUAL OPS O 1 1
0112551981 MITS 4000 O 0 0
0112551982 MANUAL OPS O 0 0
0112551983 MITS 4000 O 0 0
0112551984 MANUAL OPS O 0 0
0672848701 MITS 4000 O 0 0
0672848702 MITS 4000 O 0 0
0683948801 MITS 4000 O 0 0
0683948802 MANUAL OPS O 0 0
0750649501 MITS 4000 O 0 0
0750649502 MITS 4000 O 0 0
0750649503 MITS 4000 O 0 0

Problem is, when a workcenter has a status of S the query also returns the first workcenter with a status of O after the workcenter with status of S. What I am trying to do is have the workcenter with the status of S be the only workcenter returned. If there is no workcenter with S as a status then this query returns the correct info.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-09 : 13:17:49

which one in this example you show?


0040516401 MITS 4000 S 0 0
0040516401 BRAKE CELL O 1 1

well your requirements keep shifting so it is kind of hard. like mentioned earlier there might be a flaw in your design. for example
you said
"Current is being define by us the user. We define current as the workcenter with the GREATEST sequence number that has a status of S.
If no workcenter has the status of S then current would be the workcenter with the LOWEST sequence number that has a status of O." but then you have all these subqueries and where clauses that show something totally different. so post back for this question: what if it is neither S nor O then what?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jbannister
Starting Member

6 Posts

Posted - 2009-11-12 : 08:53:23

Got it to work
STATEMENT

SELECT A.JNUM, A.WC, A.CS, A.CurrentWorkCenter, A.SEQ
FROM ( SELECT Job.Job as JNUM,
Job_Operation.Sequence as SEQ,
Job_Operation.Status as CS,
Job_Operation.Work_Center as WC,
CurrentWorkCenter =
CASE
WHEN Job_Operation.Status = 'S' THEN (SELECT TOP 1 Sequence FROM Job_Operation gs1 WHERE gs1.Job = Job.Job AND gs1.Status = 'S' ORDER BY Sequence ASC)
WHEN Job_Operation.Status = 'O' THEN (SELECT TOP 1 Sequence FROM Job_Operation gs2 WHERE gs2.Job = Job.Job AND gs2.Status = 'O' ORDER BY Sequence ASC)
END
FROM Job_Operation
INNER JOIN Job ON Job_Operation.Job = Job.Job
where Job.Status = 'ACTIVE' AND (Job_Operation.Status = 'O' OR Job_Operation.Status = 'S') AND Job_Operation.Inside_Oper = 1
) AS a
WHERE a.CurrentWorkCenter = a.SEQ
order by cs desc

I use this as a sub-report in CR10 and then I suppress all rows except the first. I want to thank yosiasz for all the help.
I am sure there are other/better ways to do this and I will continue to see if anyone post something different.

THANKS AGAIN YOSIASZ :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources