/*
Alias: WORK
LiveAnswer: FALSE

*/

SELECT EXTRACT(MONTH FROM DentEconomics."Date") AS DateMonth, 
		EXTRACT(YEAR FROM DentEconomics."Date") AS DateYear, DentEconomics."Date",
		DentEconomics.DentistID, (Doctors.LastName + ' ' + Doctors.FirstName) AS FullDocName,

		0 AS Credit, 0 AS CreditEURO, DentEconomics.Credit as Debit, DentEconomics.CreditEURO as DebitEURO,
		0 AS ReceiptCredit, 0 AS ReceiptCreditEURO, DentEconomics.ReceiptCredit as ReceiptDebit, DentEconomics.ReceiptCreditEURO as ReceiptDebitEURO, 

		EXTRACT(MONTH FROM DentEconomics."Date") + EXTRACT(YEAR FROM DentEconomics."Date") * 12 AS DateMonthYear,
		Sign, -TransactionType-1 AS Type, EconomID, SourceName, Description, ReceiptNo
FROM DentEconomics LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE Sign = -1
UNION
SELECT EXTRACT(MONTH FROM DentEconomics."Date") AS DateMonth, 
		EXTRACT(YEAR FROM DentEconomics."Date") AS DateYear, DentEconomics."Date",
		DentEconomics.DentistID, (Doctors.LastName + ' ' + Doctors.FirstName) AS FullDocName,

		DentEconomics.Credit as Credit, DentEconomics.CreditEuro as CreditEURO,0 as Debit, 0 as DebitEURO,
		DentEconomics.ReceiptCredit  as ReceiptCredit, DentEconomics.ReceiptCreditEuro  as ReceiptCreditEURO,0 as ReceiptDebit, 0  as ReceiptDebitEURO, 

		EXTRACT(MONTH FROM DentEconomics."Date") + EXTRACT(YEAR FROM DentEconomics."Date") * 12 AS DateMonthYear,
		Sign, 2 AS Type, EconomID, SourceName, Description, ReceiptNo
FROM DentEconomics LEFT OUTER JOIN Doctors ON DentEconomics.DentistID = Doctors.DocID
WHERE TransactionType = 2  AND Sign = 1 
UNION
SELECT EXTRACT(MONTH FROM PatEconomics."Date") AS DateMonth, 
		EXTRACT(YEAR FROM PatEconomics."Date") AS DateYear, PatEconomics."Date",
		PatEconomics.DentistID, (Doctors.LastName + ' ' + Doctors.FirstName) AS FullDocName,

		PatEconomics.Credit, PatEconomics.CreditEURO, 0 as Debit, 0 as DebitEURO, 
		PatEconomics.ReceiptCredit  as ReceiptCredit, PatEconomics.ReceiptCreditEURO as ReceiptCreditEURO,0 as ReceiptDebit, 0 as ReceiptDebitEURO, 

		EXTRACT(MONTH FROM PatEconomics."Date") + EXTRACT(YEAR FROM PatEconomics."Date") * 12 AS DateMonthYear,
		-Sign AS Sign, 1 AS Type, EconomID, SourceName, Description, ReceiptNo
FROM PatEconomics LEFT OUTER JOIN Doctors ON PatEconomics.DentistID = Doctors.DocID
WHERE Sign = -1
