git.roojs.org Git - Pman.Xtuple/blob - sqlreports/slowmoving.sql

   1
   2 --- concept is to find the slow moving stock.
   3
   4
   5 -- a find the average product sales for the last 3 months.
   6
   7 SELECT
   8     rkey,
   9     rkid,
  10     rvalue,
  11     cstock,
  12     avsales
  13 FROM
  14     (SELECT
  15
  16             item_descrip1   AS rkey,
  17             item_number AS rkid,
  18
  19             --(SELECT
  20             --            COALESCE(SUM(loccurbal_ending),0)
  21             --        FROM
  22             --            loccurbal
  23             --        LEFT JOIN
  24             --            location
  25             --        ON
  26             --            location_id = loccurbal_location_id
  27             --        WHERE
  28             --            loccurbal_itemsite_id = itemsite_id
  29             --            and
  30             --            location_name NOT LIKE 'Import Goods%'
  31             --)
  32               (SELECT
  33                          COALESCE(SUM(itemloc_qty),0)
  34                      FROM
  35                          itemloc
  36                      LEFT JOIN
  37                          location
  38                      ON
  39                          location_id = itemloc_location_id
  40
  41                      WHERE
  42                          itemloc_itemsite_id = itemsite_id
  43                          and
  44                          location_name NOT LIKE 'Import Goods%'
  45                          AND
  46                         '' =  charass_getvalue('C', location_cust_id,'INTERNALCOMPANY')
  47              )
  48
  49
  50                 /
  51             (
  52                 CASE WHEN ( sum(invcitem_billed) / 3  ) > 0 THEN
  53                     ( sum(invcitem_billed) / 3  )
  54                 ELSE
  55                     0.01
  56                 END
  57             )
  58                 as rvalue,
  59             (SELECT
  60                          COALESCE(SUM(itemloc_qty),0)
  61                      FROM
  62                          itemloc
  63                      LEFT JOIN
  64                          location
  65                      ON
  66                          location_id = itemloc_location_id
  67                      WHERE
  68                          itemloc_itemsite_id = itemsite_id
  69                          and
  70                          location_name NOT LIKE 'Import Goods%'
  71                             AND
  72                         '' =  charass_getvalue('C', location_cust_id,'INTERNALCOMPANY')
  73              ) as cstock,
  74
  75              (
  76                 CASE WHEN ( sum(invcitem_billed) / 3  ) > 0 THEN
  77                     ( sum(invcitem_billed) / 3  )
  78                 ELSE
  79                     0.01
  80                 END
  81             )
  82                 as avsales
  83             FROM
  84
  85                 invcitem
  86
  87
  88             LEFT JOIN
  89                 invchead
  90             ON
  91                 invchead_id = invcitem_invchead_id
  92
  93              LEFT JOIN
  94                 item
  95             ON
  96                 item_id = invcitem_item_id
  97
  98             LEFT JOIN
  99                 itemsite
 100             ON
 101                 itemsite_item_id = invcitem_item_id
 102
 103
 104
 105
 106             WHERE
 107
 108                 invchead_invcdate >= NOW() - INTERVAL '3 MONTHS'
 109                 AND
 110                 invchead_invcdate  <= NOW()
 111                 AND
 112                 invchead_void = false
 113
 114             GROUP BY
 115                 rkey, rkid ,itemsite_id
 116
 117            ORDER BY
 118                 rvalue DESC
 119     ) AS data
 120
 121 WHERE
 122     rvalue >=0
 123     AND
 124     cstock > 0
 125 LIMIT 30
 126 ;