Trouble suppressing completed records

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 2012 Forums

Transact-SQL (2012)

Trouble suppressing completed records

Author

Topic

rusmanicai
Starting Member

13 Posts

Posted - 2013-10-26 : 14:04:32

Hello,
I need to create a report that returns all jobs in process (order header table), that have a particular process code (in the order process table), and that activity was either not clocked into (then there is no record of it in productionjob table) or it was clocked into but not completed (there is a record in production job table, and a completion code of cm010, if process complete then completion code is cm002).
I created a sp that pulls all desired fields such as job number, cust name, and connected all these tables.
A new line is added to production job table, every time someone completed a process listed in order process table, for that job.
All clock in entries are stored in production table (for all jobs).
I left joined productionjob table because a line will not be in the table if nobody clocked into the job.
My problem is how I do not know how to supress records if the activity is complete.

Please help! Thank you!

Here is the sp:

--declare
--@StartDate VARCHAR(50),
--@EndDate VARCHAR(50)
-- , @PlantID varchar(15)

-- set @StartDate='2013-10-01'
-- set @EndDate='2013-10-31'
-- set @PlantID='msi'

SELECT distinct
oh.jobnumber,
oc.UserDefined1,
oh.jobdescription,
oh.custaccount,
oh.custname,
oh.duedate,
oh.proofdate,
oh.orderdate ,
oh.salesrepcode,
oh.jobstatus,
oh.userdefined5,
oh.NoProofRequired,
oh.EntryDate,
oh.TotalSellPrice,
Oh.OrderSellPrice,
oc.ComponentType,
oc.UserDefined1,
oh.CSR,
p.processcode,
p.completioncode,
complastactivity= (SELECT TOP 1 pr.DESCRIPTION
FROM production p
INNER JOIN productionjobnumber pjn
ON p.code = pjn.productioncode
LEFT JOIN process pr
ON p.processcode = pr.processcode
LEFT JOIN completioncode c
ON c.completioncode = p.completioncode
WHERE pjn.jobnumber = oh.jobnumber
AND ( pjn.componentnumber =oc.componentnumber
OR pjn.componentnumber = 0 )
ORDER BY p.startdate DESC,
p.starttime DESC,
p.code),
lastactivity=(SELECT TOP 1 pr.DESCRIPTION
FROM production p
INNER JOIN productionjobnumber pjn
ON p.code = pjn.productioncode
LEFT JOIN process pr
ON p.processcode = pr.processcode
LEFT JOIN completioncode c
ON c.completioncode = p.completioncode
WHERE pjn.jobnumber = oh.jobnumber),
orderquantity =(select SUM(OrderQtyTable.Quantity)
FROM OrderQtyTable
WHERE OrderQtyTable.JobNumber=oh.jobnumber and oc.qtyordindex = orderqtytable.quantitylineno)

FROM orderheader oh

inner JOIN ordercomponent oc
ON oh.jobnumber = oc.jobnumber
INNER JOIN orderqtytable
ON oc.jobnumber = orderqtytable.jobnumber
AND oc.componentnumber = orderqtytable.componentnumber
AND oc.qtyordindex = orderqtytable.quantitylineno
left JOIN ProductionJobNumber on ProductionJobNumber.JobNumber=oh.JobNumber
left JOIN Process on Process.ProcessCode=ProductionJobNumber.ProductionCode
inner join OrderProcess on OrderProcess.JobNumber=oh.JobNumber
left join Production p on p.ProcessCode = Process.ProcessCode
LEFT JOIN completioncode c
ON c.completioncode = p.completioncode

WHERE
oh.PlantID=@PlantID
and ( oh.DueDate <= CONVERT(DATETIME, @EndDate)
AND oh.DueDate >= CONVERT(DATETIME, @StartDate) )
and oc.UserDefined1 not in ('Die Charges','Rush Charges','Setup Charges') and oc.Description not like '%FAI%'
and Oc.UserDefined1 in ('S0 Shipments','KR Shipments')
and oh.JobStatus ='in process'
and OrderProcess.ProcessCode in (1105,1106,1120,1130,1140)
and oc.ComponentPlanned=1
--and not(p.CompletionCode='cm002')

order by oh.DueDate, oh.UserDefined5

RJulia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-26 : 15:38:00

just add a condition like


...
AND NOT EXISTS (
                  SELECT 1 FROM ProductionJobNumber pjn
                  inner JOIN Process pc on pc.ProcessCode=pjn.ProductionCode
                  inner join Production pn on pn.ProcessCode = pc.ProcessCode
                  WHERE pn.completioncode 'cm002'
                  AND pjn.JobNumber = oh.JobNumber
                 )

in WHERE part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rusmanicai
Starting Member

13 Posts

Posted - 2013-10-27 : 10:53:45

Visak,
Thank you for your help. Your statement didfn't do it but understood the concept and used the right joins and got it to work.
ooks like had the solution yesterday while working on this looking at it from a different angle but didn't know how to use it.
realized what didn't know was using the "Not exist" function.
Thank you. You saved me again.

RJulia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-27 : 14:26:40

Np
Glad that you could sort it out!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources