CREATE view [dbo].[v_SalesView]
As
SELECT	S.surfaceItemId,
		S.dateOfService, 
		S.name, 
		S.surname, 
		S.itemType,  
		S.invoiceNo,
		S.receiptNo, 
		S.reference,
		CASE S.itemType 
			WHEN 'PM' THEN 'RCT' + CAST(S.receiptNo AS VARCHAR(20)) 
			WHEN 'QT' THEN 'QTE' + CAST(S.invoiceNo AS VARCHAR(20)) 
			ELSE 'INV' + CAST(S.invoiceNo AS VARCHAR(20)) 
			END [Number],
		CASE S.itemType 
			WHEN 'QT' THEN 'Quote # QTE'+ CONVERT(varchar(50),S.invoiceNo)
			WHEN 'TI' THEN 'Invoice # INV'+ CONVERT(varchar(50),S.invoiceNo)
			WHEN 'PM' THEN 'Payment on Invoice # INV'+ CONVERT(varchar(50),T.invoiceNo) 
			WHEN 'CN' THEN 'Credit Note # CN'+ CONVERT(varchar(50),S.receiptNo)
			END Display,
		CASE WHEN SUM(S.amount) < 0 THEN (SUM(S.amount * -1)) ELSE 0 END [Received],
		CASE WHEN SUM(S.amount) > 0 AND S.itemType <> 'QT' THEN SUM(S.amount) ELSE 0 END [Invoiced],
		CASE WHEN SUM(S.amount) > 0 AND S.itemType = 'QT' THEN SUM(S.amount) ELSE 0 END [Quoted],
		SUM(S.allocated) [allocated],
		CASE WHEN S.itemType = 'TI' THEN SUM (S.amount - S.allocated) ELSE 0 END AS [Due],
		SUM(CASE WHEN SUM(S.amount) > 0 AND S.itemType <> 'QT' THEN SUM(S.amount) ELSE 0 END 
			- CASE WHEN SUM(S.amount) < 0 THEN (SUM(S.amount * -1)) ELSE 0 END) 
			OVER (ORDER BY S.dateOfService DESC, CASE WHEN S.invoiceNo > 0 THEN CAST(S.invoiceNo AS VARCHAR(20)) ELSE S.itemType END ROWS UNBOUNDED PRECEDING) RunningBalance
FROM	pal_Sales S
		LEFT JOIN pal_Sales T
			ON T.recId IN (SELECT (CASE WHEN SUBSTRING(splitdata,0,CHARINDEX(':',splitdata))='' THEN splitdata ELSE SUBSTRING(splitdata,0,CHARINDEX(':',splitdata)) END) FROM dbo.SplitString(CASE WHEN S.itemType = 'PM' THEN S.allocatedReference ELSE '' END,','))
GROUP BY S.surfaceItemId, S.name, S.surname, S.itemType, S.dateOfService, S.invoiceNo, S.receiptNo, S.reference,T.invoiceNo


