API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 22nd, 2020
109
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.68 KB | None | 0 0
raw download clone embed print report
  1. SELECT ROW_NUMBER() OVER (ORDER BY CONTAINER_ID) AS RowNumber, COUNT(*) OVER () AS TotalRows, ORDER_ID, CUSTOMER_ID, NAME, ADDRESS1, ADDRESS2, TOWN, POSTCODE, COUNTRY
  2. , CONTACT_PHONE, DELIVER_BY_DATE, DOCUMENTATION_TEXT_2, DOCUMENTATION_TEXT_3, ORDER_REFERENCE
  3. , TO_LOC_ID, CARRIER_ID, CONTAINER_ID, LOCATION_ID, cNAME, cADDRESS1, cADDRESS2
  4. , cPOSTCODE, cTOWN, cCOUNTRY, MAX(SKU_ID) AS SKU_ID, MAX(DESCRIPTION) AS DESCRIPTION, MAX(EAN_PARCEL) AS EAN_PARCEL
  5. , SUM(QTY_ON_HAND) AS QTY_ON_HAND, MAX(ExpiryDate) AS ExpiryDate, MAX(ExpiryDateEAN) AS ExpiryDateEAN, COUNT(SKU_ID) AS CountSkuID FROM (SELECT oh.ORDER_ID, oh.CUSTOMER_ID, oh.NAME, oh.ADDRESS1, oh.ADDRESS2, oh.TOWN, oh.POSTCODE, oh.COUNTRY
  6. , oh.CONTACT_PHONE, oh.DELIVER_BY_DATE, oh.DOCUMENTATION_TEXT_2, oh.DOCUMENTATION_TEXT_3, oh.ORDER_REFERENCE
  7. , oh.TO_LOC_ID, oh.CARRIER_ID, il.CONTAINER_ID, il.LOCATION_ID,c.NAME AS cNAME, c.ADDRESS1 AS cADDRESS1, c.ADDRESS2 AS cADDRESS2
  8. , c.ZipCode AS cPOSTCODE, c.TOWN AS cTOWN, c.COUNTRY AS cCOUNTRY, il.SKU_ID, s.DESCRIPTION, s.EAN_PARCEL
  9. , SUM(il.QTY_ON_HAND) AS QTY_ON_HAND, SUBSTRING((SELECT ',' + CONVERT(nvarchar(10),EXPIRY_DATE,103)
  10.     + ' (' + CONVERT(nvarchar(8),SUM(QTY_ON_HAND)) + ') '
  11.     FROM INVENTORY i WHERE i.CONTAINER_ID =il.CONTAINER_ID
  12.     GROUP BY CONVERT(nvarchar(10),EXPIRY_DATE,103) FOR XML Path('')),2,8000)
  13.     AS ExpiryDate, CASE WHEN ISNULL(MIN(il.EXPIRY_DATE),'')='' THEN '' ELSE CONVERT(NCHAR(6),MIN(il.EXPIRY_DATE), 12) END AS ExpiryDateEAN
  14. FROM CLIENT c, SKU s, ORDER_HEADER oh INNER JOIN INVENTORY il ON oh.ORDER_ID=il.ORDER_ID AND oh.CLIENT_ID=il.CLIENT_ID
  15.     LEFT JOIN CUSTOMER_SKU cs ON il.CLIENT_ID=cs.CLIENT_ID AND il.SKU_ID=cs.SKU_ID AND oh.CUSTOMER_ID=cs.CUSTOMER_ID
  16. WHERE oh.CLIENT_ID = c.CLIENT_ID AND oh.ORDER_ID='8952282917' AND il.SKU_ID = s.SKU_ID AND il.CLIENT_ID = s.CLIENT_ID
  17. AND CASE WHEN oh.REPACK=1 AND cs.SKU_ID=il.SKU_ID
  18.         THEN LEN(il.CONTAINER_ID)
  19.         ELSE
  20.         CASE WHEN il.FROM_TAG_ID<>il.CONTAINER_ID
  21.             THEN LEN(il.CONTAINER_ID)
  22.             ELSE 0
  23.         END
  24.     END
  25.     =18
  26.     AND CASE WHEN oh.REPACK=1 THEN
  27.             CASE WHEN oh.STATUS_ID IN ('Repack', 'Complete') THEN 1 ELSE 0 END
  28.         ELSE
  29.             CASE WHEN oh.STATUS_ID IN ('Shortage', 'Picked', 'Complete') THEN 1 ELSE 0 END
  30.         END = 1
  31. GROUP BY oh.ORDER_ID, oh.CUSTOMER_ID, oh.NAME, oh.ADDRESS1, oh.ADDRESS2, oh.TOWN, oh.POSTCODE, oh.COUNTRY, oh.CONTACT_PHONE, oh.DELIVER_BY_DATE, oh.DOCUMENTATION_TEXT_2, oh.DOCUMENTATION_TEXT_3, oh.ORDER_REFERENCE, oh.TO_LOC_ID, oh.CARRIER_ID, il.CONTAINER_ID, il.LOCATION_ID, c.NAME, c.ADDRESS1, c.ADDRESS2, c.ZipCode, c.TOWN, c.COUNTRY, il.SKU_ID, s.DESCRIPTION, s.EAN_PARCEL
  32. UNION ALL
  33. SELECT oh.ORDER_ID, oh.CUSTOMER_ID, oh.NAME, oh.ADDRESS1, oh.ADDRESS2, oh.TOWN, oh.POSTCODE, oh.COUNTRY
  34. , oh.CONTACT_PHONE, oh.DELIVER_BY_DATE, oh.DOCUMENTATION_TEXT_2, oh.DOCUMENTATION_TEXT_3, oh.ORDER_REFERENCE
  35. , oh.TO_LOC_ID, oh.CARRIER_ID, il.CONTAINER_ID, il.LOCATION_ID, c.NAME AS cNAME, c.ADDRESS1 AS cADDRESS1, c.ADDRESS2 AS cADDRESS2
  36. , c.ZipCode AS cPOSTCODE, c.TOWN AS cTOWN, c.COUNTRY AS cCOUNTRY, '' AS SKU_ID, '' AS DESCRIPTION, '' AS EAN_PARCEL
  37. , SUM(il.QTY_ON_HAND) AS QTY_ON_HAND, CONVERT(nvarchar(10), MIN(il.EXPIRY_DATE), 103) AS ExpiryDate
  38. , CASE WHEN ISNULL(MIN(il.EXPIRY_DATE),'')='' THEN '' ELSE CONVERT(NCHAR(6),MIN(il.EXPIRY_DATE), 12) END AS ExpiryDateEAN
  39. FROM CLIENT c, SKU s, ORDER_HEADER oh INNER JOIN INVENTORY il ON oh.ORDER_ID=il.ORDER_ID AND oh.CLIENT_ID=il.CLIENT_ID
  40.     LEFT JOIN CUSTOMER_SKU cs ON il.CLIENT_ID=cs.CLIENT_ID AND il.SKU_ID=cs.SKU_ID AND oh.CUSTOMER_ID=cs.CUSTOMER_ID
  41. WHERE oh.CLIENT_ID = c.CLIENT_ID AND oh.ORDER_ID='8952282917' AND il.SKU_ID = s.SKU_ID AND il.CLIENT_ID = s.CLIENT_ID
  42. AND CASE WHEN oh.REPACK=1 AND cs.SKU_ID=il.SKU_ID
  43.         THEN LEN(il.CONTAINER_ID)
  44.         ELSE
  45.         CASE WHEN il.FROM_TAG_ID<>il.CONTAINER_ID
  46.             THEN LEN(il.CONTAINER_ID)
  47.             ELSE 0
  48.         END
  49.     END
  50.     <>18
  51.     AND CASE WHEN oh.REPACK=1 THEN
  52.             CASE WHEN oh.STATUS_ID IN ('Repack', 'Complete') THEN 1 ELSE 0 END
  53.         ELSE
  54.             CASE WHEN oh.STATUS_ID IN ('Shortage', 'Picked', 'Complete') THEN 1 ELSE 0 END
  55.         END = 1
  56. GROUP BY oh.ORDER_ID, oh.CUSTOMER_ID, oh.NAME, oh.ADDRESS1, oh.ADDRESS2, oh.TOWN, oh.POSTCODE, oh.COUNTRY, oh.CONTACT_PHONE, oh.DELIVER_BY_DATE, oh.DOCUMENTATION_TEXT_2, oh.DOCUMENTATION_TEXT_3, oh.ORDER_REFERENCE, oh.TO_LOC_ID, oh.CARRIER_ID, il.CONTAINER_ID, il.LOCATION_ID, c.NAME, c.ADDRESS1, c.ADDRESS2, c.ZipCode, c.TOWN, c.COUNTRY) label
  57. WHERE CASE WHEN LEN(ISNULL(SKU_ID,''))=0 THEN 14 ELSE LEN(ISNULL(EAN_PARCEL,'')) END = 14
  58. GROUP BY ORDER_ID, CUSTOMER_ID, NAME, ADDRESS1, ADDRESS2, TOWN, POSTCODE, COUNTRY
  59. , CONTACT_PHONE, DELIVER_BY_DATE, DOCUMENTATION_TEXT_2, DOCUMENTATION_TEXT_3, ORDER_REFERENCE
  60. , TO_LOC_ID, CARRIER_ID, CONTAINER_ID, LOCATION_ID, cNAME, cADDRESS1, cADDRESS2
  61. , cPOSTCODE, cTOWN, cCOUNTRY
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!