CREATE TEMPORARY TABLE GrossRev1 SELECT c.ID, IFNULL(R.GrossRevenue,0) AS GROSSREVENUE, IFNULL(R.RoyaltiesOwed,0) ROYALTIESOWED, IFNULL(week1,0) WEEK1_HRS, IFNULL(week2,0) WEEK2_HRS, IFNULL(week3,0) WEEK3_HRS, IFNULL(week4,0) WEEK4_HRS, IFNULL(week5,0) WEEK5_HRS, IFNULL(Total_hours,0) TOTAL_HRS, c.year,c.month FROM (SELECT a.ID, a.franchise_no, a.closeddate, b.year,b.month FROM franchises AS a, (SELECT DISTINCT year, month FROM royalties WHERE year = 2015 ) as b) as c LEFT JOIN royalties AS R ON c.id = R.franchiseno AND c.year=R.year AND c.month=R.month LEFT JOIN (SELECT h.franchiseno AS fn, h.Month, h.year, MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week1, MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week2, MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week3, MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week4, MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hoursbilled AS DECIMAL(30,2)) ELSE 0 end) week5, CAST(SUM(h.hoursbilled) AS DECIMAL(30,2)) AS Total_hours FROM hours_log h WHERE h.Year = 2015 -- and h.month = 9 GROUP BY h.franchiseno,h.reportmonth, h.reportyear) h1 ON h1.fn = R.franchiseno AND h1.ReportMonth = R.Month AND h1.year = R.year WHERE (c.closeddate IS NULL OR c.closeddate = '0000-00-00') ORDER BY c.year DESC, c.month DESC,c.ID ASC; SELECT GR.*, CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.GROSSREVENUE ELSE GR.GROSSREVENUE END CURRENT_MONTH_FORECAST_AGR, CASE WHEN GR.GROSSREVENUE = '0.00' AND GR.ROYALTIESOWED = '0.00' THEN R.RoyaltiesOwed ELSE GR.ROYALTIESOWED END CURRENT_MONTH_FORECAST_TOTAL_ROYALTIES FROM GrossRev1 GR LEFT JOIN (SELECT RL.franchiseno, RL.GROSSREVENUE, RL.RoyaltiesOwed, RL.year, RL.month FROM royalties_log RL ) R ON R.franchiseno = GR.ID AND R.year = GR.year AND R.month = GR.month - 1; DROP TEMPORARY TABLE GrossRev1;
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE