GO
/****** Object:  StoredProcedure [dbo].[usp_ConsultPackingEachDay]    Script Date: 12/12/2017 7:37:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC usp_ConsultPackingEachDay 'P30', '12/12/2017', '-1', '-1'

ALTER PROCEDURE [dbo].[usp_ConsultPackingEachDay]
    @site_key NVARCHAR(50) = NULL
    ,@start_date DATETIME = NULL
    --,@end_date DATETIME = NULL
    ,@classification NVARCHAR(5)
    ,@partNumber NVARCHAR(MAX)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

IF @site_key IS NULL
    SET @site_key = dbo.ufn_GetSite()

--IF @start_date IS NULL
--  SET @start_date = CAST(GETDATE() AS DATE)


--DECLARE @start_time TIME
--DECLARE @end_time TIME
--DECLARE @from_add_days INT
--DECLARE @to_add_days INT
DECLARE @end_date DATETIME
DECLARE @days AS NVARCHAR(500)
DECLARE @PIVOT AS NVARCHAR(MAX)

---- CRAMIREZ 12/05/2016 Date range used for Packing
--SET @start_date = DATEADD(HOUR, 6, @start_date)

--SET @end_date = DATEADD(DAY,1,DATEADD(SECOND,-1,@start_date))

-- <--- Change CTE to theview uvw_ListPackingByHour

DECLARE @days_table TABLE([1] NVARCHAR(10),
                          [2] NVARCHAR(10),
                          [3] NVARCHAR(10),
                          [4] NVARCHAR(10),
                          [5] NVARCHAR(10),
                          [6] NVARCHAR(10),
                          [7] NVARCHAR(10),
                          [8] NVARCHAR(10),
                          [9] NVARCHAR(10),
                          [10] NVARCHAR(10));

INSERT @days_table([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) VALUES
(LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -10, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -9, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -8, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -7, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -6, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -5, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -4, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -3, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -2, @start_date) ), 0), 103), 5),
 LEFT(CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -1, @start_date) ), 0), 103), 5)
 )

 SET @days = (SELECT [Days]= STUFF(
              COALESCE(', [' + RTRIM([1]), '')
            + COALESCE('], [' + RTRIM([2]), '')
            + COALESCE('], [' + RTRIM([3]), '')
            + COALESCE('], [' + RTRIM([4]), '')
            + COALESCE('], [' + RTRIM([5]), '')
            + COALESCE('], [' + RTRIM([6]), '')
            + COALESCE('], [' + RTRIM([7]), '')
            + COALESCE('], [' + RTRIM([8]), '')
            + COALESCE('], [' + RTRIM([9]), '')
            + COALESCE('], [' + RTRIM([10]), '')
            + COALESCE(']', '')
            , 1, 2, '')
            FROM @days_table)

SET @end_date = @start_date

SET @start_date = CONVERT(VARCHAR(15), DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DAY, -10, @start_date) ), 0), 103)


 SET @PIVOT = ('SELECT *
  FROM (SELECT scoe_whst_wheel_style_part_number, wheel_style, effective_date, classification,ISNULL(packing_total,0) AS packing_total
          FROM cte
          WHERE (classification = ' + @classification +' OR ' + @classification + '= '+'-1'+')) aps
 PIVOT (SUM(packing_total) FOR effective_date IN
         (
         ' + @days + '
          )
         ) AS pvt')

;WITH cte
AS
(
SELECT
    ISNULL(csoe.scoe_whst_wheel_style_part_number, 'ND') scoe_whst_wheel_style_part_number
    ,ws.wheel_style
    --,DAY(effective_date) AS 'effective_date'
    --,DATEPART(HOUR, effective_date) AS effective_hour
    ,LEFT(CONVERT(VARCHAR(15), csoe.effective_date, 103), 5) AS 'effective_date'
    ,SUM(ISNULL(quantity,0)) AS packing_total
    ,classification
FROM collect_schemas_operations_equipments csoe
     INNER JOIN
     wheel_styles ws ON csoe.scoe_whst_wheel_style_part_number = ws.wheel_style_part_number
WHERE csoe.opre_operation_key = '090'
    AND effective_date BETWEEN @start_date AND @end_date
    AND opre_result_id = 'ACC'
    AND record_status = 'ACT'
    AND ((csoe.scoe_whst_wheel_style_part_number IN (SELECT * FROM SPLIT(@partNumber))) OR @partNumber = '-1')
GROUP BY
    ISNULL(csoe.scoe_whst_wheel_style_part_number, 'ND')
    ,ws.wheel_style
    ,production_start_date
    , classification
    , effective_date
)

SELECT * FROM cte

EXEC (@pivot)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT OFF