Lesson Progress
0% Complete

Once you have planned everything out, writing the SQL code is quite simple. See the sample code below:

SELECT
		 i."Item Name" 'Item Name',
		 i."SKU" 'SKU',
		 concat('https://books.zoho.com/app/722265549#/inventory/items/', i."Item ID") 'Link to Item',
		 0 'Sold Last 2 Months',
		 0 'Committed Stock',
		 s."Stock In Hand" 'In Hand',
		 'On Hand' 'Category'
FROM  "Stock In Flow Table (Zoho Books)" s
JOIN "Items (Zoho Books)" i ON i."Item ID"  = s."Product ID"  
WHERE	 s."Stock In Hand"  > 0
UNION ALL
 SELECT
		 i."Item Name" 'Item Name',
		 i."SKU" 'SKU',
		 concat('https://books.zoho.com/app/722265549#/inventory/items/', i."Item ID") 'Link to Item',
		 sum(if(is_last_nday(v."Created Time", 60)  LIKE 1, v.Quantity, 0)) 'Sold Last 2 Months',
		 0 'Committed Stock',
		 0 'Stock in Hand',
		 'Sales' 'Category'
FROM  "Invoice Items (Zoho Books)" v
JOIN "Items (Zoho Books)" i ON i."Item ID"  = v."Product ID"  
WHERE	 is_last_nday(v."Created Time", 60)  LIKE 1
GROUP BY i."Item Name",
	 i."SKU",
	  i."Item ID" 
UNION ALL
 SELECT
		 i."Item Name" 'Item Name',
		 i."SKU" 'SKU',
		 concat('https://books.zoho.com/app/722265549#/inventory/items/', i."Item ID") 'Link to Item',
		 0 'Sold Last 2 Months',
		 sum(s.Quantity) 'Committed Stock',
		 0 'Stock in Hand',
		 'Committed' 'Category'
FROM  "Sales Order Items (Zoho Books)" s
JOIN "Sales Orders (Zoho Books)" so ON so."Sales order ID"  = s."Sales order ID" 
JOIN "Items (Zoho Books)" i ON i."Item ID"  = s."Product ID"  
WHERE	 so."Status"  NOT LIKE 'invoiced'
GROUP BY i."Item Name",
	 i."SKU",
	  i."Item ID"