CREATE VIEW v_SalesView
AS
SELECT	dateOfService, name, surname, itemType,  
		invoiceNo, receiptNo, 
		CASE WHEN itemType = 'PM' THEN 'RCT' + CAST(receiptNo AS VARCHAR(20)) 
			WHEN itemType = 'QT' THEN 'QTE' + CAST(invoiceNo AS VARCHAR(20)) 
			ELSE 'INV' + CAST(invoiceNo AS VARCHAR(20)) END [number],
		CASE WHEN SUM(amount) < 0 THEN (SUM(amount * -1)) ELSE 0 END [Received],
		CASE WHEN SUM(amount) > 0 AND itemType = 'TI' THEN SUM(amount) ELSE 0 END [Invoiced],
		CASE WHEN SUM(amount) > 0 AND itemType = 'QT' THEN SUM(amount) ELSE 0 END [Quoted],
		SUM(allocated) [allocated],
		CASE WHEN  itemType = 'TI' THEN SUM (amount - allocated) ELSE 0 END AS [Due],
		CASE WHEN itemType = 'PM' THEN 'RCT' + CAST(receiptNo AS VARCHAR(20)) 
			WHEN itemType = 'QT' THEN 'QTE' + CAST(invoiceNo AS VARCHAR(20)) 
			ELSE 'INV' + CAST(invoiceNo AS VARCHAR(20)) END + ' - '+name+' '+surname +' (R' +
		CASE WHEN  itemType = 'TI' THEN CAST(SUM(amount - allocated) as varchar) ELSE CAST(0 as VARCHAR) END +')' AS details
FROM pal_Sales
GROUP BY name, surname, itemType, dateOfService, invoiceNo, receiptNo
