WITH NUMBEROFTOTALORDER AS (
  
	SELECT DISTINCT Count(*) As NumberofTotalOrder
	From [SCE].[vw_ORDERS_1] 
	WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
	AND vw_ORDERS_1.STATUS NOT IN (95,98,99) 
	/* 95 = Shipped Complete, 98 = Cancelled Externally, 99 = Canceled Internally */ 
	
  ), PICKED AS (
  
    SELECT DISTINCT Count(*) As Picked
	From [SCE].[vw_ORDERS_1] 
	WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
	AND vw_ORDERS_1.STATUS IN (55) 
	
	/* 15 =  Part Allocated / Part Picked
	 * 25 = Part Released/Part Picked
	 * 51 = In Picking
	 * 52 = Part Picked
	 * 53 = Part Picked / Part Shipped
	 * 55 = Picked Complete
	 * 57 = Picked / Part Shipped
	 *  */ 
  
  ), PACKED AS (
  
	SELECT DISTINCT Count(*) As Packed
	From [SCE].[vw_ORDERS_1] 
	WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
	AND vw_ORDERS_1.STATUS IN (68) 
	
	/* 61 =  In Packing
	 * 68 = Pack Complete
	 *  */
	
	), PRIORITY AS (
	
	SELECT DISTINCT Count(*) As Priority
	From [SCE].[vw_ORDERS_1] 
	WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
	AND vw_ORDERS_1.STATUS NOT IN (95,98,99) 
	AND vw_ORDERS_1.PRIORITY IN (1,2,3)
	
	/* 95 = Shipped Complete, 98 = Cancelled Externally, 99 = Canceled Internally */ 
	/* 1 = Highest Priority, 3 = Normal Priority */
 
    ), ACTUALORDER AS (
    
    SELECT DISTINCT Count(*) As ActualOrder
	From [SCE].[vw_ORDERS_1] 
	WHERE vw_ORDERS_1.TYPE in ('ECOM','ECOMAPP')
	AND vw_ORDERS_1.ORDERDATE = DATEADD(dd, -1, CAST( GETDATE() AS Date)) 

	), ORDERSHIPPED24HOUROLD AS (
	
	SELECT OrderShipped24HourOld, 
	CASE 
		WHEN DATENAME(weekday, GETDATE()) = 'Monday' THEN 
		(
			SELECT DISTINCT COUNT(*) As OrderShipped24HourOld
			From [SCE].[vw_ORDERS_1] 
			WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
			AND vw_ORDERS_1.ACTUALSHIPDATE=  DATEADD(dd,-3,CAST( GETDATE() AS Date))
			AND vw_ORDERS_1.ORDERDATE = DATEADD(dd,-4,CAST( GETDATE() AS Date ))
		
		)
		
		
	
	
	
	), ORDERSHIPPEDGREATERTHAN24HOUROLD AS 
	
		IF (DATENAME(weekday, GETDATE()) IN ('Monday'))
			BEGIN
				SELECT DISTINCT Count(*) As OrdersShippedGreaterThan24HoursOldForMONDAY
				FROM [SCE].[vw_ORDERS_1] 
				WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
				AND vw_ORDERS_1.ACTUALSHIPDATE = DATEADD(dd,-3,CAST( GETDATE() AS Date ))
				AND vw_ORDERS_1.ORDERDATE < DATEADD(dd,-4,CAST( GETDATE() AS Date ))
			END
		ELSE IF (DATENAME(weekday, GETDATE()) IN ('Tuesday'))
			BEGIN
				SELECT DISTINCT Count(*) As OrdersShippedGreaterThan24HoursOldForTuesday
				FROM [SCE].[vw_ORDERS_1] 
				WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
				AND vw_ORDERS_1.ACTUALSHIPDATE = DATEADD(dd,-1,CAST( GETDATE() AS Date ))
				AND vw_ORDERS_1.ORDERDATE < DATEADD(dd,-4,CAST( GETDATE() AS Date ))
			END
		ELSE
			BEGIN
				SELECT DISTINCT Count(*) As OrderShippedGreaterThan24HourOld
				FROM [SCE].[vw_ORDERS_1] 
				WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
				AND vw_ORDERS_1.ACTUALSHIPDATE = DATEADD(dd,-1,CAST( GETDATE() AS Date ))
				AND vw_ORDERS_1.ORDERDATE < DATEADD(dd,-2,CAST( GETDATE() AS Date ))
			END
	)
  	SELECT NUMBEROFTOTALORDER.NumberofTotalOrder, PICKED.Picked, PACKED.Packed, PRIORITY.Priority, ACTUALORDER.ActualOrder, ORDERSHIPPED24HOUROLD.OrderShipped24HourOld,
  	ORDERSHIPPEDGREATERTHAN24HOUROLD.OrderShippedGreaterThan24HourOld
	FROM DAYSTART, PICKED, PACKED, PRIORITY, ACTUALORDER, ORDERSHIPPED24HOUROLD, ORDERSHIPPEDGREATERTHAN24HOUROLD
	

IF (DATENAME(weekday, GETDATE()) = 'Monday')
		BEGIN
			SELECT DISTINCT COUNT(*) As OrderShipped24HourOld
			From [SCE].[vw_ORDERS_1] 
			WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
			AND vw_ORDERS_1.ACTUALSHIPDATE=  DATEADD(dd,-3,CAST( GETDATE() AS Date))
			AND vw_ORDERS_1.ORDERDATE = DATEADD(dd,-4,CAST( GETDATE() AS Date ))	
		END
	ELSE IF (DATENAME(weekday, GETDATE()) = 'Tuesday')
		BEGIN
			SELECT DISTINCT Count(*) As OrderShipped24HourOld
			From [SCE].[vw_ORDERS_1] 
			WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
			AND vw_ORDERS_1.ACTUALSHIPDATE=  DATEADD(dd,-1,CAST( GETDATE() AS Date ))
			AND vw_ORDERS_1.ORDERDATE = DATEADD(dd,-4,CAST( GETDATE() AS Date ))
		END
	ELSE
		BEGIN
			SELECT DISTINCT Count(*) As OrderShipped24HourOld
			From [SCE].[vw_ORDERS_1] 
			WHERE vw_ORDERS_1.TYPE IN ('ECOM','ECOMAPP')
			AND vw_ORDERS_1.ACTUALSHIPDATE = DATEADD(dd,-1,CAST( GETDATE() AS Date ))
			AND vw_ORDERS_1.ORDERDATE = DATEADD(dd,-2,CAST( GETDATE() AS Date ))	
		END