/*------------------------------------------

---------------------------------------
See:  Apartments.Midterm.SQLExpress.sql
for the sample Midterm project
---------------------------------------------
-->  I have given results for only *most* <--
-->  (but not all) of the queries.        <--
---------------------------------------------

Note:  The keyword "GO" separates
statements into BATCHES that 
execute independently of one another.


-------------------------------------------*/

/*  Highlight and execute these commands 
    one "section" at a time.  */

-----------------------------------------------


use apartments
go

--
-- 1. What is the rent history for a given apartment?
--
-- (For example, for building 1923 apartment C?)

SELECT begindate, amount 
FROM renthistory
WHERE buildingID = 1923 and apartmentID = 'C'
ORDER by begindate desc;

/* -- results:

begindate  amount
---------  ------
2005-07    1050
2004-07    1000
2003-07	   960
2002-07	   930
2001-07	   900
2000-07	   875
1998-07	   850
*/


--
-- 2. What is the current rent by apartment?
--
-- First, create the view RentByApartment

CREATE VIEW RentByApartment
AS
select a.buildingID, a.apartmentID, h.amount as currentRent
from apartment a, renthistory h
where a.apartmentID <> 'ALL'
and a.buildingID = h.buildingID
and a.apartmentID = h.apartmentID
and beginDate = (select max(beginDate) from renthistory
		where apartmentID = h.apartmentID
		and buildingID = h.buildingID)
go

-- Then, answer the question:

SELECT * FROM RentByApartment
ORDER BY buildingID, apartmentID

/* -- results

buildingID apartmentID currentRent
---------- ----------- -----------
1923	        A  	1525
1923	        B  	1050
1923	        C  	1050
1923	        D  	1225
2101	        A  	1500
2101	        B  	1170
2101	        C  	1090
*/

-- 
-- 3. What is the total rent by building?
--
-- First, create a view, RentByBuilding

CREATE VIEW RentByBuilding
AS
select buildingID, sum(currentRent) as TotalRent
from RentByApartment
group by buildingID
go

-- Next, answer the question:

Select * from RentByBuilding

/* -- results

buildingID apartmentID
---------- -----------
1923	      4850
2101	      3750

*/


-- 
-- 4. What is the latest incidence of given maintenance type, for all apartments?
--

SELECT DISTINCT maintTypeID from maintenance; -- determine the maintenance types

-- Pick maintenance type 'newCarpet' 

SELECT buildingID, apartmentID, max(datePerformed) as LatestDate
FROM maintenance
WHERE maintTypeID = 'newCarpet' 
GROUP BY buildingID, apartmentID
ORDER BY buildingID, apartmentID;

/* -- results

buildingID  apartmentID LatestDate
----------  ----------- ----------
1923		A  	1998-05-01
1923		B  	1993-02-01
1923		C  	1998-01-01
1923		D  	1999-11-01
2101		A  	1991-10-01
2101		B  	2002-07-01
2101		C  	2003-11-06

*/

-- 
-- 5. What is the *cost* of the latest incidence 
--    of a given maintenance type.
--
-- Note:  The reason it must be done this way is that if the amount column
--        were included in the SELECT statement determining the latest date, 
--        that column would have to be included in the GROUP BY clause.

SELECT m.apartmentID, m.buildingID, m.datePerformed, m.amount
FROM
   (SELECT buildingID, apartmentID, max(datePerformed) as LatestDate
   FROM maintenance
   WHERE maintTypeID = 'paint'
   GROUP BY buildingID, apartmentID) AS t,
maintenance m
WHERE t.apartmentID = m.apartmentID
AND t.buildingID = m.buildingID
AND t.LatestDate = m.datePerformed
AND m.apartmentID <> 'ALL'
AND m.maintTypeID = 'paint'
ORDER BY m.buildingID, m.apartmentID

*/ -- results

