July 14, 2008 at 9:04 am
Hi - new to the forum.
I have a query that's frustrating me no end. I have a view in SQL Server 2000 that provides a live electronic production board for the factory. Jobs are loaded and it shows various parameters. Works great - BUT -
JOB_ID JOB_NAME SIZE run SHEETS QTY job_status m/c pages UNIT1 UNIT2 .... UNIT10
53365ABC 11480112250022018X
53368KMP 1148011240022018X
53370LFTTT114802220476120112XX
53370LFTTT1 14802220476120116XX
53366mmmC 178625197170140140XXXXX
53361PAT0127381133424113018X
53342PAT0127381133424113018X
Numbers 53361, 53342are the same job, but due to changes the second one is the correct one, but the 53342 job isn't deleted from the job queue table - which is fine, but I need to filter out the earlier job - all other details are pretty much identical except the UNITn that it will tun on ( the 'X' values). I'm running a php web page against it and can do the query to filter it from there.
Also - I have a problem where the job 53370 (LFTTT) has split into 2 - just the way the system works - but I need to add together the values in column 9 - ie 12 and 16 and combine the 2 rows into a single job.
Can anyone point me in the right direction?
July 14, 2008 at 9:30 am
Hopefully this will get you started...
--data
declare @t table (JOB_ID int, JOB_NAME varchar(10), [?] int, SIZE int, run int, SHEETS int,
QTY int, job_status int, mc int, pages int, UNIT1 varchar(1), UNIT2 varchar(1), UNIT10 varchar(1))
insert @t
select 53365, 'ABC', 1, 1480, 1, 1, 22500, 2, 201, 8, null, null, 'X'
union all select 53368, 'KMP', 1, 1480, 1, 1, 2400, 2, 201, 8, null, null, 'X'
union all select 53370, 'LFTTT', 1, 1480, 2, 2, 20476, 1, 201, 12, null, 'X', 'X'
union all select 53370, 'LFTTT', 1, 1480, 2, 2, 20476, 1, 201, 16, null, 'X', 'X'
union all select 53366, 'mmmC', 1, 786, 2, 5, 197170, 1, 401, 40, 'X', 'X', 'X'
union all select 53361, 'PAT01', 2, 738, 1, 1, 334241, 1, 301, 8, null, 'X', null
union all select 53342, 'PAT01', 2, 738, 1, 1, 334241, 1, 301, 8, 'X', null, null
--calculation
select JOB_ID, max(JOB_NAME) as JOB_NAME, max([?]) as [?], max(SIZE) as SIZE, max(run) as run,
max(SHEETS) as SHEETS, max(QTY) as QTY, max(job_status) as job_status, max(mc) as mc,
sum(pages) as pages, max(UNIT1) as UNIT1, max(UNIT2) as UNIT2, max(UNIT10) as UNIT10
from @t
where JOB_ID in (select max(JOB_ID) from @t group by JOB_NAME)
group by JOB_ID order by JOB_NAME
/* results
JOB_ID JOB_NAME ? SIZE run SHEETS QTY job_status mc pages UNIT1 UNIT2 UNIT10
------- ---------- --- ----- ----- ------- -------- ----------- ----- ------- ----- ----- ------
53365 ABC 1 1480 1 1 22500 2 201 8 NULL NULL X
53368 KMP 1 1480 1 1 2400 2 201 8 NULL NULL X
53370 LFTTT 1 1480 2 2 20476 1 201 28 NULL X X
53366 mmmC 1 786 2 5 197170 1 401 40 X X X
53361 PAT01 2 738 1 1 334241 1 301 8 NULL X NULL
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 15, 2008 at 4:58 am
Thanks for that.
I was thinking along the lines of variables and the DECLARE type of thing but have never used it.
The data is dynamic and jobs are added constantly throughout the day, so I presume in the data section I could just put the field names rather than the sctual data values? When a job is completed it drops out of the source jobqueue table and so drops out of the view.
July 15, 2008 at 5:06 am
The data section is just for this example. You only need the calculation section to work with your actual data.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 18, 2008 at 6:57 am
That's worked perfectly - thanks very much.
:0)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply