USE FinalTenMan;

/* #8 Query showing leases expiring with 12 Months */
/* I used this one with the last month of our project */
/* to show at least one that would expire */
SELECT *
FROM Tenant
WHERE LeaseEnd < '2005-03-31' + INTERVAL 12 MONTH;

/* #8 Query where you can do it from the current date */
SELECT *
FROM Tenant
WHERE LeaseEnd < DATE_SUB(CURDATE(),INTERVAL 12 MONTH);

/* #9 Query showing current percentage rent */
/* I did this one in Feb 2005 but you can */
/* either change the date each time or do */
/* the current date formula Note: DESC does */
/* not work for this version of mysql */
SELECT *
FROM AccountsReceivable
WHERE Type = 'P'
AND MONTH(ARDate) = 02 
AND YEAR(ARDate) = 2005 
ORDER BY Amount DESC;

/* #9 the current date version*/
SELECT *
FROM AccountsReceivable
WHERE Type = 'P'
AND YEAR(ARDate) = YEAR(CURDATE())
AND MONTH(ARDate) = MONTH(CURDATE())
ORDER BY Amount DESC;

/* #10 unpaid charges during the current month */
/* This one uses Dec 2004 as current month */
SELECT AccountsReceivable.TenantNum, TName, SUM(Amount)
FROM AccountsReceivable, Tenant
WHERE AccountsReceivable.TenantNum = Tenant.TenantNum 
AND YEAR(ARDate) = 2004
AND MONTH(ARDate) = 12 
GROUP BY AccountsReceivable.TenantNum
HAVING SUM(Amount) > 0;

/* #10 unpaid current charges using the current date */
SELECT AccountsReceivable.TenantNum, TName, SUM(Amount)
FROM AccountsReceivable, Tenant
WHERE AccountsReceivable.TenantNum = Tenant.TenantNum 
AND YEAR(ARDate) = YEAR(CURDATE())
AND MONTH(ARDate) = MONTH(CURDATE())
GROUP BY AccountsReceivable.TenantNum
HAVING SUM(Amount) > 0;

/* #11 unpaid charges for months previous current month */
SELECT AccountsReceivable.TenantNum, TName, SUM(Amount)
FROM AccountsReceivable, Tenant
WHERE AccountsReceivable.TenantNum = Tenant.TenantNum 
AND ARDate < '2005-03-31'
GROUP BY AccountsReceivable.TenantNum
HAVING SUM(Amount) > 0
ORDER BY Amount DESC;

/* #11 current date method */
SELECT AccountsReceivable.TenantNum, TName, SUM(Amount)
FROM AccountsReceivable, Tenant
WHERE AccountsReceivable.TenantNum = Tenant.TenantNum 
AND ARDate < CURDATE()
GROUP BY AccountsReceivable.TenantNum
HAVING SUM(Amount) > 0
ORDER BY Amount DESC;

/* #12 show the number days taken to pay an individual charge */
/* Not Quite right not sure how to get this one */
CREATE TEMPORARY TABLE tempDays1
SELECT TenantNum, Min(ARDate) AS minDate
FROM ARHistory
GROUP BY TenantNum, InvoiceNum;

CREATE TEMPORARY TABLE tempDays2
SELECT TenantNum, MAX(ARDate) AS maxDate
FROM ARHistory
GROUP BY TenantNum, InvoiceNum;

SELECT tempDays1.TenantNum, 
DATEDIFF(tempDays2.maxDate, tempDays1.minDate) AS NumberOfDays
FROM tempDays1, tempDays2
WHERE tempDays1.TenantNum = tempDays2.TenantNum
GROUP BY tempDays1.TenantNum;