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