ProTech-Online.com

Microsoft SQL code example to pull Syteline job materials.

USE [testsite_live_App]
GO
/****** Object:  StoredProcedure [MYSCHEMA].[_chad_TestJobMaterialsSp]    Script Date: 06/24/2013 23:24:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MYSCHEMA].[_chad_TestJobMaterialsSp]
      @JobStatus nchar(1)
      ,@StartingWC nvarchar(6)
      ,@EndingWC nvarchar(6)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT job.stat AS Status
      ,jm.job AS Job
      ,jm.suffix AS Release
      ,job.qty_released AS 'Parent Qty'
      ,itm.item AS 'Parent Item'
      ,job.description AS 'Parent Description'
      ,itm.product_code AS 'Parent Product Code'
      ,jm.oper_num AS Operation
      ,jbr.wc AS WC
      ,jm.item AS Material
      ,CASE
            WHEN EXISTS (SELECT TOP 1 1 FROM item  WHERE (item.item = jm.item))
            THEN Matl.description
            ELSE jm.description
            END AS 'Material Description'
      ,jm.matl_qty_conv as Qty
      ,jm.u_m AS UofM
      FROM jobmatl AS jm
            INNER JOIN job AS job
                  ON jm.job = job.job
                        AND jm.suffix = job.suffix
            INNER JOIN jobroute AS jbr
                  ON jm.job = jbr.job
                        AND jm.suffix = jbr.suffix
                        AND jm.oper_num = jbr.oper_num
            LEFT OUTER JOIN item AS itm
                  ON job.item = itm.item
                        AND (itm.job = job.job or job.type <> 'S')
            LEFT OUTER JOIN item AS Matl
                  ON Matl.item = jm.item
      WHERE job.stat = @JobStatus
            AND jbr.wc between @StartingWC AND @EndingWC
      ORDER BY jm.job, jm.suffix, jm.oper_num

END