apartmentID buildingID  LatestDate      amount
----------- ----------  ----------      ------
A  		1923	2001-11-01	1559
B  		1923	2001-04-01	500
C  		1923	1998-01-01	1485
D  		1923	2001-10-01	895
A  		2101	1992-06-01	500
B  		2101	2003-04-01	1577

*/

-- 
-- 6. List the current tenants by apartment.
--
-- First, define the view TenantsByApartment

CREATE VIEW TenantsByApartment
AS
select buildingID, apartmentID, lastName, firstName
from apartmenttenant at inner join tenant t
on at.tenantID = t.tenantID
where at.isCurrent = 1
go

-- Next, answer the question:

SELECT * FROM TenantsByApartment
ORDER BY buildingID, apartmentID

-- 
-- 7. Create a tenant mailing list.
--

SELECT (firstName + ' ' + lastName) as Tenant,
  (streetAddress + ' Apt. ' + a.apartmentID) as Line2,
  (city + ', ' + state + '  ' + CAST(zip as char(5))) as Line3
FROM building b INNER JOIN apartment a
  ON b.buildingID = a.buildingID
INNER JOIN apartmenttenant at 
  ON a.apartmentID = at.apartmentID and a.buildingID = at.buildingID
INNER JOIN tenant t
  ON at.tenantID = t.tenantID
WHERE isCurrent = 1;

/* -- resutls

Tenant                	Line2                        	Line3
----------------      	--------------------------   	------------------------
Cheryl Bodmer		1923 Belmont Lane Apt. A  	Redondo Beach, CA  90278
John Bodmer		1923 Belmont Lane Apt. A  	Redondo Beach, CA  90278
Beverly Lee		2101 Dufour Ave. Apt. A  	Redondo Beach, CA  90278
Scott Matlock		1923 Belmont Lane Apt. B  	Redondo Beach, CA  90278
Rose Reese		2101 Dufour Ave. Apt. B  	Redondo Beach, CA  90278
Geri Dobbs		1923 Belmont Lane Apt. C  	Redondo Beach, CA  90278
Chris Wilson		1923 Belmont Lane Apt. C  	Redondo Beach, CA  90278
Julie Bassine		2101 Dufour Ave. Apt. C  	Redondo Beach, CA  90278
Rosa Hernandez		1923 Belmont Lane Apt. D  	Redondo Beach, CA  90278
Michelle Torres		1923 Belmont Lane Apt. D  	Redondo Beach, CA  90278

*/


-- 
-- 8. Queries for when an apartment gets a new tenant:
--
-- (In "real life", this would be done by a stored procedure
--  which could be called by a front-end application.
--  The procedure would take parameters as input and perform 
--  all these queries.)

UPDATE apartmenttenant
   SET isCurrent = 0, dateMovedOut = '2005-09-01'
   WHERE buildingID = 2101 and apartmentID = 'A'
   AND tenantID = 'LeeB'

INSERT tenant
   values('ManzA', NULL, 'Manz', 'Allison', NULL, NULL, NULL, NULL)

INSERT apartmenttenant
   values('A', 2101, 'ManzA', '2005-09-08', NULL, 1)

INSERT renthistory
   values('A', 2101, '2004-12-08', 1600)


-- 
-- 9. What is the average amount of money per year spent on
--    a particular major maintenance type?  e.g. new carpet
--
-- Note:  Because the datePerformed column is CHAR(10), I had to
--        CAST it as datetime data type to show the date manipulation.
--

SELECT DATEPART(year, CAST(datePerformed as datetime)) as Year,
       sum(amount) as 'Total Amount'
FROM maintenance
WHERE maintTypeID = 'newCarpet'
GROUP BY DATEPART(year, CAST(datePerformed as datetime))
ORDER BY 1 desc;

/* -- results:

Year    Total Amount
----    ------------
2003    565
2002	1457
1999	3240
1998	492
1994	4566
1993	2485
1992	1744
1991	1866
1990	1480
1989	6200
1986    1155

*/