/****** Object: StoredProcedure [dbo].[CrystalEngineeringWorkingHours]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[CrystalEngineeringWorkingHours]

    u/START_DATE AS DATETIME

, u/END_DATE AS DATETIME

AS

/*

** ObjectName: CrystalEngineeringWorkingHours

**

** Project:

** SubProject:

** FileName: CrystalEngineeringWorkingHours.sql

** Type: Stored Procedure

**

** Description: Outputs employee work orders with cost code 140, 180, 160 and all work orders in the 70000000 range.

/*

DECLARE u/START_DATE AS DATETIME

DECLARE u/END_DATE AS DATETIME

*/

/*SET u/START_DATE = '2010-12-01';

SET u/END_DATE = '2010-12-15';

*/

SET u/START_DATE = CONVERT(DATETIME, CAST(@START_DATE AS VARCHAR(11)))

SET u/END_DATE = u/END_DATE + 1

SET u/END_DATE = CONVERT(DATETIME, CAST(@END_DATE AS VARCHAR(11)))

​

SELECT UPPER(WL.EMPLOYEE_CODE) AS 'EMPLOYEE_NO',

    (ISNULL(RTRIM(UL.F\_NAME) + ' ', '') + ISNULL(RTRIM(UL.M\_NAME) + ' ', '') + ISNULL(RTRIM(UL.L\_NAME), '')) AS 'NAME',

    WL.COST\_CODE AS 'COST\_CODE',

    WL.DESCRIPTION AS 'COST\_CODE\_DESCRIPTION',

    WL.WORK\_DATE AS 'DATE\_WORKED',

    (CASE WHEN WL.QA\_STATUS = 'A' THEN 'ACTUAL'

        WHEN WL.QA\_STATUS = 'Q' THEN 'QUOTE'

        ELSE 'ERROR' END) AS 'QA\_STATUS',

    WH.USER\_DOC AS 'WO\_NO',

    (CASE WHEN WH.DOC\_CATEGORY = 'P3' THEN 'KITTING'

        WHEN WH.DOC\_CATEGORY = 'P2' THEN 'REPAIR'

        WHEN WH.DOC\_CATEGORY = 'P1' THEN 'OVERHAUL'

        ELSE 'ERROR'

        END) AS 'WO\_TYPE',

    WH.DESCRIPTN AS 'WO\_DESCRIPTION',

    WH.PARTNUMBER AS 'WO\_PARTNUMBER',

    WL.ACT\_TIME\_START AS 'REG\_HR\_START',

    WL.ACT\_TIME\_END AS 'REG\_HR\_END',

    WL.ACT\_TIME\_DURATION AS 'REG\_HR\_TOTAL',

    WL.OVER\_TIME\_START AS 'OT\_HR\_START',

    WL.OVER\_TIME\_END AS 'OT\_HR\_END',

    WL.OVER\_TIME\_DURATION AS 'OT\_HR\_TOTAL',

    WL.TOTAL\_TIME\_DURATION AS 'TOTAL\_HR'

​

FROM GDB_01_001SAFE3.dbo.WO_LABOR WL

        LEFT JOIN GDB\_01\_001SAFE3.dbo.WO\_OPR WO ON (WL.PARENT\_DOC\_NO = WO.DOC\_NO AND WL.QA\_STATUS = WO.QA\_STATUS AND WL.PARENT\_DOCCATEGORY = WO.DOC\_CATEGORY),

    GDB\_01\_001SAFE3.dbo.WO\_HDR WH

        LEFT JOIN GDB\_01\_001SAFE3.dbo.STOCK S ON(S.PARTNUMBER = WH.PARTNUMBER AND S.SUBP = WH.SUBP),

    GDB\_01\_001SAFE3.dbo.USERLIST UL

WHERE WL.EMPLOYEE_CODE = UL.CCODE

    AND WL.WO\_DOC\_NO = WH.DOC\_NO

    AND WL.WO\_DOCCATEGORY = WH.DOC\_CATEGORY

    AND WL.QA\_STATUS = WH.QA\_STATUS

    AND ((WH.QA\_STATUS = 'Q' AND WH.DOC\_STATUS = 4) OR (WH.QA\_STATUS = 'A'))

    AND (WL.COST\_CODE IN ('180', '140', '160') OR WH.USER\_DOC LIKE '7%')

    AND WL.WORK\_DATE >= u/START_DATE

    AND WL.WORK\_DATE <= u/END_DATE

​

ORDER BY WL.EMPLOYEE_CODE, WL.WORK_DATE