Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @LatestYear numeric(19,5), @LatestMonth numeric(19,5), @MinDate DATETIME
- SET @MinDate = CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
- SET @LatestYear = (SELECT MAX([YEAR])as MaxYear FROM [ILS].[dbo].[LOCATION_INVENTORY_SNAPSHOT])
- SET @LatestMonth = (SELECT MAX([MONTH])as MaxMonth FROM [ILS].[dbo].[LOCATION_INVENTORY_SNAPSHOT] WHERE [YEAR] = @LatestYear)
- SELECT dbo.SHIPMENT_HEADER.COMPANY, dbo.SHIPMENT_DETAIL.ITEM, dbo.SHIPMENT_DETAIL.ITEM_DESC, dbo.XREF_OTYPE_TO_BCODE.ORDER_TYPE AS OrderType,
- CASE dbo.XREF_OTYPE_TO_BCODE.ORDER_TYPE WHEN 75 THEN 'NON-REPLACMENT' WHEN 77 THEN 'NON-REPLACMENT' WHEN 79 THEN 'REPLACMENT' WHEN
- 89 THEN 'REPLACMENT' ELSE 'UNKNOWN TYPE' END AS OrderTypeDesc, MAX(dbo.SHIPMENT_HEADER.ACTUAL_SHIP_DATE_TIME) AS LastShipDate
- FROM dbo.SHIPMENT_HEADER INNER JOIN
- dbo.SHIPMENT_DETAIL ON dbo.SHIPMENT_HEADER.INTERNAL_SHIPMENT_NUM = dbo.SHIPMENT_DETAIL.INTERNAL_SHIPMENT_NUM INNER JOIN
- (
- SELECT
- [MONTH]
- ,[YEAR]
- ,[COMPANY]
- ,[ITEM]
- ,[ON_HAND_QTY]
- FROM [ILS].[dbo].[LOCATION_INVENTORY_SNAPSHOT]
- WHERE ON_HAND_QTY > 0
- AND [YEAR] = @LatestYear
- AND [MONTH] = @LatestMonth
- ) as ItemOnHand ON SHIPMENT_DETAIL.ITEM = ItemOnHand.Item
- LEFT OUTER JOIN
- dbo.XREF_OTYPE_TO_BCODE ON dbo.SHIPMENT_HEADER.ORDER_TYPE = dbo.XREF_OTYPE_TO_BCODE.BUDGET_CODE
- WHERE (dbo.SHIPMENT_HEADER.ACTUAL_SHIP_DATE_TIME > @MinDate)
- GROUP BY dbo.XREF_OTYPE_TO_BCODE.ORDER_TYPE, dbo.SHIPMENT_DETAIL.ITEM, dbo.SHIPMENT_DETAIL.ITEM_DESC, dbo.SHIPMENT_HEADER.COMPANY
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement