tmp.sql

SELECT
stock.lot_number AS "Lot Number",
p.cached_name AS "Product Name",
p.code,
count(stock.piece_id) AS "Inventory in Azure Warehouse",
wp.cached_purchased_but_not_arrived_stock_count AS "Inventory in Transit",
CASE
WHEN wp.breakdown_level_id = 1 --When piece is bulk
Then wp.cached_stock_committed_count-(SELECT
piece.cached_stock_committed_count / breakdown.child_quantity
FROM warehouse_pieces_piecebreakdown AS bd
JOIN warehouse_pieces_piece AS piece ON piece.id = bd.child_piece_id
WHERE bd.child_piece_id = breakdown.child_piece_id) 
ELSE wp.cached_stock_committed_count END AS "Product Committed on Orders",
to_char(wp.cached_predicted_daily_sales_rate, '9999d99') AS "Avg Sold per Day", 

CASE 
WHEN sales.custom_percent_off > 0 THEN 'Y'
WHEN sales.custom_amount_off > 0 THEN 'Y'
WHEN vpl.percent_off > 0 THEN 'Y'
WHEN vpl.amount_off > 0 THEN 'Y'
ELSE 'N'
END AS "On Sale",
CASE 
WHEN COALESCE(sales.custom_percent_off, vpl.percent_off, 0) > 0 THEN
(COALESCE(sales.custom_percent_off, 0) + COALESCE(vpl.percent_off, 0))
WHEN COALESCE (sales.custom_amount_off, vpl.amount_off, 0) > 0 THEN
((COALESCE(sales.custom_amount_off, 0) + COALESCE(vpl.amount_off, 0)) 
- p.cached_retail_price) / p.cached_retail_price * 100
ELSE 0
END AS "Percent Off",

CASE (
SELECT status
FROM products_productstatus
WHERE 
product_id = p.id AND (end_date > now() OR end_date IS NULL)
ORDER BY id 
DESC LIMIT 1
) 
WHEN 1 THEN 'Featured'
WHEN 2 THEN 'New'
WHEN 3 THEN 'Discontinued'
WHEN 4 THEN 'Soon to be discontinued'
WHEN 5 THEN 'In House'
WHEN 6 THEN 'Out short term'
WHEN 7 THEN 'Seasonally Out'
WHEN 8 THEN 'Out long term'
WHEN 9 THEN 'New on sale'
ELSE 'Active'
END AS "Status",

stock.expiration_date::date AS "Expiration Date",
CASE WHEN(
((SUM(count(stock.piece_id)) OVER (ORDER BY stock.expiration_date) / 
((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
wpiece.cached_predicted_daily_sales_rate
FROM warehouse_pieces_piece AS wpiece
WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
ELSE 0 END) + wp.cached_predicted_daily_sales_rate)) --days of stock
- ((stock.expiration_date::date - now()::date)-10)) --days tell expired including shipping time to customer
* ((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
wpiece.cached_predicted_daily_sales_rate
FROM warehouse_pieces_piece AS wpiece
WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
ELSE 0 END) + wp.cached_predicted_daily_sales_rate)) > 0
THEN(
((SUM(count(stock.piece_id)) OVER (ORDER BY stock.expiration_date) / 
((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
wpiece.cached_predicted_daily_sales_rate
FROM warehouse_pieces_piece AS wpiece
WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
ELSE 0 END) + wp.cached_predicted_daily_sales_rate)) --days of stock
- ((stock.expiration_date::date - now()::date)-10)) --days tell expired including shipping time to customer
* ((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
wpiece.cached_predicted_daily_sales_rate
FROM warehouse_pieces_piece AS wpiece
WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
ELSE 0 END) + wp.cached_predicted_daily_sales_rate))::int
ELSE NULL END AS "Warning! Product Likely to Expire"
FROM
products_product AS p
LEFT JOIN warehouse_pieces_piece AS wp ON wp.id = p.piece_id
LEFT JOIN warehouse_pieces_piecebreakdown AS breakdown
ON breakdown.child_piece_id = wp.id OR
breakdown.parent_piece_id = wp.id
LEFT JOIN vendor_vendorpiece AS vp 
ON vp.piece_id = breakdown.child_piece_id OR
vp.piece_id = breakdown.parent_piece_id OR
vp.piece_id = wp.id
LEFT JOIN inventory_stock AS stock ON stock.piece_id = wp.id
LEFT JOIN sales_sheets_salessheetline AS sales
ON sales.product_id = p.id AND 
sales.sales_sheet_id = (-- Current price sheet
SELECT 
id
FROM sales_sheets_salessheet
WHERE end_date > now() AND 
start_date < now() AND
price_level = 1
AND id = sales.sales_sheet_id 
group by 1, start_date
order by start_date asc
LIMIT 1
)
LEFT JOIN vendor_vendorpromotionline AS vpl ON sales.vendor_promotion_line_id = vpl.id

WHERE vp.vendor_id = 259
--AND p.code = 'CH134'
AND vp.is_active = 'TRUE'
AND stock.status IN ( 'ARRIVED', 'REAL' ) AND
stock.box_id IS NULL AND
stock.pallet_id IS NULL AND
stock.picker_id IS NULL
AND
COALESCE((
SELECT status
FROM products_productstatus
WHERE 
product_id = p.id AND (end_date > now() OR end_date IS NULL)
ORDER BY id 
DESC LIMIT 1
), 0) IN (0, 1, 2, 4, 6, 7, 8, 9)
GROUP BY p.id, wp.id, vp.id, breakdown.id, sales.id, vpl.id, stock.expiration_date --, stock.lot_number
ORDER BY p.code

SELECT

p.code AS "Product Code",
p.cached_name AS "Product Name",
count(stock.piece_id) AS "Inventory in Azure Warehouse",
wp.cached_purchased_but_not_arrived_stock_count AS "Inventory in Transit",

CASE WHEN wp.breakdown_level_id = 1 --When piece is bulk
Then wp.cached_stock_committed_count-(SELECT
piece.cached_stock_committed_count / breakdown.child_quantity
FROM warehouse_pieces_piecebreakdown AS bd
JOIN warehouse_pieces_piece AS piece ON piece.id = bd.child_piece_id
WHERE bd.child_piece_id = breakdown.child_piece_id)
ELSE wp.cached_stock_committed_count END AS "Product Committed on Orders",

to_char(wp.cached_predicted_daily_sales_rate, '9999d99') AS "Avg Sold per Day",

   CASE 
     WHEN sales.custom_percent_off > 0 THEN 'Y'
     WHEN sales.custom_amount_off > 0 THEN 'Y'
     WHEN vpl.percent_off > 0 THEN 'Y'
     WHEN vpl.amount_off > 0 THEN 'Y'
   ELSE 'N'
   END AS "On Sale",
   CASE 
     WHEN COALESCE(sales.custom_percent_off, vpl.percent_off, 0) > 0 THEN
          (COALESCE(sales.custom_percent_off, 0) + COALESCE(vpl.percent_off, 0))
     WHEN COALESCE (sales.custom_amount_off, vpl.amount_off, 0) > 0 THEN
          ((COALESCE(sales.custom_amount_off, 0) + COALESCE(vpl.amount_off, 0)) 
            - p.cached_retail_price) / p.cached_retail_price * 100
     ELSE 0
   END AS "Percent Off",

CASE (
SELECT status
FROM products_productstatus
WHERE
product_id = p.id AND (end_date > now() OR end_date IS NULL)
ORDER BY id
DESC LIMIT 1
)
WHEN 1 THEN 'Featured'
WHEN 2 THEN 'New'
WHEN 3 THEN 'Discontinued'
WHEN 4 THEN 'Soon to be discontinued'
WHEN 5 THEN 'In House'
WHEN 6 THEN 'Out short term'
WHEN 7 THEN 'Seasonally Out'
WHEN 8 THEN 'Out long term'
WHEN 9 THEN 'New on sale'
ELSE 'Active'
END AS "Status",

CASE WHEN(
((count(stock.piece_id) /
((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
wpiece.cached_predicted_daily_sales_rate
FROM warehouse_pieces_piece AS wpiece
WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
ELSE 0 END) + wp.cached_predicted_daily_sales_rate)) --days of stock

• ((stock.expiration_date::date - now()::date)-10)) --days tell expired including shipping time to customer
• ((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
  wpiece.cached_predicted_daily_sales_rate
  FROM warehouse_pieces_piece AS wpiece
  WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
  ELSE 0 END) + wp.cached_predicted_daily_sales_rate)) > 0
  THEN(
  ((count(stock.piece_id) /
  ((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
  wpiece.cached_predicted_daily_sales_rate
  FROM warehouse_pieces_piece AS wpiece
  WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
  ELSE 0 END) + wp.cached_predicted_daily_sales_rate)) --days of stock
• ((stock.expiration_date::date - now()::date)-10)) --days tell expired including shipping time to customer
• ((CASE WHEN COUNT(stock.piece_id) = COUNT(breakdown.parent_piece_id) THEN (SELECT --This piece of code includes childpieces in the predicted daily sales
  wpiece.cached_predicted_daily_sales_rate
  FROM warehouse_pieces_piece AS wpiece
  WHERE breakdown.child_piece_id = wpiece.id)/breakdown.child_quantity
  ELSE 0 END) + wp.cached_predicted_daily_sales_rate))::int
  ELSE NULL END AS "Warning! Product Likely to Expire"

FROM
products_product AS p
LEFT JOIN warehouse_pieces_piece AS wp ON wp.id = p.piece_id

LEFT JOIN warehouse_pieces_piecebreakdown AS breakdown
ON breakdown.child_piece_id = wp.id OR
breakdown.parent_piece_id = wp.id
LEFT JOIN vendor_vendorpiece AS vp
ON vp.piece_id = breakdown.child_piece_id OR
vp.piece_id = breakdown.parent_piece_id OR
vp.piece_id = wp.id
LEFT JOIN inventory_stock AS stock ON stock.piece_id = wp.id

LEFT JOIN sales_sheets_salessheetline AS sales
ON sales.product_id = p.id AND
sales.sales_sheet_id = (-- Current price sheet
SELECT
id
FROM sales_sheets_salessheet
WHERE end_date > now() AND
start_date < now() AND
price_level = 1
AND id = sales.sales_sheet_id
group by 1, start_date
order by start_date asc
LIMIT 1
)
LEFT JOIN vendor_vendorpromotionline AS vpl ON sales.vendor_promotion_line_id = vpl.id

WHERE vp.vendor_id = 259
--AND p.code = 'CH134'
AND vp.is_active = 'TRUE'
AND stock.status IN ( 'ARRIVED', 'REAL' ) AND
stock.box_id IS NULL AND
stock.pallet_id IS NULL AND
stock.picker_id IS NULL
AND
COALESCE((
SELECT status
FROM products_productstatus
WHERE
product_id = p.id AND (end_date > now() OR end_date IS NULL)
ORDER BY id
DESC LIMIT 1
), 0) IN (0, 1, 2, 4, 6, 7, 8, 9)

GROUP BY p.id, wp.id, vp.id, breakdown.id, sales.id, vpl.id, stock.expiration_date
ORDER BY 1 asc

Thats the closest I've come