SELECT COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON) AS TRAN_YEAR, "1st " + cstr(YEAR(PREPAREDON)) AS TRAN_QTR, AVG(PersonalVisits_Booked/Inquiries_Contacted) AS VISIT_RATIO, AVG(Deposits/FIRST_VISITS) AS FIRST_VISIT_RATIO, AVG(Total_Deposits/Total_PersonalVisits) AS DEPOSIT_RATIO, AVG(MoveIns/Total_PersonalVisits) AS CLOSING_RATIO, AVG(Appt_From_Lead_Call_Out/Lead_Call_Out) AS LEAD_RATIO, SUM(MoveIns/ActivityPoints) AS ACTIVITY_MOVEIN, AVG((Shop_Front+Shop_Market)/2) AS MYSTERY_AVG, SUM(MOVEINS) AS AVG_MOVEINS
FROM COMMUNITYTRANSACTION INNER JOIN COMMMUNITY ON COMMUNITYTRANSACTION.COMMUNITYID = COMMMUNITY.ID
WHERE MONTH(PREPAREDON) IN (1,2,3)
GROUP BY COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON)
UNION
SELECT COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON) AS TRAN_YEAR, "2nd " + cstr(YEAR(PREPAREDON)) AS TRAN_QTR, AVG(PersonalVisits_Booked/Inquiries_Contacted) AS VISIT_RATIO, AVG(Deposits/FIRST_VISITS) AS FIRST_VISIT_RATIO, AVG(Total_Deposits/Total_PersonalVisits) AS DEPOSIT_RATIO, AVG(MoveIns/Total_PersonalVisits) AS CLOSING_RATIO, AVG(Appt_From_Lead_Call_Out/Lead_Call_Out) AS LEAD_RATIO, AVG(MoveIns/ActivityPoints) AS ACTIVITY_MOVEIN, AVG((Shop_Front+Shop_Market)/2) AS MYSTERY_AVG, SUM(MOVEINS) AS AVG_MOVEINS
FROM COMMUNITYTRANSACTION INNER JOIN COMMMUNITY ON COMMUNITYTRANSACTION.COMMUNITYID = COMMMUNITY.ID
WHERE MONTH(PREPAREDON) IN (4,5,6)
GROUP BY COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON)
UNION
SELECT COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON) AS TRAN_YEAR, "3rd " + cstr(YEAR(PREPAREDON)) AS TRAN_QTR, AVG(PersonalVisits_Booked/Inquiries_Contacted) AS VISIT_RATIO, AVG(Deposits/FIRST_VISITS) AS FIRST_VISIT_RATIO, AVG(Total_Deposits/Total_PersonalVisits) AS DEPOSIT_RATIO, AVG(MoveIns/Total_PersonalVisits) AS CLOSING_RATIO, AVG(Appt_From_Lead_Call_Out/Lead_Call_Out) AS LEAD_RATIO, AVG(MoveIns/ActivityPoints) AS ACTIVITY_MOVEIN, AVG((Shop_Front+Shop_Market)/2) AS MYSTERY_AVG, SUM(MOVEINS) AS AVG_MOVEINS
FROM COMMUNITYTRANSACTION INNER JOIN COMMMUNITY ON COMMUNITYTRANSACTION.COMMUNITYID = COMMMUNITY.ID
WHERE MONTH(PREPAREDON) IN (7,8,9)
GROUP BY COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON)
UNION SELECT COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON) AS TRAN_YEAR, "4th " + cstr(YEAR(PREPAREDON)) AS TRAN_QTR, AVG(PersonalVisits_Booked/Inquiries_Contacted) AS VISIT_RATIO, AVG(Deposits/FIRST_VISITS) AS FIRST_VISIT_RATIO, AVG(Total_Deposits/Total_PersonalVisits) AS DEPOSIT_RATIO, AVG(MoveIns/Total_PersonalVisits) AS CLOSING_RATIO, AVG(Appt_From_Lead_Call_Out/Lead_Call_Out) AS LEAD_RATIO, AVG(MoveIns/ActivityPoints) AS ACTIVITY_MOVEIN, AVG((Shop_Front+Shop_Market)/2) AS MYSTERY_AVG, SUM(MOVEINS) AS AVG_MOVEINS
FROM COMMUNITYTRANSACTION INNER JOIN COMMMUNITY ON COMMUNITYTRANSACTION.COMMUNITYID = COMMMUNITY.ID
WHERE MONTH(PREPAREDON) IN (10,11,12)
GROUP BY COMMMUNITY.COMPANYNAME, YEAR(PREPAREDON);