daily pastebin goal
32%
SHARE
TWEET

Untitled

a guest Nov 13th, 2017 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT  ta.ALIAS AS attribute
  2.       , ta.LABEL
  3. FROM ITEM
  4.   INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
  5.     AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
  6.   INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
  7.     AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
  8.   INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
  9.   INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
  10.   INNER JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
  11.   INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID IN (33)
  12. WHERE item.HID.GetLevel() = 1
  13.   AND conItemLink.CONTAINER_ID = 182258
  14. GROUP BY IIF(propLink.PROPERTY_ID=22, propName.ALIAS, ta.ALIAS), ta.LABEL
  15.  
  16. SELECT [other], [BagID], [ItemID]
  17.       , SUM( CAST(ISNULL(quantity, 1) AS INT)) AS quantity
  18. FROM
  19.     (
  20.         SELECT  base.*
  21.           FROM
  22.             (
  23.                 SELECT filter.attribute
  24.                        , filter.value
  25.                        , filter.INSTANCE_ID
  26.                 FROM
  27.                     (                
  28.                       SELECT IIF(propLink.PROPERTY_ID=22, propName.ALIAS, ta.NAME) AS attribute
  29.                             , tiav.value
  30.                             , IIF((COUNT(*) OVER (PARTITION BY tiav.INSTANCE_ID) > 1), 2, 1) AS count
  31.                             , propLink.PROPERTY_ID
  32.                             , tiav.INSTANCE_ID
  33.                       FROM ITEM
  34.                         INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
  35.                           AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
  36.                         INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
  37.                           AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
  38.                         INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
  39.                         INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
  40.                         INNER JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
  41.                         INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID IN (33, 22)
  42.                         INNER JOIN TEMPLATE_ATTRIBUTE_PROP_DEF propName ON propLink.PROPERTY_ID = propName.ID
  43.                         INNER JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = item.TEMPLATE_INSTANCE_ID
  44.                       WHERE item.HID.GetLevel() = 1
  45.                         AND conItemLink.CONTAINER_ID = 182258
  46.                     ) filter
  47.                 WHERE NOT (filter.PROPERTY_ID = 22 AND filter.count = 1)
  48.                 UNION ALL
  49.                 SELECT DISTINCT IIF(propLink.PROPERTY_ID=22, propName.ALIAS, 'other') AS attribute
  50.                       , IIF(propLink.PROPERTY_ID=22, ISNULL(tiav.value, '1'), 'other') AS value
  51.                       , item.TEMPLATE_INSTANCE_ID AS INSTANCE_ID
  52.                 FROM ITEM
  53.                     INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
  54.                       AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
  55.                     INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
  56.                       AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
  57.                     INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
  58.                     INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
  59.                     LEFT JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
  60.                     LEFT JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID = 22
  61.                     LEFT JOIN TEMPLATE_ATTRIBUTE_PROP_DEF propName ON propLink.PROPERTY_ID = propName.ID
  62.                     LEFT JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = item.TEMPLATE_INSTANCE_ID AND propLink.PROPERTY_ID = 22
  63.  
  64.                 WHERE item.HID.GetLevel() = 1
  65.                   AND conItemLink.CONTAINER_ID = 182258
  66.                   AND (
  67.                     SELECT SUM(IIF(propLink.PROPERTY_ID = 33, 1, 0))
  68.                       FROM TEMPLATE_ATTRIBUTE ta
  69.                         INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID
  70.                         INNER JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = ti.ID AND tiav.value IS NOT NULL
  71.                     WHERE ti.ID = item.TEMPLATE_INSTANCE_ID
  72.                       AND ta.TEMPLATE_ID = temp.ID
  73.                   ) = 0
  74.  
  75.             ) base
  76.     ) pivotBase
  77. PIVOT (
  78.         MAX(pivotBase.value)
  79.         FOR pivotBase.attribute IN ([other], [BagID], [ItemID], [quantity])
  80.       ) main
  81. GROUP BY [other], [BagID], [ItemID]
RAW Paste Data
Top