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;
select *
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
It seems to me a totally unnecessary complication to cross join distinct month from royalty to franchise and then join again the whole thing to royalty. That has to be simplified to simple joins with ON clauses.CREATE TABLE franchise (
FIN_NUM INT,
Franchise_no INT,
Business_Entity varchar (50)
);
CREATE TABLE Royalties (
FIN_NUM INT,
GrossRev DECIMAL(30,2),
RMonth INT,
RYear Int
);
CREATE TABLE hours_log (
FIN_NUM INT,
Hours INT,
RMonth INT,
RYear INT,
reportweek INT
);
-- insert data franchise
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0001, 101, 'test1');
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0002, 102, 'test2');
INSERT INTO franchise (FIN_NUM, Franchise_no, Business_Entity) VALUES (0003, 103,'test3');
-- insert data royalty
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 500, 12, 2014);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0001, 2000, 2, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 2500, 2, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0002, 4000, 1, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 2000, 1, 2015);
INSERT INTO Royalties (FIN_NUM, GrossRev, RMonth, RYear) VALUES (0003, 1000, 3, 2015);
-- insert data hours_log
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 10, 12, 2014, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 20, 12, 2014, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 25, 12, 2014, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 60, 1, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 40, 1, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 40, 2, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 45, 2, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0001, 47, 2, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 50, 2, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 65, 2, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 55, 2, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0002, 70, 2, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 30, 1, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 1, 2015, 4);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 10, 3, 2015, 1);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 20, 3, 2015, 2);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 3);
INSERT INTO hours_log (FIN_NUM, Hours, RMonth, RYear, reportweek) VALUES (0003, 25, 3, 2015, 4);
CREATE TEMPORARY TABLE test1
SELECT c.FIN_NUM, c.Franchise_no, IFNULL(R.grossrev,0.00) As grossrev, 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.ryear, c.rmonth
FROM
(SELECT a.FIN_NUM, a.Franchise_no, b.ryear,b.rmonth FROM franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
order by c.ryear DESC, c.rmonth DESC,c.FIN_NUM ASC;
SELECT GR.franchise_no, GR.GROSSREV, GR.WEEK1_HRS, GR.WEEK2_HRS, GR.WEEK3_HRS, GR.WEEK4_HRS,
GR.WEEK5_HRS, GR.TOTAL_HRS, GR.ryear,GR.rmonth,
CASE WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV END FORECAST_GR
-- R.AdjGrossRevenue, R.RoyaltiesOwed
FROM test1 GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by GR.franchise_no, GR.ryear asc, GR.rmonth;
DROP TEMPORARY TABLE test1;
SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear
This is a new company that I am working for and I cannot use SPs as well. So I am trying to work within the parameters that they have given meIf they don't want SPs is one thing but if they want to pull out data from the database in the form of reports there is no way you can bypass index creation. Beside that it doesn't make any sense because it will not affect the functionality but it WILL affect the performance when they will run these queries and it will be felt by the entire system.
SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear
Hours_log:That is your big problem because I believe that is the largest table in terms of records. That is why I asked you about that last query...
no indexes
EXPLAIN
SELECT
GR.franchise_no,
GR.GROSSREV,
GR.WEEK1_HRS,
GR.WEEK2_HRS,
GR.WEEK3_HRS,
GR.WEEK4_HRS,
GR.WEEK5_HRS,
GR.TOTAL_HRS,
GR.ryear,
GR.rmonth,
CASE
WHEN GR.GROSSREV = '0.00' THEN R.GROSSREV
ELSE GR.GROSSREV
END FORECAST_GR
FROM
(
SELECT c.FIN_NUM, c.Franchise_no, ISNULL(R.grossrev,0.00) As grossrev, ISNULL(week1,0) WEEK1_HRS, ISNULL(week2,0) WEEK2_HRS,
ISNULL(week3,0) WEEK3_HRS, ISNULL(week4,0) WEEK4_HRS, ISNULL(week5,0) WEEK5_HRS, ISNULL(Total_hours,0) TOTAL_HRS,
c.ryear, c.rmonth
FROM
(SELECT * from franchise as a,
(Select distinct ryear, rmonth from royalties) as b) as c
left join royalties as R on c.FIN_NUM = R.FIN_NUM and c.ryear=R.ryear AND c.rmonth=R.rmonth
LEFT JOIN (SELECT h.FIN_NUM AS fn, h.rMonth, h.ryear,
MAX(CASE WHEN h.reportweek =1 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week1,
MAX(CASE WHEN h.reportweek =2 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week2,
MAX(CASE WHEN h.reportweek =3 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week3,
MAX(CASE WHEN h.reportweek =4 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week4,
MAX(CASE WHEN h.reportweek =5 THEN CAST(h.hours AS DECIMAL(30,2)) ELSE 0 end) week5,
CAST(SUM(h.hours) AS DECIMAL(30,2)) AS Total_hours
FROM
hours_log h
WHERE h.rYear = 2015
GROUP BY
h.FIN_NUM,h.rmonth, h.ryear) h1 ON h1.fn = R.FIN_NUM AND h1.RMonth = R.RMonth AND h1.ryear = R.ryear
)
GR
LEFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
order by
GR.franchise_no,
GR.ryear asc,
GR.rmonth;
EFT JOIN (SELECT RL.FIN_NUM fn, RL.GrossRev, RL.ryear, RL.rmonth
FROM royalties RL
) R ON R.fn = GR.FIN_NUM AND R.ryear = GR.ryear AND R.rmonth = GR.rmonth - 1
I helped in one question involving the franchise, and royalties tables.
If you can rephrase the question using these two existing tables, I might be able to help.
In the meantime, one needs to find out which part is causing the delay