/*
Alias: WORK
LiveAnswer: FALSE

*/

SELECT DentEconomics.SupplierID, Suppliers.Company, DentEconomics.DentistID, Doctors.LastName AS Doctor, 
			DentEconomics."Date", DentEconomics.ReceiptNo, MaterialOrderItems.Price as Credit, 
			MaterialOrderItems.PriceEURO as CreditEURO, DentEconomics.Sign, 
			0 AS ReceiptCredit, 0 AS ReceiptCreditEURO,  
			MaterialDB.Description, MaterialOrderItems.QuantityDelivered, DentEconomics.EconomID
FROM ((((DentEconomics LEFT OUTER JOIN Suppliers ON DentEconomics.SupplierID = Suppliers.SupplierID)
		LEFT OUTER JOIN MaterialOrders ON DentEconomics.HistoryID = MaterialOrders.OrderID)
		LEFT OUTER JOIN MaterialOrderItems ON MaterialOrders.OrderID = MaterialOrderItems.OrderID)
		LEFT OUTER JOIN MaterialDB ON MaterialOrderItems.MaterialID = MaterialDB.MaterialID)
		LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 0 AND HistoryID > 0  
UNION
SELECT DentEconomics.SupplierID, Suppliers.Company, DentEconomics.DentistID, Doctors.LastName AS Doctor, 
			DentEconomics."Date", DentEconomics.ReceiptNo, DentEconomics.Credit, 
			DentEconomics.CreditEURO, DentEconomics.Sign,      
			DentEconomics.ReceiptCredit, DentEconomics.ReceiptCreditEURO,
			CAST('' AS CHAR(50)) AS Description, 0 AS QuantityDelivered, DentEconomics.EconomID
FROM (DentEconomics LEFT OUTER JOIN Suppliers ON DentEconomics.SupplierID = Suppliers.SupplierID)
		LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 0 AND (HistoryID IS NULL) AND (Sign = -1)   
UNION
SELECT DentEconomics.SupplierID, Suppliers.Company, DentEconomics.DentistID, Doctors.LastName AS Doctor, 
			DentEconomics."Date", DentEconomics.ReceiptNo, DentEconomics.Credit, 
			DentEconomics.CreditEURO, DentEconomics.Sign,      
			0 AS ReceiptCredit, 0 AS ReceiptCreditEURO,
			CAST('' AS CHAR(50)) AS Description, 0 AS QuantityDelivered, DentEconomics.EconomID
FROM (DentEconomics LEFT OUTER JOIN Suppliers ON DentEconomics.SupplierID = Suppliers.SupplierID)
		LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 0 AND (HistoryID IS NULL) AND (Sign = 1)
ORDER BY DentEconomics."Date", DentEconomics.EconomID

