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
